I need to create a formula or a Macro that compares data on one worksheet and
it compares it to another worksheet and if it finds a match moves it to a new
worksheet. I tried using the index and match functions but neither seem to
working very well for me. Can anyone help give me an example?

Thank you

You'll find that you'll get better answers if you clearly define the
question.

What data? Compare how? Move where?

The way your question is worded, it would seem that if the letter "a" (for
example) appears anywhere on the first worksheet and also appears anywhere
on the second worksheet then you would move it to a random location on a new
worksheet. I'm sure this is not what you mean.
__________________________________________________ ____________________

"Adurr" wrote in message
...
I need to create a formula or a Macro that compares data on one worksheet
and
it compares it to another worksheet and if it finds a match moves it to a
new
worksheet. I tried using the index and match functions but neither seem
to
working very well for me. Can anyone help give me an example?

Thank you

"Adurr" wrote:
I need to create a formula or a Macro that compares data on one worksheet and
it compares it to another worksheet and if it finds a match moves it to a new
worksheet. I tried using the index and match functions but neither seem to
working very well for me. Can anyone help give me an example?

Try this sample from my archives which seems a good fit:
http://savefile.com/files/655843
Compare B vs A n filter exclusions in C.xls

As-is, the sample illustrates as titled, ie it'll compare values within a
key col in sheet: B with values within a corresponding key col in sheet: A,
then filters exclusions into a new sheet C. Exclusions are the lines in B not
found in A.

If you want it to return the converse in C, ie lines in B found in A,
just amend the criteria col as follows

In C,

As-is, the formula in A2 is:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

Just replace the criteria formula in A2 with:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),RO W(),""))
then copy down

(just do a simple swap of the ROW() & "" values to flag *matches* instead of
non-matches between the key cols in B vs A)

Cols B and C will now return the converse, ie lines in B found in A, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Using the formula that you gave me, I changed them like so:

=IF('Sony Reseller Pricelist eff 6-1'!A2,"",IF(ISNUMBER(MATCH('Sony SI
Pricelist eff 6-18-07 t'!A2,'Sony SI Pricelist eff 6-18-07 t'!A:A+'Sony SI
Pricelist eff 6-18-07 t'!A:A,0)),ROW(),""))

But I am only getting a #Value! in the field so obviously I am missing
something. The two spreadsheets that I am trying to compare a

Sony Reseller Pricelist eff 6-1
Sony SI Pricelist eff 6-18-07 t

I am trying to insert the duplicates into another sheet called Compare. The
data that I am comparing is model numbers which are AlphaNumeric.


"Max" wrote:

"Adurr" wrote:
I need to create a formula or a Macro that compares data on one worksheet and
it compares it to another worksheet and if it finds a match moves it to a new
worksheet. I tried using the index and match functions but neither seem to
working very well for me. Can anyone help give me an example?

Try this sample from my archives which seems a good fit:
http://savefile.com/files/655843
Compare B vs A n filter exclusions in C.xls

As-is, the sample illustrates as titled, ie it'll compare values within a
key col in sheet: B with values within a corresponding key col in sheet: A,
then filters exclusions into a new sheet C. Exclusions are the lines in B not
found in A.

If you want it to return the converse in C, ie lines in B found in A,
just amend the criteria col as follows

In C,

As-is, the formula in A2 is:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

Just replace the criteria formula in A2 with:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),RO W(),""))
then copy down

(just do a simple swap of the ROW() & "" values to flag *matches* instead of
non-matches between the key cols in B vs A)

Cols B and C will now return the converse, ie lines in B found in A, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

OK I downloaded your spreadsheet and it was great, I was able to find
unmatched entries, I changed your formula to find matched entries like so:

=IF(ROW(A1)COUNT($A:$A),INDEX(SonySI!A:A,SMALL($A :$A,ROW(A1))),"")

Everything comes up blank, I have missed something, I just don't know what.

"Max" wrote:

"Adurr" wrote:
I need to create a formula or a Macro that compares data on one worksheet and
it compares it to another worksheet and if it finds a match moves it to a new
worksheet. I tried using the index and match functions but neither seem to
working very well for me. Can anyone help give me an example?

Try this sample from my archives which seems a good fit:
http://savefile.com/files/655843
Compare B vs A n filter exclusions in C.xls

As-is, the sample illustrates as titled, ie it'll compare values within a
key col in sheet: B with values within a corresponding key col in sheet: A,
then filters exclusions into a new sheet C. Exclusions are the lines in B not
found in A.

If you want it to return the converse in C, ie lines in B found in A,
just amend the criteria col as follows

In C,

As-is, the formula in A2 is:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

Just replace the criteria formula in A2 with:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),RO W(),""))
then copy down

(just do a simple swap of the ROW() & "" values to flag *matches* instead of
non-matches between the key cols in B vs A)

Cols B and C will now return the converse, ie lines in B found in A, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Did you change the criteria formula in A2 like I mentioned in my response,

Just replace the criteria formula in A2 with:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),RO W(),""))
then copy down

It should work fine. Try it again.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Adurr" wrote:
OK I downloaded your spreadsheet and it was great, I was able to find
unmatched entries, I changed your formula to find matched entries like so:

=IF(ROW(A1)COUNT($A:$A),INDEX(SonySI!A:A,SMALL($A :$A,ROW(A1))),"")

Everything comes up blank, I have missed something, I just don't know what.

Yes, I believe that I did, this is the formula that I am using

=IF(SonySI!A2="","",IF(ISNUMBER(MATCH(SonySI!A2,So nyReseller!A:A,0)),ROW(),""))

I either get a blank response now or I get the number the row, but not the
content of the row.

"Max" wrote:

Did you change the criteria formula in A2 like I mentioned in my response,

Just replace the criteria formula in A2 with:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),RO W(),""))
then copy down

It should work fine. Try it again.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Adurr" wrote:
OK I downloaded your spreadsheet and it was great, I was able to find
unmatched entries, I changed your formula to find matched entries like so:

=IF(ROW(A1)COUNT($A:$A),INDEX(SonySI!A:A,SMALL($A :$A,ROW(A1))),"")

Everything comes up blank, I have missed something, I just don't know what.

I'm not sure what's happening there. I suppose you did copy A2 down <g. It
should work ok.

Here's a quick working sample,
with the sheets renamed to suit your actuals:
http://cjoint.com/?gApXb22mgF
Compare_B_vs_A_n_filter_matched lines_in_C.xls

Take a look at the sample ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Adurr" wrote:
Yes, I believe that I did, this is the formula that I am using

=IF(SonySI!A2="","",IF(ISNUMBER(MATCH(SonySI!A2,So nyReseller!A:A,0)),ROW(),""))

I either get a blank response now or I get the number the row, but not the
content of the row.