Creating a list from two fields of numbers

[Redder Lurtz]

Using 2007 here. I've attached a small sample. I've identified that some numbers appear in both lists. What I'd like to do is create a list of those numbers. Also is there a way of creating a list of numbers which do NOT appear in both lists?

Thanks in advance

RL.

[darknation144]

Answered my own question

[NBVC]

Change your formula in D2 to:

=IF(COUNTIF($B$2:$B$30,A2)>0,COUNT(D$1:D1)+1,"")

copied down

and in E2:

=IF(COUNTIF($B$2:$B$30,A2)>0,COUNT($E$1:$E1)+1,"")

Then in your summary column, say G2:

=IFERROR(INDEX(A$2:A$30,MATCH(ROWS($A$1:$A1),D$2:D$30,0)),"")

copied down and to next column.

[Fermented Reptile]

Hi there,

There's probably a more elegant way of doing this but...

If you copy both lists into a single column (I used J) and go to Data > Remove duplicates. Then enter in K and copy down:

=SUM(COUNTIF($A$2:$A$30,J2),COUNTIF($B$2:$B$30,J2))

Sort to give you the lowest occurences - i.e. the 1's, then delete the 2's..

See attached. As I say, a bit hacky - but it works!

HTH,

Fermented S

[Andrew-R]

I put the following formula in cell C2:

=IF(ROW()-1>SUM(--(NOT(ISNA(MATCH(INDEX($A$2:$A$30,0),INDEX($B$2:$B$30,0),0))))),"",INDEX($B$2:$B$30,LARGE(IF(ISNA(MATCH(INDEX($A$2:$A$30,0),INDEX($B$2:$B$30,0),0)),0,MATCH(INDEX($A$2:$A$30,0),INDEX($B$2:$B$30,0),0)),ROW()-1)))

As it's an array formula it must be entered using Ctrl-Shift-Enter, but can then be copied down to row 30 and will list all of the numbers in column A which also appear in column B.

[Redder Lurtz]

Thanks everyone, some real inspiration here. Thank you.

RL