• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Two Lists - Extract Different Records

Portucale

Member
Hi,

I received a list every day which I need to compare with the existent list and extract records which are not in the existent list, like:

Existent List
10
11
13
15
16

New List
10
11
12
14
15
16
17
18

When I compare both lists I see that "New List" contains; 12;14;17 & 18 as not common with "Existent List" so, the final result would be:

Final List
10
11
13
15
16

12
14
17
18


In essence is just to add the records that New List contains, but aren't common in the Existent List.

Any help is appreciated,

Thanks,
 
Hi Portucale -

You can do this using pivot tables...

Suppose Column A contain Existing List and Column B Contains new list..

1) Press ALT + D + P
2) Select Multiple consolidation ranges from Step 1 of 3 and click next
3) Step 2a of 3 - Click on Create a single page field for me and then click next..
4) Step 2b of 3 - Select the entire range data table, then click on Add button and then click Next
5) Then if you wish to create the pivot on new worksheet..then select new worksheet otherwise existing worksheet..and click on Finish button
6) Then see the below image..as to what you need to select..

upload_2014-11-28_22-11-37.png

I am working on formula driven solution will share it with you soon..
 
Last edited:
Formula solution -

Suppose existing list is in A2:A6
And new list is in B2:B9
Then in D2 put the below formula and drag it down..

Note: Array enter the formula..

IFERROR(IFERROR(INDEX($A$2:$A$6, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$6), 0)), INDEX($B$2:$B$9, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$9), 0))), "")
 
Back
Top