Help with Duplicates

[Guest]

I need to use duplicates for good! I have two sheets and I need to figure out
two formulas. Both Sheets have similar information. Column A has UPC codes
and Column B has a numeric value. I want the first formula to compare every
UPC in Sheet 1 to every UPC in sheet 2 and if it finds duplicates to give me
the difference between the numeric values.
I want the second formula (or formulas) to reurn the UPC# and it's numeric
value to separate columns (ie...D1,E1) if there is NO duplicate UPC on Sheet
1.

Can anyone help?

 

[Guest]

Hazarding some interps and thoughts here ...

Assuming data in both sheets: Sheet 1, Sheet 2
are in cols A and B, data from row2 down
(UPC codes in col A, values in col B)

1st formula:

... I want the first formula to compare every UPC in Sheet 1
to every UPC in sheet 2 and if it finds duplicates to give me
the difference between the numeric values.

Assumed "find duplicate" means where there's a matching UPC code for the
code in Sheet1's col A in Sheet 2's col A, then do something ..
(above implies there are only unique UPCs in col A in each sheet)

In Sheet 1,

Put in C2:
=IF(ISNA(MATCH(A2,'Sheet 2'!A:A,0)),"",B2-VLOOKUP(A2,'Sheet 2'!A:B,2,0))
Copy down to the last row of data in col A. This returns what you want for
the 1st formula. If there's no match for the code in col A in Sheet 2's col
A, blanks: "" will be returned. Adapt to suit.

2nd formula:

.. I want the second formula (or formulas)
to return the UPC# and it's numeric value
to separate columns (ie...D1,E1) if there is NO duplicate UPC on Sheet 1.

Read the above to mean that you want to compare the UPCs in Sheet 2 to those
in Sheet 1, and if there's no match for the UPCs in Sheet 2's col A in Sheet
1's col A, then to extract all of these UPCs & their corresp numeric values
(those in Sheet 2) ..

In Sheet 2,

Place in C2:
=IF(OR(A2="",'Sheet 1'!$A$2=""),"",IF(ISNUMBER(MATCH(A2,'Sheet
1'!$A:$A,0)),"",ROW()))
(Leave C1 blank)

Place in D2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy D2 to E2. Then just select C2:E2, copy down to last row of data in col
A. Hide away col C. Cols D and E will extract the required results, all
neatly bunched at the top.

---

 

[Guest]

Hazarding some interps and thoughts here ...

Assuming data in both sheets: Sheet 1, Sheet 2
are in cols A and B, data from row2 down
(UPC codes in col A, values in col B)

1st formula:

... I want the first formula to compare every UPC in Sheet 1
to every UPC in sheet 2 and if it finds duplicates to give me
the difference between the numeric values.

Assumed "find duplicate" means where there's a matching UPC code for the
code in Sheet1's col A in Sheet 2's col A, then do something ..
(above implies there are only unique UPCs in col A in each sheet)

In Sheet 1,

Put in C2:
=IF(ISNA(MATCH(A2,'Sheet 2'!A:A,0)),"",B2-VLOOKUP(A2,'Sheet 2'!A:B,2,0))
Copy down to the last row of data in col A. This returns what you want for
the 1st formula. If there's no match for the code in col A in Sheet 2's col
A, blanks: "" will be returned. Adapt to suit.

2nd formula:

.. I want the second formula (or formulas)
to return the UPC# and it's numeric value
to separate columns (ie...D1,E1) if there is NO duplicate UPC on Sheet 1.

Read the above to mean that you want to compare the UPCs in Sheet 2 to those
in Sheet 1, and if there's no match for the UPCs in Sheet 2's col A in Sheet
1's col A, then to extract all of these UPCs & their corresp numeric values
(those in Sheet 2) ..

In Sheet 2,

Place in C2:
=IF(OR(A2="",'Sheet 1'!$A$2=""),"",IF(ISNUMBER(MATCH(A2,'Sheet
1'!$A:$A,0)),"",ROW()))
(Leave C1 blank)

Place in D2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy D2 to E2. Then just select C2:E2, copy down to last row of data in col
A. Hide away col C. Cols D and E will extract the required results, all
neatly bunched at the top.

---

 

[Guest]

Thank you Max!!!

P.S. you assumed correctly

Hazarding some interps and thoughts here ...

Assuming data in both sheets: Sheet 1, Sheet 2
are in cols A and B, data from row2 down
(UPC codes in col A, values in col B)

1st formula:

Assumed "find duplicate" means where there's a matching UPC code for the
code in Sheet1's col A in Sheet 2's col A, then do something ..
(above implies there are only unique UPCs in col A in each sheet)

In Sheet 1,

Put in C2:
=IF(ISNA(MATCH(A2,'Sheet 2'!A:A,0)),"",B2-VLOOKUP(A2,'Sheet 2'!A:B,2,0))
Copy down to the last row of data in col A. This returns what you want for
the 1st formula. If there's no match for the code in col A in Sheet 2's col
A, blanks: "" will be returned. Adapt to suit.

2nd formula:

Read the above to mean that you want to compare the UPCs in Sheet 2 to those
in Sheet 1, and if there's no match for the UPCs in Sheet 2's col A in Sheet
1's col A, then to extract all of these UPCs & their corresp numeric values
(those in Sheet 2) ..

In Sheet 2,

Place in C2:
=IF(OR(A2="",'Sheet 1'!$A$2=""),"",IF(ISNUMBER(MATCH(A2,'Sheet
1'!$A:$A,0)),"",ROW()))
(Leave C1 blank)

Place in D2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy D2 to E2. Then just select C2:E2, copy down to last row of data in col
A. Hide away col C. Cols D and E will extract the required results, all
neatly bunched at the top.

 

[Guest]

Thank you Max!!!

P.S. you assumed correctly

Hazarding some interps and thoughts here ...

Assuming data in both sheets: Sheet 1, Sheet 2
are in cols A and B, data from row2 down
(UPC codes in col A, values in col B)

1st formula:

Assumed "find duplicate" means where there's a matching UPC code for the
code in Sheet1's col A in Sheet 2's col A, then do something ..
(above implies there are only unique UPCs in col A in each sheet)

In Sheet 1,

Put in C2:
=IF(ISNA(MATCH(A2,'Sheet 2'!A:A,0)),"",B2-VLOOKUP(A2,'Sheet 2'!A:B,2,0))
Copy down to the last row of data in col A. This returns what you want for
the 1st formula. If there's no match for the code in col A in Sheet 2's col
A, blanks: "" will be returned. Adapt to suit.

2nd formula:

Read the above to mean that you want to compare the UPCs in Sheet 2 to those
in Sheet 1, and if there's no match for the UPCs in Sheet 2's col A in Sheet
1's col A, then to extract all of these UPCs & their corresp numeric values
(those in Sheet 2) ..

In Sheet 2,

Place in C2:
=IF(OR(A2="",'Sheet 1'!$A$2=""),"",IF(ISNUMBER(MATCH(A2,'Sheet
1'!$A:$A,0)),"",ROW()))
(Leave C1 blank)

Place in D2:
=IF(ROW(A1)>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy D2 to E2. Then just select C2:E2, copy down to last row of data in col
A. Hide away col C. Cols D and E will extract the required results, all
neatly bunched at the top.

 

[Guest]

Delighted to hear that, and thanks for the feedback !

 

[Guest]

Delighted to hear that, and thanks for the feedback !