Excel help

[domoldham]

Hi, I have looked elsewhere, but no luck. I,m not sure if im wording the search correctly.
Anyway please help.
I have a large list of addresses in excess of 3000 all of which have a unique code.
Every week i have a client send me a list that need the addresses (normally about 250)
Is their anyway i can make excel do a search on the large list and copy the required weekly addresses. I have searched and i hear about something called vlook up, but i dont know what this is.
I am using Excel 2008 Mac. I am not totally familiar with excel so VERY simplified terms would be very much appreciated.
Thanks in advance

 

[xUKHCx]

Do they send you a list of the unique idenifier?

If so then yes it is possible to do it fairly simply. Without seeing your spreadsheet I'll make a few assumptions, You have your list in "worksheet 1" and you copy the information supplied into "worksheet 2".

In the columns next to the unique identifier (assuming "column a") in "worksheet 2" you need to put a formula like

=VLOOKUP(A2,'Worksheet 1!A1:D5000,2,FALSE))

I'll break it down:

=VLOOKUP(A2,

This part tells the lookup what value to lookup, so bascially it is searching for the value that is present in A2

'Worksheet 1!A1:D5000

This part tells the lookup where to search, this is seaching worksheet 1 for columns A->D down to row 5000

2

This tells the lookup which row to spit out as the result. If your unique identifier was in column A this would return the value in column B. If you wanted column C then use a 3 and column D use 4 etc.

FALSE))

This part tells the lookup that the list isn't ordered so it won't stop where it thinks it should find the data.

Obviously without seeing your spreadsheet I can only give examples but hopefully the above should give you enough to work it out.

 

[domoldham]

Hi Thanks for the reply. Column C is the unique code that represents the address, then columns D,E,F,G and H are the addresses ie house number, street name, Town, County, Postcode respectively. So that is in the large Database.
Then weekly they send a database which only has the unique code (column A)

So i want the weekly spread sheet to look at the large spread sheet and pick out the unique code and the addresses next to it.
What you have described is looking like something i need, but i could do with a bit more of an idiots guide please. Also is this possible on a mac, i have been told elsewhere that mac cant do this.

Thanks again

 

[xUKHCx]

I rarely use Excel on the mac these days so can't say for certain until I get home but I see no reason why a fairly simple function such as vlookup doesn't work on a mac.

What you need to be careful about is keeping links/vlookups in documents that you are going to be sending to 3rd parties because it can break very easily.

If it were me what I'd do is setup a second worksheet in the first document with the lookup formulas already in place, then copy the recieved columns into that worksheet and then do a copy/paste special (values) back. The copy/paste special as values removes the links that you have created.


In the second worksheet assuming the unique code is in column A then to give the addresses.

In Column B (this should return your column D)
=VLOOKUP(A2,Sheet2!C2:H5000,2,FALSE)

In Column C (this should return your column E)
=VLOOKUP(A2,Sheet2!C2:H5000,3,FALSE)

In Column D (this should return your column F)
=VLOOKUP(A2,Sheet2!C2:H5000,4,FALSE)

In Column E (this should return your column G)
=VLOOKUP(A2,Sheet2!C2:H5000,5,FALSE)

In Column F (this should return your column H)
=VLOOKUP(A2,Sheet2!C2:H5000,6,FALSE)

Note the difference between these statements is only one digit, this references which column to pick the results from.


If you have some rows that contain some blanks, i.e. nothing in column F, then the above forumlas will return a 0 in that part of the address to get around this you can do two lookups

=IF(VLOOKUP(A2,Sheet2!C2:H5000,2,FALSE)="","",VLOOKUP(A2,Sheet2!C2:H5000,2,FALSE))
=IF(VLOOKUP(A2,Sheet2!C2:H5000,3,FALSE)="","",VLOOKUP(A2,Sheet2!C2:H5000,3,FALSE))
=IF(VLOOKUP(A2,Sheet2!C2:H5000,4,FALSE)="","",VLOOKUP(A2,Sheet2!C2:H5000,4,FALSE))
=IF(VLOOKUP(A2,Sheet2!C2:H5000,5,FALSE)="","",VLOOKUP(A2,Sheet2!C2:H5000,5,FALSE))
=IF(VLOOKUP(A2,Sheet2!C2:H5000,6,FALSE)="","",VLOOKUP(A2,Sheet2!C2:H5000,6,FALSE))

Doing 2 lookups for every value is a little more taxing but if you are only dealing with small amounts of data then it really shouldn't be a problem. However if you are dealing with 100,000s of rows then you can slim it down with a helper column. Give it a go and see if it is usable. Always try this out of a copy of your original document as you don't want to screw that up.

Again without seeing the spreadsheet I have made some assumptions as to where your data is and how much there is. For example the range C2:H5000 assumes you have a header row hence starting at row 2 and also your data does not extend past row 5000. If it does change this value. Also it assumes that the data you are sent also has a header row as as such the unique identifiers start from row 2.

The above should give you a pretty decent start and will hopefully return enough useful data that you can see how it works.

Remember that you should copy and paste special as values before sending the data outside as you don't want to send the lookups as they will fail.