Remove duplicates but keep 2 of the same-

[thursday140]

Hello,

I have a list of over 1,000 entries and I would like to remove duplicates. But, I need to keep 2 of the exact same and remove the rest. Please see attached sample of the before and after.

Any help is greatly appreciated.

Thank you!

[Pete_UK]

You seem to have lost Job No 451245 (row 5) from your "After" table. Is it duplicate names that you are concerned with?

Pete

[thursday140]

You seem to have lost Job No 451245 (row 5) from your "After" table. Is it duplicate names that you are concerned with?

Pete

Basically, I need to know how many mechanics it takes to do the same job> they have duplicate business names and addresses so when I remove duplicates I only stay with one line and lose the rest. I need to keep 2 lines when removing duplicates. Is this possible?

[Sam Capricci]

Have you tried the remove duplicates function under the data tab?

[thursday140]

Have you tried the remove duplicates function under the data tab?

Yes, but when I use it it removes all rows but one and my goal is to keep 2 identical rows per account.

[Sam Capricci]

with Olive starter you have three different mechanics, does that matter?

[thursday140]

with Olive starter you have three different mechanics, does that matter?

No but I would like to keep 2 of the same Job numbers.

[Sam Capricci]

well for me and without using the remove duplicates function I'd use a helper column with this formula in it dragged down...
=COUNTIF($A$3:A3,A3)
this only focuses on the job number and not the mechanic.
then I'd apply a filter to the column where the countif is and using the filter I'd sort them ascending order, then filter out 1s and 2s (deselect) then delete those that are higher than 2.

[thursday140]

well for me and without using the remove duplicates function I'd use a helper column with this formula in it dragged down...
=COUNTIF($A$3:A3,A3)
this only focuses on the job number and not the mechanic.
then I'd apply a filter to the column where the countif is and using the filter I'd sort them ascending order, then filter out 1s and 2s (deselect) then delete those that are higher than 2.

Ok I will try this.. Thank you,

[bebo021999]

It looks like making a unique distinct list matching 3 criterias: Job - 1st name - 2nd name? Then take 2 records each at least?

[bebo021999]

Try in E2:

Please Login or Register  to view this content.

[thursday140]

Try in E2:

Please Login or Register  to view this content.

Your formula was not working. But thank you, for the help.

[thursday140]

well for me and without using the remove duplicates function I'd use a helper column with this formula in it dragged down...
=COUNTIF($A$3:A3,A3)
this only focuses on the job number and not the mechanic.
then I'd apply a filter to the column where the countif is and using the filter I'd sort them ascending order, then filter out 1s and 2s (deselect) then delete those that are higher than 2.

Thank you this worked well!

[FlameRetired]

If I have interpreted correctly this is another way.

Array enter this in A19 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then array enter this in B19, fill down and across.

Please Login or Register  to view this content.