Matching lists in Excel

[tastie]

Hi - hope you can help. I have two list of numbers that I want to create one list of anything that is in both lists.

Example extract:

List 1 List 2
123 456
456 132
789 987
465 321
132 798
798

The new list being:
456
132
798

This is a simple example - my actual data is a few hundred lines.

Hope you can help
Thanks in advance

 

[SkipVought]

Hi,

Copy both lists into one column WITH A ONE CELL HEADER.

Data/Filter/Advanced Filter - Check UNIQUE VALUES - Copy to another location (select or enter the other location)

VOLA!

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[GlennUK]

No Skip, the request was for a list of codes that are in both original lists, and your solution simply gives codes that appear in either.

So tastie, do you mind having a couple of helper columns containing formulae that build toward a solution? I hope not, 'cos here's a solution that does it that way:

Assuming that your 2 lists have their headings ( List 1, List 2 ) in row 1, and that the data is in range A2:A7, B2:B6, then put this formula in cell C2:

=COUNTIF($B$2:$B$6,A2)

and copy down to C7.
Then in cell D2, put this formula:

=IF(C2=0,0,C2+SUM($C$1:C1))

and copy down to cell D7.

Then in cell E2, put this formula:

=IF(ROW()-1>MAX($D$2:$D$7),"",INDEX($A$2:$A$7,MATCH(ROW()-1,$D$2:$D$7,0)))

and copy to cell E7.

Your list will appear in column E.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Glenn,

You're right! I missed that criteria completely!



Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[GlennUK]

Hey Skip, it's not often that happens ... but when you do, I'll always be there to rib you about it

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Just another way:-

Two lists one under the other with List1 or List2 against each entry in the appropriate list. Title 1st column LIST, and second Column NUM.

Data / Pivot table and Chart Report / Next / Next / Finish

Drag NUM to Row fields, LIST to Column fields, NUM again to Data field. Right click on any number in the Row fields, choose Field settings / Advanced / Tick Descending and choose 'COUNT of List' from the 'Using Field' box.

Data will now look like this:-

CntList    LIST	     
     NUM   List1   List2 GrTotal
     798       1       1       2
     456       1       1       2
     132       1       1       2
     987               1       1
     321               1       1
     465       1               1
     789       1               1
     123       1               1
 GrTotal       6       5      11

The ones with a 2 in the Grand Total column are the ones you want.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------