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:

Applicant Name	Personal Statement	Work History	Education History
Applicant1	Applicant1_PS	Applicant1_WH1	Applicant1_EH1
Applicant1	Applicant1_PS	Applicant1_WH1	Applicant1_EH2
Applicant1	Applicant1_PS	Applicant1_WH2	Applicant1_EH1
Applicant1	Applicant1_PS	Applicant1_WH2	Applicant1_EH2
Applicant2	Applicant2_PS	Applicant2_WH1	Applicant2_EH1
Applicant2	Applicant2_PS	Applicant2_WH1	Applicant2_EH2
Applicant3	Applicant3_PS	Applicant3_WH1	Applicant3_EH1
Applicant3	Applicant3_PS	Applicant3_WH2	Applicant3_EH1
Applicant3	Applicant3_PS	Applicant3_WH3	Applicant3_EH1

<tbody>
</tbody>

And I want it to look like this:

Applicant Name	Personal Statement	Work History	Education History
Applicant1	Applicant1_PS	Applicant1_WH1	Applicant1_EH1
		Applicant1_WH2	Applicant1_EH2
Applicant2	Applicant2_PS	Applicant2_WH1	Applicant2_EH1
			Applicant2_EH2
Applicant3	Applicant3_PS	Applicant3_WH1	Applicant3_EH1
		Applicant3_WH2
		Applicant3_WH3

<tbody>
</tbody>

....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!