20,000+ Rows Help

[pastorandy]

Hi Guys
I have two sets of data in the same worksheet.

I have a spreadsheet with uk addresses including postcode data in rows and I want to be able to read the first 4 characters of the address postcode in that column and add the name of the office that falls in that territory into an empty column within that row.

"First Set of Data"

E.G. [COL A] [COL B] [COL C] [COL D] [COL E]
Mr Smith 24 Park Road London B96 6BW

The data for the office territories is listed in two other columns and only has up to the 4 characters of the Postocde in the column.

"Second Set of Data"

e.g [COL F] [COL G]
B96 Office 1
BN18 Office 2


I need to be able to read the 4 characters of the Address Postcode and compare with the postcode in [Column F] and assign the Office in [Column G] to the appropriate row.

I want to come up with this....

E.G. [COL A] [COL B] [COL C] [COL D] [COL E]
Mr Smith 24 Park Road London B96 6BW Office 1


Apologies if I haven't explained this well!

 

[pbrodsky]

Use VLOOKUP in col E
eg. in E3, =VLOOKUP(TRIM(LEFT(D3,4)),DATA2!$F$3:$G$300,2,FALSE)

something like that.

 

[pastorandy]

Hi

Does DATA2 refer to a different worksheet or excel file?

 

[pbrodsky]

refers to the range (whatever it's real name and location) where your "second set of data" is located.

You would copy the function down column E.

look at the help for VLOOKUP for all the details of how to use the function.