A tough one at least for me. Please help

Posted by Al Morris on May 26, 2000 7:04 PM

I need help to solve this problem as a worksheet formula and as a macro also.

In A1, A2, A3, A4, A5, A6, A7 I have
Client 3, Client 7, Client 5, Client 1, Client 4, Client 2, Client 6 (client order is random and represents whole addresses). In reality I have a few hundreds clients.

In B1, B2, B3, B4 I have
4, 7, 5, 2 (represents only part of the clients addresses)

Starting in C1 and next down I want to get the whole address only for clients from B1,B2,B3,B4 in the same order than in B column, with no empty cells betwen them.

In this example C1 will get Client 4, C2 Client7, C3 Client 5 and C4 Client 2.

I found a solution but is viable only when I have 20, 30 clients, but for a few hundreds is too much.

Thanks alot for all your help.

Posted by Scott on May 30, 2000 9:34 AM

I think I have the solution to this:
FIRST
Insert a "new" column A (select column A and click Columns from the Insert menu)
Enter this formula in the "new" column A for as many cells as you have Client names: =RIGHT(B1,X)*1. The value X represents how many numbers are in the address. For example, if the value is Client 1, use 1. If the value is Client 234, use 3, etc.

NEXT
Select both column A & B and name them Clients (or whatever you want). The shortcut to this is ctrl+F3 (pulls up the Define Name menu).

NEXT
Now, column C should contain the numerical values you mention above, 4,7,5,2. In cell D1 enter this formula: =VLOOKUP(C1,Clients,2,FALSE). You will now see the client address in cell D1 that corresponds to the value typed in cell C1. From here just copy the formula in cell D1 down for as many cells as you need.

Scott