Solved Need help deleting or sorting lines in excel

August 30, 2017 at 14:55:27
Specs: Windows 7
I have an excel worksheet that has 8,000+ rows of information. There are approximately 900 different employees making up those rows. I want to remove the employees that have exactly 12 rows of information and leave all the other ones. is there an easy way to do this without having to scroll thru them all?
The headers are ID #, Last name, Pay Date, Pay Code, Amount
Each Last name should have exactly 12 rows. If they don't I need to figure out why.

See More: Need help deleting or sorting lines in excel

Reply ↓  Report •

✔ Best Answer
August 31, 2017 at 10:37:42
Maybe it would be better if you just used this in H2:

=COUNTIF(B:B,B2)

Now when you sort by Column H, you'll see how many rows there are for each name. Since you mentioned that you want to "find out why they do not have 12 rows" , maybe seeing the numbers of rows for each name - in a sorted order - will reveal a pattern of some type.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
August 30, 2017 at 16:11:23
We need a little more information related to the layout of your data.

You say your headers are:

ID #   Last Name    Pay Date     Pay Code     Amount 

So I'll assume (dangerous!) this:

      A        B           C            D            E
1   ID #   Last Name    Pay Date     Pay Code     Amount 

If that is correct, does your data look like this..

      A        B           C            D            E
1   ID #   Last Name    Pay Date     Pay Code     Amount 
2          Smith
3          
4          
...
13         
14         Jones
15         
16         

...or this:

      A        B           C            D            E
1   ID #   Last Name    Pay Date     Pay Code     Amount 
2          Smith
3          Smith
4          Smith
...
13         Smith
14         Jones
15         Jones
16         Jones

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
August 30, 2017 at 16:22:28
This is how it looks. I would want to find all of the Names that have 12 rows (signifying months) so that I can sort out the others and find out why they do not have 12 rows.

3062 Doe, Jane 7/8/2016 7026 LTD ER 15.81
3062 Doe, Jane 8/5/2016 7026 LTD ER 17.56
3062 Doe, Jane 9/2/2016 7026 LTD ER 17.56
3062 Doe, Jane 10/14/2016 7026 LTD ER 17.56
3062 Doe, Jane 11/10/2016 7026 LTD ER 17.56
3062 Doe, Jane 12/9/2016 7026 LTD ER 17.56
2251 Smith, Bob 1/8/2016 7026 LTD ER 49.98
2251 Smith, Bob 2/5/2016 7026 LTD ER 52.26
2251 Smith, Bob 3/4/2016 7026 LTD ER 52.26
2251 Smith, Bob 4/1/2016 7026 LTD ER 52.26
2251 Smith, Bob 5/13/2016 7026 LTD ER 52.26
2251 Smith, Bob 6/10/2016 7026 LTD ER 52.26
2251 Smith, Bob 7/8/2016 7026 LTD ER 53.83
2251 Smith, Bob 8/5/2016 7026 LTD ER 53.83
2251 Smith, Bob 9/2/2016 7026 LTD ER 53.83
2251 Smith, Bob 10/14/2016 7026 LTD ER 53.83
2251 Smith, Bob 11/10/2016 7026 LTD ER 53.83
2251 Smith, Bob 12/9/2016 7026 LTD ER 53.83

message edited by TinaG


Reply ↓  Report •

#3
August 30, 2017 at 18:41:43
You will notice that I included Column Letters and Row numbers in my example. I noticed that you did not.

Since I don't know what columns your data is in, I have to guess in order to offer a suggestion and I hate guessing. In any case, I'll give it a shot. I once again going to make an assumption. Your data looks something like this:

    A       B               C             D      E       F       G
1   ID    Name             Date          W       X       Y       Z
2  3062	Doe, Jane	7/8/2016	7026	LTD	ER	15.81
3  3062	Doe, Jane	8/5/2016	7026	LTD	ER	17.56
4  3062	Doe, Jane	9/2/2016	7026	LTD	ER	17.56
5  3062	Doe, Jane	10/14/2016	7026	LTD	ER	17.56
6  3062	Doe, Jane	11/10/2016	7026	LTD	ER	17.56
7  3062	Doe, Jane	12/9/2016	7026	LTD	ER	17.56
8  2251	Smith, Bob	1/8/2016	7026	LTD	ER	49.98
9  2251	Smith, Bob	2/5/2016	7026	LTD	ER	52.26
10 2251	Smith, Bob	3/4/2016	7026	LTD	ER	52.26
11 2251	Smith, Bob	4/1/2016	7026	LTD	ER	52.26
12 2251	Smith, Bob	5/13/2016	7026	LTD	ER	52.26
13 2251	Smith, Bob	6/10/2016	7026	LTD	ER	52.26
14 2251	Smith, Bob	7/8/2016	7026	LTD	ER	53.83
15 2251	Smith, Bob	8/5/2016	7026	LTD	ER	53.83
16 2251	Smith, Bob	9/2/2016	7026	LTD	ER	53.83
17 2251	Smith, Bob	10/14/2016	7026	LTD	ER	53.83
18 2251	Smith, Bob	11/10/2016	7026	LTD	ER	53.83
19 2251	Smith, Bob	12/9/2016	7026	LTD	ER	53.83


1 - In H2, enter this formula:

=IF(COUNTIF(B:B,B2)=12,"Good","Bad")

2 - Click on H2 and hover your cursor over the little square in the lower right corner of the cell. When it turns into a plus sign, double click. That should auto-fill the formula down to the bottom of your data, assuming there is data in Column G all the way down to the bottom of your data set.

3 - Column H will now contain Good for each name that appears 12 times and Bad
for each name that doesn't.

4 - Select Columns A:H and Sort on Column H. All of your Bad names will be moved to the top of your data set.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
August 31, 2017 at 10:37:42
✔ Best Answer
Maybe it would be better if you just used this in H2:

=COUNTIF(B:B,B2)

Now when you sort by Column H, you'll see how many rows there are for each name. Since you mentioned that you want to "find out why they do not have 12 rows" , maybe seeing the numbers of rows for each name - in a sorted order - will reveal a pattern of some type.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question