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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
sorry,

It only goes up to 65,500 rows in the code column. But i know all the codes are within this range.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top