Excel 2002 : Formula to pick up codes
 

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

Excel 2002 : Formula to pick up codes
 

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
--
Gary's Student


"Mr. Low" wrote:

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

Excel 2002 : Formula to pick up codes
 

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

--
A36B58K641


"Gary''s Student" wrote:

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
--
Gary's Student


"Mr. Low" wrote:

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

Excel 2002 : Formula to pick up codes
 

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)+1ROWS($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


Mr. Low wrote:
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

--
A36B58K641


"Gary''s Student" wrote:

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
--
Gary's Student


"Mr. Low" wrote:

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