combining several lists into one?

[Omics]

I have several lists (for example: list 1 to 4) and would like to combine
them into a summary list. Does anybody know a good way to do this. Thanks a
lot.

Example:
List 1:
A 10
B 20
C 0.1
D 3
E 100
F 0.01
G 5
H 10
I 1
J 10


List 2:
A 11
B 21
C 0.5
D 6
E 10
F 0.2
G 5
H 10
M 5
N 6


List 3:
A 15
B 30
C 0.9
D 9
E 10
F 0.2
K 11
H 10
M 5
Z 1


List 4:
A 20
B 40
C 1.5
D 12
E 10
X 3
K 11
H 10
M 5
Z 2


Summary List
A 10 11 15 20
B 20 21 30 40
C 0.1 0.5 0.9 1.5
D 3 6 9 12
E 100 10 10 10
F 0.01 0.2 0.2 NA
G 5 5 NA NA
H 10 10 10 10
I 1 NA NA NA
J 10 NA NA NA
K NA NA 11 11
M NA 5 5 5
N NA 6 NA NA
X NA NA 3 NA
Z NA NA 1 2

 

[rspowell]

I have several lists (for example: list 1 to 4) and would like to combine
them into a summary list. Does anybody know a good way to do this. Thanksa
lot.

Example:
List 1:
A       10
B       20
C       0.1
D       3
E       100
F       0.01
G       5
H       10
I       1
J       10

List 2:
A       11
B       21
C       0.5
D       6
E       10
F       0.2
G       5
H       10
M       5
N       6

List 3:
A       15
B       30
C       0.9
D       9
E       10
F       0.2
K       11
H       10
M       5
Z       1

List 4:
A       20
B       40
C       1.5
D       12
E       10
X       3
K       11
H       10
M       5
Z       2

Summary List
A       10      11      15      20      
B       20      21      30      40
C       0.1     0.5     0.9     1.5
D       3       6       9       12
E       100     10      10      10
F       0.01    0.2     0.2     NA
G       5       5       NA      NA
H       10      10      10      10
I       1       NA      NA      NA
J       10      NA      NA      NA
K       NA      NA      11      11
M       NA      5       5       5
N       NA      6       NA      NA
X       NA      NA      3       NA
Z       NA      NA      1       2

Set up something like this ...


List 1 List 2 List 3 List 4
A
B
C
D
E
etc

Then use a VLOOKUP function to get the values matching criteria "A",
"B", "C", etc, from each of the other individual lists ...

=VLOOKUP($A2,Range_List1,2,FALSE)

.... the argument 'Range_List1' needs to be the range address (ie.
A10:B20) for your lists ... List 1, List 2, List 3 -- as they
correspond to each of your columns

Hope it helps you !

- Rodney POWELL
Microsoft MVP - Excel

www.BeyondTechnology.com