Merging lists that don't fully match

[madsci64]

I have lists that are something like this:

name score
Bob 90
Frank 80
Mary 85

name weight
Alice 100
Bob 180
Frank 200

What I would like to do is merge them, so that rows with the same name are merged and the people who are on only one list are still there.
Like:

name score weight
Alice * 100
Bob 90 180
Frank 80 200
Mary 85 *

Any ideas? And what is this type of action called?

[Richard Buttrey]

I have lists that are something like this:
snipped....

Any ideas? And what is this type of action called?

Hi,

Assuming the two lists are on the same sheet in the same columns, (i.e. one list stacked under the other, say columns A & B), highlight column A and use Data Filter Advanced Unique to extract a unique list of names starting in D1 with the column label 'Name'. If they're not on the same sheet, just extract the names and create a temporary list of names and then do the operation above.

Now name the two lists 'List1' and 'List2. (They can be on separate sheets), and enter the following formula in E2

Please Login or Register  to view this content.

and in F2

Please Login or Register  to view this content.

There's no particular name for this technique, although since I find myself using it a lot maybe there should be
HTH

[madsci64]

Although I understood your suggestion, I couldn't find how to name lists.

[Richard Buttrey]

Although I understood your suggestion, I couldn't find how to name lists.

Just use the Insert--> Name --> Define from the menu, and complete the boxes entering the name 'List1' and selecting the range. Same for List2.

Alternatively, ensure the Formula Bar is active, highlight the range of cells with the cursor and type the Name in the name box.

Rgds

[madsci64]

Cool, thanks a bunch.