Combine 2 lists to 1 unique list

[roelandwatteeuw]

Hi all

Hope you can help me with this one:

I have a list A with data.

List A:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID-nr
[/TD]
[TD]Address
[/TD]
[TD]Meters
[/TD]
[TD]User
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Street A
[/TD]
[TD]123.4
[/TD]
[TD]Marc
[/TD]
[TD]OK
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Street B
[/TD]
[TD]56.7
[/TD]
[TD]Rob
[/TD]
[TD]Problem
[/TD]
[/TR]
</tbody>[/TABLE]

Now I got a new list B with more data. They have the same columns, same data, only the column 'Comment' will be blank. BUT: Not all the lines from list A will be in list B and the other way around.

I paste these lines under the lines from list A.

List B:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID-nr
[/TD]
[TD]Address
[/TD]
[TD]Meters
[/TD]
[TD]User
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Street B
[/TD]
[TD]56.7
[/TD]
[TD]Rob
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Street C
[/TD]
[TD]89.1
[/TD]
[TD]Frank
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to combine these two lists to one unique list.

Conditions:
1. All the lines (ID-nr) from list A that are not in list B, need to be deleted in list A
2. All the lines from list B that are in list A, need to be deleted in list B

The results should be:

Result:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID-nr
[/TD]
[TD]Address
[/TD]
[TD]Meters
[/TD]
[TD]User
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Street B
[/TD]
[TD]56.7
[/TD]
[TD]Rob
[/TD]
[TD]Problem
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Street C
[/TD]
[TD]89.1
[/TD]
[TD]Frank
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What would be the best way to do this?

VBA-code?

Thank you!
Roeland

 

[Learning To Excel]

I would do the following

(1) put the lists together where list A has the column labels and list B is directly beneath list A without any labels
(2) select the entire list (it doesn't matter if you select the labels or not)
(3) go to the data tab
(4) click remove duplicates and unselect all of the columns except the ID-nr
(5) press ok