How to Remove Duplicates of a Given Row using Formula?

[gordonhtfu]

Here is a sample (simplified) scenario that I am trying to resolve.

Given...
[TABLE="width: 270"]
<COLGROUP><COL style="WIDTH: 54pt" span=5 width=72><TBODY>[TR]
[TD="class: xl76, width: 72, bgcolor: #ffcc99"]　[/TD]
[TD="class: xl76, width: 72, bgcolor: #ffcc99"]Tom[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]Peter[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]Sam[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]Susan[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo1[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]1[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]2[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]1[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]3[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo2[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]A[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]A[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]B[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]A[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo3[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]![/TD]
[TD="class: xl77, bgcolor: #ffcc99"]@[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]#[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]$[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo4[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]a[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]b[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]b[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]c[/TD]
[/TR]
</TBODY>[/TABLE]

Trying to make it into this by removing all the duplicates:
[TABLE="width: 270"]
<COLGROUP><COL style="WIDTH: 54pt" span=5 width=72><TBODY>[TR]
[TD="class: xl76, width: 72, bgcolor: #ffcc99"]Combo1[/TD]
[TD="class: xl76, width: 72, bgcolor: #ffcc99"]1[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]2[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]3[/TD]
[TD="class: xl77, width: 72, bgcolor: #ffcc99"]　[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo2[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]A[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]B[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]　[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]　[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo3[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]![/TD]
[TD="class: xl77, bgcolor: #ffcc99"]@[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]#[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]$[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #ffcc99"]Combo4[/TD]
[TD="class: xl76, bgcolor: #ffcc99"]a[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]b[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]c[/TD]
[TD="class: xl77, bgcolor: #ffcc99"]　[/TD]
[/TR]
</TBODY>[/TABLE]

Is there any method using formula? I am preferring formula (over macro or advanced filter) because it would dynamically update the result if the value in given table changes.

 

[XOR LX]

Hi,

You won't be able to have the original table values replaced as desired without VBA, though if you are happy with having a different table which updates to give your desired results then, assuming the original table as you give it is in the range A1:E5 (with headers in row 1 and column A), create another table (with identical dimensions and headers), enter this formula in the topleftmost cell and then copy across and down to fill the table:

=IF(COLUMNS($A:A)>SUMPRODUCT(1/COUNTIF($B2:$E2,$B2:$E2)),"",OFFSET(B2,,SUMPRODUCT(--(FREQUENCY(MATCH($B2:B2,$B2:B2,0),MATCH($B2:B2,$B2:B2,0))>1)),,))

Regards

 

[gordonhtfu]

Hi,

You won't be able to have the original table values replaced as desired without VBA, though if you are happy with having a different table which updates to give your desired results then, assuming the original table as you give it is in the range A1:E5 (with headers in row 1 and column A), create another table (with identical dimensions and headers), enter this formula in the topleftmost cell and then copy across and down to fill the table:

=IF(COLUMNS($A:A)>SUMPRODUCT(1/COUNTIF($B2:$E2,$B2:$E2)),"",OFFSET(B2,,SUMPRODUCT(--(FREQUENCY(MATCH($B2:B2,$B2:B2,0),MATCH($B2:B2,$B2:B2,0))>1)),,))

Regards

Thank you. You are too smart.

How do you find the answer so quickly to random questions?

 

[XOR LX]

Probably as I am fortunate enough to be able to see order within that apparent randomness!

Glad I could help and thanks for the feedback.

Regards