Equal Values

  • Thread starter Thread starter Maureen Smith
  • Start date Start date
M

Maureen Smith

I have two columns - they are item numbers for things purchased at two
different facilities. One list is over 4000 items long, the other is almost
11,000 items long.

I need to be able to compare these two columns, and see if there are any
identical item numbers.

I've read a bit about "vlookup" but as I don't understand the syntax of the
formula, I don't know if this will work. Especially since the lists are not
even. I don't care, especially, about the row position, I just need to
generate a report of identical item numbers.

Is there a way to do what I need to do?
 
VLOOKUP will work but it will only detect the first instance of a
duplicate.
Is that ok(?), if so;
(assume 4,000 item list in colA (r1->4000) and 11,000 r1->11,000) in col
B.)
In a reporting col (call it x) row 1;
=VLOOKUP(A1,B$1:B$11000,1,False)
(Don't omit the $ signs!)
What the statement says is;
"using the value in Column A (whatever row) search the table defined as
Column B, Rows 1 to 11,000 and,
if there is a match then put the found value in this cell in col x.
If there is not match then display put the value "#N/A" in this cell in
col x."

Now copy it down to r4000.

As noted, if there are duplicate Col B values you'll only get the 1st
hit.

There are (many) other methods to do this but seeing as you asked about
VLOOKUP I've provided a very basic example. Hopefully this basic example
coupled with the Help description (and some experimenting) will get you
on your way.
 
Just another option to try out ..

A simple example to illustrate

Assume you have a list in A1:A10

100
101
102
103
104
105
106
107
108
109

and a shorter list in B1:B5

103
107
101
120
102

Select C1:C5
(i.e. a range equal to the shorter list)

Put in the *formula bar* :

=IF(ISERROR(SMALL(IF(COUNTIF(A1:A10,B1:B5)>=1,B1:B5),ROW())),"",SMALL(IF(COU
NTIF(A1:A10,B1:B5)>=1,B1:B5),ROW()))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

You'll get in C1:C5 :

101
102
103
107
<blank>

the extracted list of numbers in the shorter list
which appears in the longer list, sorted in ascending order
(i.e. items common to both lists)

Adapt to suit ..
 
I threw VLOOKUP in there only because I'd come across it while searching for
a way to accomplish what I needed to do. I have found a few instances of
multiple duplicates, so it's good that I didn't use this method.

If you can supply some other means to the end, that would be great!
 
You still have 3 other suggestions to try:
Mine, Frank's and Aladin's

Why not look these through and give it a bash?
Maybe start with my suggestion ..
(afterall, my post is 2nd in queue, after RWN's <bg>)
 
Back
Top