Removing Duplicate Information in an Excel Table

[Interiority]

Hi all

I'm trying to write a macro that will remove duplicate data in a table. Essentially, my table looks like this:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Applicant Name[/TD]
[TD]Personal Statement[/TD]
[TD]Work History[/TD]
[TD]Education History[/TD]
[/TR]
[TR]
[TD]Applicant1[/TD]
[TD]Applicant1_PS[/TD]
[TD]Applicant1_WH1[/TD]
[TD]Applicant1_EH1[/TD]
[/TR]
[TR]
[TD]Applicant1[/TD]
[TD]Applicant1_PS[/TD]
[TD]Applicant1_WH1[/TD]
[TD]Applicant1_EH2[/TD]
[/TR]
[TR]
[TD]Applicant1[/TD]
[TD]Applicant1_PS[/TD]
[TD]Applicant1_WH2[/TD]
[TD]Applicant1_EH1[/TD]
[/TR]
[TR]
[TD]Applicant1[/TD]
[TD]Applicant1_PS[/TD]
[TD]Applicant1_WH2[/TD]
[TD]Applicant1_EH2[/TD]
[/TR]
[TR]
[TD]Applicant2[/TD]
[TD]Applicant2_PS[/TD]
[TD]Applicant2_WH1[/TD]
[TD]Applicant2_EH1[/TD]
[/TR]
[TR]
[TD]Applicant2[/TD]
[TD]Applicant2_PS[/TD]
[TD]Applicant2_WH1[/TD]
[TD]Applicant2_EH2[/TD]
[/TR]
[TR]
[TD]Applicant3[/TD]
[TD]Applicant3_PS[/TD]
[TD]Applicant3_WH1[/TD]
[TD]Applicant3_EH1[/TD]
[/TR]
[TR]
[TD]Applicant3[/TD]
[TD]Applicant3_PS[/TD]
[TD]Applicant3_WH2[/TD]
[TD]Applicant3_EH1[/TD]
[/TR]
[TR]
[TD]Applicant3[/TD]
[TD]Applicant3_PS[/TD]
[TD]Applicant3_WH3[/TD]
[TD]Applicant3_EH1[/TD]
[/TR]
</tbody>[/TABLE]

















And I want it to look like this:
[TABLE="class: grid, width: 75, align: left"]
<tbody>[TR]
[TD]Applicant Name[/TD]
[TD]Personal Statement[/TD]
[TD]Work History[/TD]
[TD]Education History[/TD]
[/TR]
[TR]
[TD]Applicant1[/TD]
[TD]Applicant1_PS[/TD]
[TD]Applicant1_WH1[/TD]
[TD]Applicant1_EH1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Applicant1_WH2[/TD]
[TD]Applicant1_EH2[/TD]
[/TR]
[TR]
[TD]Applicant2[/TD]
[TD]Applicant2_PS[/TD]
[TD]Applicant2_WH1[/TD]
[TD]Applicant2_EH1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Applicant2_EH2[/TD]
[/TR]
[TR]
[TD]Applicant3[/TD]
[TD]Applicant3_PS[/TD]
[TD]Applicant3_WH1[/TD]
[TD]Applicant3_EH1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Applicant3_WH2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Applicant3_WH3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]














....and so on.

Does anyone have any ideas how I can go about doing this? I'm drawing a real blank so far

 

[Interiority]

Please excuse the bump, I'd really appreciate some help with this.

 

[smartysr]

This might solve your problem.

Sub test()
Dim last_row_add, return_back, last_column


last_column = Range("XFD1").End(xlToLeft).Column + 1
last_row_add = Range("A" & Range("A650000").End(xlUp).Row).Address


Range(last_row_add).Select


Do
If Range(last_row_add).Column <> last_column Then


Do
If ActiveCell.Row <> 2 Then
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.ClearContents
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
End If
Loop Until ActiveCell.Row = 2


Range(last_row_add).Offset(0, 1).Select
last_row_add = ActiveCell.Address
End If
Loop Until Range(last_row_add).Column = last_column




End Sub

 

[azumi]

Something like this perhaps? with COUNTIF Function:

https://simpan.ugm.ac.id/public.php?service=files&t=a1089d28103b84a5a7713a406a963fab

 

[Interiority]

Smartysr - This is brilliant, thank you so much. Thanks also to Azumi - very useful!