Vlookup with Condition formula 1

[MaffewW]

hi folks

I've got 2 x relational tables in 2 x worksheets

sheet1 = a list of companys. The accountnumber field is unique to each row.

sheet2 = a list of phone numbers, there are multiple phone numbers for each account number in this list

so the relationship between the 2 x tables is on the accountnumber field. I've added an instancenumber to define how many phone numbers there are to each account


sheet1 eg
accountnumber company
1 testcompany1
2 testcompany2
3 testcompany3

sheet 2 eg

account Phone instancenumber
1 123456 1
1 234567 2
2 345678 1
3 456789 1
3 012345 2
3 456798 3
3 456788 4


I need to transpose the lists into a flat file so the phonenumbers from sheet2 are linear with the data in sheet1

Sheet1 aim is
accountnumber company Phone1 Phone2 Phone3 Phone4
1 testcompany1 123456 234567
2 testcompany2 345678
3 testcompany3 456789 012345 456798 456788

so I guess for phone1 I need to do a vlookup where accountnumbers match and sheet2 instance = 1 and phone2 a vlookup where accountnumbers match and sheet2 instance = 2


Hope that makes some sense, I can do it in sql if all else fails thoough want to do it in excel ideally

cheers

Matt

Brighton, UK

 

[KenWright]

Highest instance number?

How about simply adding another field to your sheet 2 that concatenates Account number and instance number. Then just do a VLOOKUP in your table on sheet 1, creating the Lookup variable from the relevant account and instance number on your table.

So, on sheet 2, to the left of your data (Col A) so that you can use the easier VLOOKUP rather than INDEX/MATCH, put the following:-

=B2&"x"&C2 and copy down.

Now on your table on sheet 1, assuming the table is in A1:G100, (assumes highest instance number is 5 and that acct num is in Col A and Comp Name in Col B), put the instance numbers in cells C1:G1, and the account numbers in A2:A100.

In cell B2 on sheet 1, put

=IF(ISNA(VLOOKUP($A2&"x"&C$1,Sheet2!$A$1:$C$100,3,0)),"",VLOOKUP($A2&"x"&C$1,Sheet2!$A$1:$C$100,3,0))

and copy across and down

Done.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MaffewW]

nice didnt think of doing it that way.

Ken that has done the biz. However being as there are 17000 rows in sheet1 and up to 11 phone numbers per account my other PC is looking a little ropey at the moment doing 187000 lookups

Matt

Brighton, UK

 

[KenWright]

Thats OK, just do it one column at a time, then hardwire them and move on to the next. Just be sure to leave the formula in the first row alone, so that you can just copy it for the next column.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------