Deleting All Rows for Duplicate Entries Except Those With Most Items In Row

[foofoo]

I need to identify & delete rows of data. Most entries in Column B
appear more than one time in my list, but the other data on the rows
associated with these Column B duplicates are different.

I need to keep just 1 unique occurrence of each Column B entry, and the
occurrence I need to keep is the one that has the most items in each
row. In the example below, I need to keep Rows 1 and 5 for the 2
unique entries in Column B.

I am not comfortable with programs. Is there a way to accomplish my
goal with formulas?

A B C D E
Row 1 KEEP 123456789 4567890123 12345 23456 34567
Row 2 DELETE 123456789 4567890123 23456 34567
Row 3 DELETE 123456789 4567890123 34567
Row 4 DELETE 234567890 5678901234 45678
Row 5 KEEP 234567890 5678901234 12345 45678


Thanks!



Sandi

 

[Herbert Seidenberg]

Assume your simplified data looks like this
A B C D E F G
31 3 4 5 2 5 0
31 5 4 2 . 4 1
45 2 4 1 2 5 0
45 5 2 2 . 4 0
45 2 4 . . 3 0
45 3 3 . . 3 0
45 5 . . . 2 1
53 5 3 3 2 5 0
53 5 5 3 2 5 0
53 3 2 4 . 4 1
68 3 4 5 2 5 1
72 5 4 2 1 5 0
72 2 4 . . 3 1
88 5 2 2 1 5 1
94 2 4 4 . 4 1
Periods are blank cells. Rows can be in any order.
You want to delete rows that have duplicates in A
and do not have as many items in B thru E
1. Enter this formula into F1 and copy down
=COUNTA(A1:E1)
2. Select data in column F and
Copy > Paste Special > Value
3. Select data in A thru F and
Sort by A ascending
Then by F descending
4. Enter this formula into G1 and copy down
=IF(A1=A2,0,1)
5. Select data in column G and
Copy > Paste Special > Value
At this point your spreadsheet should look like shown
6. Select G1 and
Insert > Shift cells down
7. Enter 1 into G1
8. Select data in column G and
Edit > Go To > Special > Column Difference
9. Delete > Entire Row