Excel 2002 : Formula to pick up codes

[Guest]

Dear Sir,

May I know is there any formula that can be input at cell A1 to pick up the
code in column B without repeating its self when it is copied downwards ?

Worksheet Illustration
A B
1 AXP22 AXP22
2 BJT48 AXP22
3 GFR96 AXP22
4 LPC14 BJT48
5 MAP36 BJT48
6 NBB21 NBB21
7 ZAT11 NBB21
8 LPC14
9 LPC14
10 LPC14
11 MAP36
12 MAP36
13 MAP36
14 ZAT11
15 ZAT11
16 GFR96
17 GFR96


If column B is not sorted, can I use back the same formula ?


Thanks

Low


A36B58K641

 

[Guest]

Enter and run this macro:

Sub MrLow()
Range("B:B").Copy Range("A1")
lastcell = Range("A65536").End(xlUp).Row
For i = lastcell To 2 Step -1
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Cells(i, 1).Delete
End If
Next
End Sub

 

[Guest]

Hello Gary's Student,

The steps looks a bit complicated.

Is there any other simpler way of doing this ?

Any suggestion from anyone ?

Thanks

Low

 

[VBA Noob]

Hi

Enter this in A1 and drag down

=IF(COUNTIF($B$1:B1,B1)=1,B1,"")

This will return the unique items form Col B.

then enter this array (ctrl + shift + enter) in C1 and drag down

=IF(ROW()-ROW($B$1:$B$17)+1>ROWS($A$1:$A$17)-COUNTBLANK($A$1:$A$17),"",INDIRECT(ADDRESS(SMALL((IF($A$1:$A$17<>"",ROW($A$1:$A$17),ROW()+ROWS($A$1:$A$17))),ROW()-ROW($B$1:$B$17)+1),COLUMN($A$1:$A$17),4)))

or

You can avoid the 2nd formula by entering he below in A1 and dragging
down

=IF(COUNTIF($B$1:B1,B1)=1,B1,"")

Then Select col A and Paste special values. Then Filter on Col A
selecting non blanks. Select range press Ctrl + g > special and select
visible then copy range to say C1.

or

Just goto Data > Filter > Advance Filter > Unique range and then select
range press Ctrl + g > special and select visible then copy range to
say C1.

VBA Noob