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

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


"Legend" wrote:

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

Thank you so much... That worked

On Oct 16, 8:16 pm, Bernard Liengme <blien...@TRUENORTH.stfx.ca>
wrote:
> 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
>
> "Legend" wrote:
> > 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...

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