How do I compare two worksheets where added value will copy to sheets3
Solved/Closed
aze_123
Posts
1
Registration date
Monday September 6, 2021
Status
Member
Last seen
September 6, 2021

Sep 6, 2021 at 09:23 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022  Sep 14, 2021 at 11:52 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022  Sep 14, 2021 at 11:52 AM
Related:
 How do I compare two worksheets where added value will copy to sheets3
 Compare two worksheets and paste differences to another sheet  excel vba free download ✓  Forum  Excel
 Macro to compare 2 sheets and copy differences ✓  Forum  Excel
 VBA Compare 2 sheets and output difference to 3rd sheet  Forum  Excel
 Macro to compare two excel sheets ✓  Forum  Excel
 How to compare two Excel sheets with varying data ✓  Forum  Excel
2 replies
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Sep 9, 2021 at 10:56 AM
Sep 9, 2021 at 10:56 AM
Hi Aze,
Your query is unclear to me.
The quantity sum of sheet1 = 2, for sheet2 it's 3 and on sheet3 you want a result of 3. Does that mean you only want to display the sum of part 001 from sheet2 on sheet3?
Or maybe you mean:
sheet3
P/N Description Quantity
001 DIODE 2 (= the sum of sheet1) >1 (= the sum of sheet2  the sum of sheet1)
Best regards,
Trowa
Your query is unclear to me.
The quantity sum of sheet1 = 2, for sheet2 it's 3 and on sheet3 you want a result of 3. Does that mean you only want to display the sum of part 001 from sheet2 on sheet3?
Or maybe you mean:
sheet3
P/N Description Quantity
001 DIODE 2 (= the sum of sheet1) >1 (= the sum of sheet2  the sum of sheet1)
Best regards,
Trowa
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Sep 13, 2021 at 11:46 AM
Sep 13, 2021 at 11:46 AM
Hi Aze,
How about this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & IF(SUMIF(Sheet1!A:A,1,Sheet1!C:C)>SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C),">",IF(SUMIF(Sheet1!A:A,1,Sheet1!C:C)<SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C),"<","=")) & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
This formula also determines which symbol (< , > , =) to place in between the numbers.
When you want to always be >, then use this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & ">" & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
Best regards,
Trowa
How about this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & IF(SUMIF(Sheet1!A:A,1,Sheet1!C:C)>SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C),">",IF(SUMIF(Sheet1!A:A,1,Sheet1!C:C)<SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C),"<","=")) & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
This formula also determines which symbol (< , > , =) to place in between the numbers.
When you want to always be >, then use this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & ">" & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
Best regards,
Trowa
Hi Trowad,
Yes, I want it always >, I've try the formula, but all result is 0>0, it something wrong maybe with me. Sorry I'm quite new beginner. Hope u can help me.
This is my sheet1:
P/N Comment Description Designator Feature Footprint LibRef MPN Price Process Quantity
001241880 GN1G DIODE D102 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D104 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D105 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D107 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D108 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D110 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D112 DGAC10SRZ DIODE Top Reflow 1
001270700 RBR5LAM40ATFTR ZENER DIODE D111 CHD_SOD128_ROHM ZENER RBR5LAM40ATFTR Top Reflow 1
001430217 UDZTE17 4.3B ZENER DIODE D100 DGAC33SRZ_BT ZENER Bottom Reflow 1
001430217 UDZVTE174.3B ZENER DIODE D308 DGAC33SRZ ZENER Top Reflow 1
001430220 UDZTE17 5.6B ZENER DIODE D101 DGAC33SRZ_BT ZENER Bottom Reflow 1
001430221 UDZ6.2B ZENER DIODE D106 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D109 DGAC33SRZ ZENER Top Reflow 1
Sheet2:
P/N Comment Description Designator Feature Footprint LibRef MPN Price Process Quantity
001241880 GN1G DIODE D102 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D104 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D105 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D107 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D108 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D110 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D112 DGAC10SRZ DIODE Top Reflow 1
001270700 RBR5LAM40ATFTR ZENER DIODE D111 CHD_SOD128_ROHM ZENER RBR5LAM40ATFTR Top Reflow 1
001430217 UDZTE17 4.3B ZENER DIODE D100 DGAC33SRZ ZENER Top Reflow 1
001430217 UDZVTE174.3B ZENER DIODE D308 DGAC33SRZ ZENER Top Reflow 1
001430220 UDZTE17 5.6B ZENER DIODE D101 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D106 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D109 DGAC33SRZ ZENER Top Reflow 1
Sheet3:
0>0
0>0
0>0
0>0
Thanks
Yes, I want it always >, I've try the formula, but all result is 0>0, it something wrong maybe with me. Sorry I'm quite new beginner. Hope u can help me.
This is my sheet1:
P/N Comment Description Designator Feature Footprint LibRef MPN Price Process Quantity
001241880 GN1G DIODE D102 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D104 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D105 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D107 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D108 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D110 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D112 DGAC10SRZ DIODE Top Reflow 1
001270700 RBR5LAM40ATFTR ZENER DIODE D111 CHD_SOD128_ROHM ZENER RBR5LAM40ATFTR Top Reflow 1
001430217 UDZTE17 4.3B ZENER DIODE D100 DGAC33SRZ_BT ZENER Bottom Reflow 1
001430217 UDZVTE174.3B ZENER DIODE D308 DGAC33SRZ ZENER Top Reflow 1
001430220 UDZTE17 5.6B ZENER DIODE D101 DGAC33SRZ_BT ZENER Bottom Reflow 1
001430221 UDZ6.2B ZENER DIODE D106 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D109 DGAC33SRZ ZENER Top Reflow 1
Sheet2:
P/N Comment Description Designator Feature Footprint LibRef MPN Price Process Quantity
001241880 GN1G DIODE D102 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D104 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D105 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D107 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D108 DGAC10SRZ_BT DIODE Bottom Reflow 1
001241880 GN1G DIODE D110 DGAC10SRZ DIODE Top Reflow 1
001241880 GN1G DIODE D112 DGAC10SRZ DIODE Top Reflow 1
001270700 RBR5LAM40ATFTR ZENER DIODE D111 CHD_SOD128_ROHM ZENER RBR5LAM40ATFTR Top Reflow 1
001430217 UDZTE17 4.3B ZENER DIODE D100 DGAC33SRZ ZENER Top Reflow 1
001430217 UDZVTE174.3B ZENER DIODE D308 DGAC33SRZ ZENER Top Reflow 1
001430220 UDZTE17 5.6B ZENER DIODE D101 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D106 DGAC33SRZ ZENER Top Reflow 1
001430221 UDZ6.2B ZENER DIODE D109 DGAC33SRZ ZENER Top Reflow 1
Sheet3:
0>0
0>0
0>0
0>0
Thanks
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
>
aze_123
Sep 14, 2021 at 11:52 AM
Sep 14, 2021 at 11:52 AM
Hi Aze,
No worries, we'll continue until we get it right!
Since you always want the > symbol, let's look at the first part of this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & ">" & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
First part:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C)
=SUMIF(Column containing values you want to get the sum of; The value you want to look up in the previous mentioned column; Column with numbers you want to get the sum of)
Column containing values you want to get the sum of:
You gave 4 result on sheet3, so I looked for 4 unique values in your sample data. It seems column B meets the criteria: GN1G, RBR5LAM40ATFTR, UDZTE17, UDZ6.2B (assuming UDZVTE17 is a typo)
The value you want to look up in the previous mentioned column:
Here you will enter 1 of the values GN1G, RBR5LAM40ATFTR, UDZTE17, UDZ6.2B between double apostrophes. To get the result for GN1G, you will enter "GN1G". You can also refer to a cell containing this value.
Column with numbers you want to get the sum of:
This will be the last column with the quantities.
I count 9 columns used, so the first part of the formula would look like:
=SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I)
The rest of the formula is more of the same:
=SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I) & ">" & SUMIF(Sheet2!B:B,"GN1G",Sheet2!I:I)SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I)
The best and easiest way would be to place the unique column B values in a column on sheet3 (I'll use column A). To do this, copy column B from sheet1 to column A of sheet3. With column A of sheet3 selected, go to the Data ribbon to find the option to remove duplicates. Now your formula in column B of sheet3 will look like this:
=SUMIF(Sheet1!B:B,A1,Sheet1!I:I) & ">" & SUMIF(Sheet2!B:B,A1,Sheet2!I:I)SUMIF(Sheet1!B:B,A1,Sheet1!I:I)
Now you only have to drag the formula down to change the unique value, instead entering it individualy.
Hopefully I've created some understanding of how the formula works, so you can alter it, if you want to change something.
Feel free to come back for more assistance.
Best regards,
Trowa
No worries, we'll continue until we get it right!
Since you always want the > symbol, let's look at the first part of this formula:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C) & ">" & SUMIF(Sheet2!A:A,1,Sheet2!C:C)SUMIF(Sheet1!A:A,1,Sheet1!C:C)
First part:
=SUMIF(Sheet1!A:A,1,Sheet1!C:C)
=SUMIF(Column containing values you want to get the sum of; The value you want to look up in the previous mentioned column; Column with numbers you want to get the sum of)
Column containing values you want to get the sum of:
You gave 4 result on sheet3, so I looked for 4 unique values in your sample data. It seems column B meets the criteria: GN1G, RBR5LAM40ATFTR, UDZTE17, UDZ6.2B (assuming UDZVTE17 is a typo)
The value you want to look up in the previous mentioned column:
Here you will enter 1 of the values GN1G, RBR5LAM40ATFTR, UDZTE17, UDZ6.2B between double apostrophes. To get the result for GN1G, you will enter "GN1G". You can also refer to a cell containing this value.
Column with numbers you want to get the sum of:
This will be the last column with the quantities.
I count 9 columns used, so the first part of the formula would look like:
=SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I)
The rest of the formula is more of the same:
=SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I) & ">" & SUMIF(Sheet2!B:B,"GN1G",Sheet2!I:I)SUMIF(Sheet1!B:B,"GN1G",Sheet1!I:I)
The best and easiest way would be to place the unique column B values in a column on sheet3 (I'll use column A). To do this, copy column B from sheet1 to column A of sheet3. With column A of sheet3 selected, go to the Data ribbon to find the option to remove duplicates. Now your formula in column B of sheet3 will look like this:
=SUMIF(Sheet1!B:B,A1,Sheet1!I:I) & ">" & SUMIF(Sheet2!B:B,A1,Sheet2!I:I)SUMIF(Sheet1!B:B,A1,Sheet1!I:I)
Now you only have to drag the formula down to change the unique value, instead entering it individualy.
Hopefully I've created some understanding of how the formula works, so you can alter it, if you want to change something.
Feel free to come back for more assistance.
Best regards,
Trowa
Sep 10, 2021 at 06:02 AM
thanks