Compare Columns and Produce Result

Dangerous

New Member
Joined
Sep 13, 2006
Messages
7
Hi,
I'm new to the board, but i have used these pages as a valuable resource for many years, so thank you firstly!

My problem is,

I have one ws with about 1500 lines of codes (in one column), some are repeated, some are unique.
In the second ws, i have 75,000 individual codes in one column and a descriptive name in the next colum.

What i would like to do is compare the codes that exist in ws1 to the codes that exist in ws2 and then when two codes match, the description is placed next to the code in ws1 in the next column. This would be for the whole work sheet.

For example in my first ws i have
DH86
UE92
TY34

In the second work sheet i have

UI23 Stock movement
DH86 Stock transfer
HU23 Finance Agreement
TY34 Repeat Transaction

So if i take the code DH86 in ws1 it scans through the code column in ws2 until it finds a match, it then takes the description of the match and places next to the code in ws1.

I hope this is clear enough, and it is possible to do, thanks for the help!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

I think VLookup function will do the job, however how can you accomodate
75,000 codes in one column?

Are you using Office12 already?
 

Dangerous

New Member
Joined
Sep 13, 2006
Messages
7
sorry,

It only goes up to 65,500 rows in the code column. But i know all the codes are within this range.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Dangerous
Welcome to the Board !!!!!

other approach
you can use index+match
  A    B                   
1 DH86 Stock transfer      
2 UE92 #N/A                
3 TY34 Repeat Transaction  

Sheet2

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B1:B3 =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))

[Table-It] version 06 by Erik Van Geit
there is #N/A because UE92 was not found int the list

list
  A    B                   
1 UI23 Stock movement      
2 DH86 Stock transfer      
3 HU23 Finance Agreement   
4 TY34 Repeat Transaction  

sheet1

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

With VLookup function

Cell B1 if Sheet1

=VLookUp(A1,Sheet2!A:B,2,False)

then place the cursor to the bottom left corner so that the cursor change
to +.
Double clicking on it will fill down to the bottom of the records in Col.A
 

Dangerous

New Member
Joined
Sep 13, 2006
Messages
7
Thanks for the quick reply,

Erik,
Where do i put this code? In the first cell on column 2 in worksheet 1?
sheet 1 where the code result go is called worksheet 1 and sheet 2 is where the codes and descriptions lie.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,598
Messages
5,541,163
Members
410,543
Latest member
ExcelGlenn
Top