Duplicate Values

[Legend]

If I have the following data:

1
1
1
2
2
2
3
3
3
1
1
1
2
2
2

How will I get the following output?

1
2
3
1
2

instead of what excel is giving me?

1
2
3

Someone please advice...

 

[Guest]

Let's say you data is in A1:A100
In B1 entrer 0
In B2 enter =IF(A2=A1,1,0)
Copy down to B100
Select B1:B100; copy; use Edit | Paste Special specifying Values
Your formulas are now values ( 0 or 1)
Sort ascending A1:B100 using B as the only key
Delete all the entries where B = 1
Best wiishes

 

[Legend]

Thank you so much... That worked

Let's say you data is in A1:A100
In B1 entrer 0
In B2 enter =IF(A2=A1,1,0)
Copy down to B100
Select B1:B100; copy; use Edit | Paste Special specifying Values
Your formulas are now values ( 0 or 1)
Sort ascending A1:B100 using B as the only key
Delete all the entries where B = 1
Best wiishes

--www.stfx.ca/people/bliengme

 

[Fan924]

Set up a loop increment LineNo. Delete and shift cells up.

If DataA2 = DataA1 Then
Range("A" & LineNo + 1).Select 'Select & delete cell Data2
Selection.ClearContents
Selection.Delete Shift:=xlUp
End If