Excel help

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

Code:

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

I'll break it down:

Code:

=VLOOKUP(A2,

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

Code:

'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

Code:

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.

Code:

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.

 

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.