Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Duplicates
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Duplicates
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rlee1999" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Duplicates
Thank you Max!!!
P.S. you assumed correctly "Max" wrote: 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rlee1999" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Duplicates
Delighted to hear that, and thanks for the feedback !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rlee1999" wrote: Thank you Max!!! P.S. you assumed correctly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
i need to find duplicates! ASAP | Excel Worksheet Functions | |||
Marking Duplicates | Excel Worksheet Functions | |||
Duplicates | Excel Discussion (Misc queries) | |||
Finding Duplicates | Excel Worksheet Functions |