Need VLOOKUP to miss blanks

**jamesryan** · 04-15-2008, 09:24 AM

Hi

I have about 90+ lists of data which looks like this: (the dots are blank cells)

Set 1
D0 Jimmy
...
D1 Keith
D2 Dave
...
...
D3 Sandra

Set 2
...
D0 Andy
D1 Ryan
...
...
...
...
...
D2 Lee

Each list was originally a set of names and i have populated the "D1, D2" cells (where D1 = the first name, D2 = the second, etc).

Is there any way, on a seperate sheet i can get a list which shows the following (ie. no spaces between lines of data):

Set 1 Set 2
Jimmy Andy
Keith Ryan
Dave Lee
Sandra

Is this possible through a VLOOKUP?

Any help would be terriffic, thanks

James

**jamesryan** · 04-15-2008, 09:29 AM

p.s.

To explain it more clearly, the D0, D1, D2, etc would then be followed by an E0, E1, E2, E3, and then a F0, F1, F2.

What i want is for a set where there is no name for D3, the list goes straight from D0, D1 to E0 (instead of entering a blank for D3)

Thanks

**NBVC** · 04-15-2008, 09:33 AM

Can you post a sample workbook showing the current layout and what you want in the end?

**oldchippy** · 04-15-2008, 09:35 AM

Hi,

If on Sheet3 in column A you list your ID's D0,D1,D2,D3,etc. then in B1

=VLOOKUP(A1,Sheet1!$A$1:$B$10,2,FALSE)&" "&VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE)

**jamesryan** · 04-15-2008, 09:47 AM

Hi,

I'll attach a sample of the document now (its football-related).

What i want is shown in the 'Table' worksheet.

Thanks

**NBVC** · 04-15-2008, 10:05 AM

Will oldchippy's solution work for you?

... you don't have to do the concatenating of the Vlookups though.

**oldchippy** · 04-15-2008, 10:45 AM

Hi,

Not quite like your question, but here's what you want this time

**jamesryan** · 04-15-2008, 11:23 AM

Cheers for that Chippy, however that isn't exactly what i need.

That formula picks the players using the position indicated (G0, D1, F2, etc), however i need a formula to check what positions are in the list as there wont always be a G3 or a D7, etc.

So i want the master sheet to have formulas in it to populate the position column too.

Is that possible?

p.s. the spreadsheet is linked externally to a website which updates the players so even though for one team there may not be a 'D7' and 'M9' now, there may be in the future so the positions need to not be static.

Thanks

James

**jamesryan** · 04-16-2008, 10:41 AM

Hi,

Anyone have a clue about this one?

thanks

**oldchippy** · 04-16-2008, 11:42 AM

Hi James,

Had another look at this and used a pivot table to look at each sheet 100 rows deep, then used the result on a VLOOKUP, it brings up all the players but unfortunately not in the order you were asking for. It's not elegent but could something like this work for you?