Removal of duplicates, with conditions.

Okay, this is a bit of a multi-step process based on the fact you just want the records with the most fields (read: columns) filled in.
1) insert a blank row above your data.
2) in cell G2 put =A2&B2 and copy down
3) in cell H2 put =COUNTA($A2:$F2) and copy down
4) in cell I2 put =IF($G2=$G1,"",INDEX($A$2:$G$9,MATCH($G2,$G$2:$G$9,0),MATCH(A2,$A2:$G2,0))) copy across for all the fields you need (in this case over to column N), and down
5) Now you need to highlight all this data starting in row 2 and do a sort. Sort by column G(Ascending) then by column H(Descending). click Sort.
6) You should now have only those records for unique combinations of column A & B with the records with the most fields filled in.
7) Copy everything in columns I:N and paste special values on another sheet.
8) Do another sort on this data and you have what you need.

Please let me know if this is works for you and if any of these steps need clarification.

I welcome anyone else who has a more elegant solution as I would love to see it.

Thanks for the feedback. I was racking my brain trying to think of a one-and-done formula and remembered not everything needs/can be solved in one step.

Cheers

as an FYI..

I got the exact same result by only employing a couple of the steps. I did:

- COUNTA then i sorted the results in descending order
- then removed duplicates
- copy and pasted special values in another sheet

I ran a test on both results and found it was the same.

My spreadsheet is 20,000 rows long. The 'cleansed' spreadsheets now each contain 5,478 rows.

To further test each spreadsheet, i concatenated all 6 cells of results (per row), then i added (LEN) the amount of characters in the concatenated cell (they were large values, like 120-150, so the chances of getting a similar number were quite slim). I then subtracted one value from the other. If it was a zero then i simply deduced it was the same entry (post duplicate removal). I then added all the differences and the total was ...zero.

I dont know if it qualifies as a more elegant solution. Its simply a watered down (possibly less robust?) solution to the one you presented.

Thanks again Craig