Excel merging tables

[Spinal]

Howdy!

A little bit of urgent help...

I have 3 tables in excel, all in the same format:
ID, Value

(e.g.
MATH:
Alice 2
Bob 1
Cheryl 3

ENGLISH:
Alice 1
Doug 5

FRENCH:
Bob 2
John 1
)

What I want/need to do, is merge the ID column, but keep the other colums distinct, so I end up with:
Alice, 2, 1
Bob, 1,,2
Cheryl, 3,
Dough,,5
John,,,1

Any ideas?
M.

 

[tpwuk]

yes, have a look at the "=concatenate" command.

 

[st13phil]

...or vlookup()

More precisely, create yourself a unique list of the ID's, and then use vlookup() to grab the entries from the other tables.

 

[Spinal]

Thanks - ended up importing the tables into Access and doing it from there, at least I knew what I was doing

I did try vlookup (and lookup) but couldn't get either to work...


Oh well

M.

 

[Bobby Dazzler]

Thanks - ended up importing the tables into Access and doing it from there, at least I knew what I was doing

I did try vlookup (and lookup) but couldn't get either to work...


Oh well

M.

Best to ensure that the list you're looking up the value from is sorted in order.

 

[Spinal]

Best to ensure that the list you're looking up the value from is sorted in order.

My issues were:
VLOOKUP: the results array needs to be part of the lookup array, but in a different colum. As both results and lookups are integers, this would mean that the whole thing was messing up

LOOKUP: if the lookup value is not found in the lookup array, it returns the lookup for the largest value less than the lookup value. This makes it totally worthless as it ends up with several hundred false positives.

A union in access and it's all fixed

M.