Simple Excel formula to purge a simple list

[1qazxc]

Hi,
Nice to be back here.
Im looking for a simple formula in Excel(Simple for most of you im sure.)

I have a row of numbers that will not change - (Employee ID's): (ROW1)
7745
7777
7774
ect... (50 to be exact)

Then I have a row of numbers that change on a daily basis (Employee Id's): (ROW 2)
4568
6547
4123
ect..

***What I want to be able to do is purge all the numbers from row 2 that do not appear in row 1.

I dont care if it highlights or copies these numbers to a new row or file. The idea is that it is simple and easy to use.

I suppose a duplication formula would be adequate I just cant for the life of me recall how to do this.

All your ideas are greatly appreciated!!!

 

[Joemaha]

If your data is in rows A and B:
In Row C:
=IF(A1<>B1,B2,"")
Should give you what you need.

 

[mbarron]

You can use a vlookup for this:

=if(isna(vlookup(B1,A1:A50,1,0),"Not in A","Duplicate")

Mike

 

[mbarron]

Sorry, I missed a ")"

=IF(ISNA(VLOOKUP(B1,A1:A50,1,0)),"Not in A","Duplicate")

Mike

 

[Joemaha]

Think I misunderstood the first time. Try this instead:
=IF(VLOOKUP(B1,$1:$65536,2,FALSE),B1,0)

 

[AnotherHiggins]

ROWS <> COLUMNS!

Storing this data in rows, rather than columns, is unusual at best, 1qazxc .

I'd start by getting all of the rows into columns. This can easily be done by:
[ul][li]select rows 1 & 2[/li]
[li]copy[/li]
[li]select cell a3[/li]
[li]right click>Paste Special>Transpose[/li]
[li]Delete rows 1 & 2[/li][/ul]
Now the data from Row 1 is in column A and the data from Row 2 is in column B.

In C1, type this

=COUNTIF(A:A,B1)

Drag the formula down. Now, because the data is in columns instead of rows, you can sort by column C and delete all rows with a zero.


John

Every generalization is false, including this one.

 

[mbarron]

And I also forgot to make the reference absolute:

=IF(ISNA(VLOOKUP(B1,$A$1:$A$50,1,0)),"Not in A","Duplicate")

Joemaha,
Using your second formula, in its current state, can simplified to:
VLOOKUP(B1,$1:$65536,1,FALSE)

If the vlookup doesn't find a match, the result is not false, but an error.

Mike

 

[byundt]

1qazxc,
Here's an array formula that returns all the values in row 2 that are also in row 1. The formula requires that row 1 does not contain any duplicates. The formula also assumes that you insert the formula in column A and copy it across. As you do, it will return matching values, and then blanks after the last matching value has been found.
=IF(SUM(COUNTIF(1:1,2:2))<COLUMN(),"",INDEX(2:2,1,SMALL(IF(COUNTIF(1:1,2:2)>0,COLUMN(2:2)),COLUMN())))
Array formula, so hold Control + Shift down while hitting Enter. Excel will respond by adding curly braces { } surrounding the equation. This formula works for text strings and numbers.
Brad