Incrementally Update QTY with each MATCH while Looping Challenge?

ChrisOK · Jan 31, 2014

I'm trying to figure out a way to Incrementally Update the QTY each time a match is found while bouncing it against another sheet within the same workbook.

Here's an example:
Start with "PARTS" sheet, row 2, look for this part # "12345" in the "INVENTORY" sheet, row 2.
If match = turn both part # cells BOLD FONT on both sheets.
Notice that the QTY is 5 on the "INVENTORY" sheet,
COPY the QTY of the "INVENTORY" sheet of '5' to the "PARTS" sheet. (overwrite the '1') this 1st time thru the loop.

Continue to look for that SAME part # on row 3 of the "INVENTORY" sheet.
If no match = continue to row 4 on "INVENTORY" sheet.
If another match is found on row 4 of "INVENTORY" sheet, turn the part # cells BOLD FONT
then SUM the QTY shown on the "INVENTORY" sheet '5' to what was previously pasted on the "PARTS" sheet '5'.
The "PARTS" sheet should now show the updated QTY of '10'

Continue with loop, an look to row 5 of "INVENTORY" sheet for additional matches...
If another match is found on row 5 of "INVENTORY" sheet, turn that part # cell BOLD FONT
then SUM the QTY shown on the "INVENTORY" sheet '15' to what was previously pasted on the "PARTS" sheet '10.
The "PARTS" sheet should now show the updated QTY of '25.
THE GOAL is to CONTINUALLY UPDATE THE QTY OF THE "PARTS" sheet based on matches/qtys reported on "INVENTORY" sheet.

Walk Thru Steps That Occured:
The QTY of 1 got overwritten and changed to 5 on the first match pass...
Then QTY got updated to QTY of 10 (5+5) found on the 2nd match pass...
Then QTY got updated to QTY of 25 (10+15) found on that last match pass..

===============================
...SHEET: "PARTS"
1..DESC......PART#.....QTY....
-----------------------------
2..bolt........12345......1......
3..clamp.....78787.......7......
4..screw.....12345.......9......

===============================
.
===============================

...SHEET: "INVENTORY"
1..QTY....DESC......PART#.....
----------------------------------
2..5......bolt......12345.....
3..3......wing......94566.....
4..5......bolt......12345.....
5..15.....bolt......12345.....
===============================

ChrisOK · Feb 4, 2014

ok, 50% there! All of the required Part #'s on the INVENTORY sheet did highlight correctly however!
.. all Part #'s on the Parts list also highlighted whether or not they had a match.

Here's what the two sheets should have bolded:
Here's the AFTER view:
(what the sheet should look like after the macro runs)
===============================
...SHEET1: "PARTS" (looks to the Inventory sheet for a match and updates Qty on this sheet if found)
(both sheets should turn the Part# bold if a match is true/found)

1..DESC......PART#.....QTY....
-----------------------------
2..bolt........12345......25......
3..clamp.....78787.......0.......(found no matches and should not be highlighted)
4..screw.....12345.......25......
===============================
===============================

...SHEET2: "INVENTORY"
1..QTY....DESC......PART#.....
----------------------------------
2..5......bolt......12345.....
3..3......wing......94566.....
4..5......bolt......12345.....
5..15.....bolt......12345.....
===============================

pleeseemailme · Feb 4, 2014

Gotcha. Try this:

Code:

Sub BoldParts()Dim cell As Range
Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("Parts").Range("B:B")) + 1
    Nmbr = Sheets("Parts").Range("B" & i)
    For Each cell In Sheets("Inventory").Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("Parts").Range("B" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

ChrisOK · Feb 4, 2014

HOLY SMOKES!!! I think we've got a winner!!!

Here's the final code that appears to work end-to-end! CHEERS to "pleeseemailme"!

Code:

Sub IncrementalMatchSumTotQty()
'
'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM THE TOTAL QTYS OF EACH 
'OF THE MATCHES FOUND.
'EX: IF IT FINDS PART #12345 WITH QT OF (10) AND FINDS IT AGAIN WITH (5) AND FINDS IT A 3RD TIME WITH (5) IT SUMS THE TOTAL TO "20"
'ROLLED UP ON THE PARTS SHEET WHILE USING THE INVENTORY SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.
'SUCCESSFUL MATCHES WILL BE BOLDED ON BOTH SHEETS.


    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC2,INVENTORY!R2C1:R5C1)"
    Range("C2").Select
    
    Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC[-1],INVENTORY!R2C1:R5C1)"
          
    
    
'THIS PART PERFORMS THE BOLD FUNCTION 
'Sub BoldParts()Dim cell As Range

Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("Parts").Range("B:B")) + 1
    Nmbr = Sheets("Parts").Range("B" & i)
    For Each cell In Sheets("Inventory").Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("Parts").Range("B" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
    
    
End Sub

pleeseemailme · Feb 4, 2014

Huzzah!

ChrisOK · Feb 28, 2014

I've been pulling my hair out trying to adjust the references to the "real" working file and something is not working... The formula successfully pastes into the correct cell and copies downward-- but it is not extracting the QTY over as it should... Do you see where the problem might lie?

I'm posting an actual copy of the layout of the working file (filled with dummy data of course) so there's no guessing on the columns, etc.
Both samples start with COLUMN A (so you'll have a point of refc with these small clips)
The "BOM Worksheet (sheet is the same thing as the "Parts" sheet used in the orig scaled down example)
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>PDN: 55555X</th><th>End Itm NSN: 111111111111PR</th><th> </th><th> </th><th> </th><th> </th><th>End Itm Noun:</th><th> </th><th>FRAME, TURBINE P/N 55XX5512345</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th></tr>
<tr><td>ES: </td><td> </td><td> </td><td> </td><td>Production Yr 1</td><td> </td><td>Production Yr 2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TO: 99-F150-99 .FIG 88 & 89</td><td> </td><td> </td><td> </td><td>50</td><td> </td><td>75</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>CI NSN</td><td>Op ID</td><td>Occ Rt</td><td>Occ Ratio</td><td>UPA</td><td>G005M RP%</td><td>Dmds 1 Yr</td><td>New RP% 1</td><td>Dmds 2 Yr</td><td>New RP% 2</td><td>Maint Cost Cd</td><td>UI</td><td>AAC</td><td>ERRC</td><td>Noun</td><td>PN</td><td>CAGE</td></tr>
<tr><td>1234512345123PR</td><td>25JJJ</td><td>100%</td><td>100%</td><td>10</td><td>100%</td><td>0</td><td>0%</td><td>281</td><td>23%</td><td>V</td><td>EA</td><td> </td><td>B</td><td>013684821</td><td>#N/A</td><td>#N/A</td></tr>
<tr><td>2345123456787PR</td><td>25JJJ</td><td>100%</td><td>100%</td><td>1</td><td>100%</td><td>0</td><td>0%</td><td>32</td><td>26%</td><td>V</td><td>EA</td><td>Z</td><td>B</td><td>SUPPORT,TURBINE COM</td><td>1222M49P01</td><td>12345</td></tr>
<tr><td>3453453453455PR</td><td>25JJJ</td><td>100%</td><td>100%</td><td>1</td><td>100%</td><td>0</td><td>0%</td><td>32</td><td>26%</td><td>V</td><td>EA</td><td>Z</td><td>B</td><td>SUPPORT,TURBINE COM</td><td>5555M25P01</td><td>12345</td></tr>
<tr><td>4545454545454PR</td><td>25JJJ</td><td>100%</td><td>100%</td><td>10</td><td>100%</td><td>0</td><td>0%</td><td>320</td><td>26%</td><td>V</td><td>EA</td><td>Z</td><td>B</td><td>LINER,TURBINE COMPO</td><td>6565Y58P001A</td><td>12345</td></tr>
<tr><td>5454545454545PR</td><td>25JJJ</td><td>100%</td><td>100%</td><td>1</td><td>100%</td><td>0</td><td>0%</td><td>33</td><td>27%</td><td>V</td><td>EA</td><td>Z</td><td>B</td><td>SUPPORT,TURBINE COM</td><td>7575K28G04</td><td>12345</td></tr>
</table>

The "TO" (sheet is the same thing as the "Inventory" sheet used in the orig scaled down example)
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>HEADER INFO</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th></tr>
<tr><td>MORE HEADER INFO</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>MORE HEADER INFO</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>MORE HEADER INFO</td><td>PART NO.</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>MORE HEADER INFO</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>MORE HEADER INFO</td><td> </td><td> </td><td>1 2 3 4 5 6 7</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>MORE HEADER INFO</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>/1 </td><td> </td><td> </td><td> </td><td> </td><td>TURBINE SUPPORTS</td><td> </td><td> </td><td> </td></tr>
<tr><td>/1 </td><td>1111FF7G05</td><td> </td><td> </td><td>. </td><td>FRAME, TURBINE /REPLACED BY XYZ</td><td>REF </td><td> </td><td>PAFDD </td></tr>
<tr><td>/1 </td><td>1111FF7G06</td><td> </td><td> </td><td>. </td><td>FRAME, TURBINE /REPLACED BY XYZ</td><td>REF </td><td> </td><td>PAFDD </td></tr>
<tr><td>/1 </td><td>1111FF7G07</td><td> </td><td> </td><td>. </td><td>FRAME, TURBINE /REPLACED BY XYZ</td><td>REF </td><td> </td><td>PAFDD </td></tr>
<tr><td>/1 </td><td>1111FF7G08</td><td> </td><td> </td><td>. </td><td>FRAME, TURBINE /REPLACED BY XYZ</td><td>REF </td><td> </td><td>PAFDD </td></tr>
<tr><td>6/1 </td><td>1222M49P01</td><td> </td><td> </td><td>. . </td><td>SUPPORT, LINER</td><td>50</td><td> </td><td>PAFZZ </td></tr>
<tr><td>7/1 </td><td>1222M49P02</td><td> </td><td> </td><td>. . </td><td>RIVET, SOLID</td><td>110 </td><td> </td><td>PAFZZ </td></tr>
<tr><td>9/1 </td><td>5555M25P01</td><td> </td><td> </td><td>. . </td><td>SUPPORT, LINER</td><td>REF </td><td> </td><td>PADZZ </td></tr>
<tr><td>Figure 2-89 </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>/1 </td><td>6565Y58P001A</td><td> </td><td> </td><td>. </td><td>LINER SEGMENT</td><td>10 </td><td>BCDFGJ </td><td>PAFZZ </td></tr>
<tr><td>13/1 </td><td>45756428G02</td><td> </td><td> </td><td>. </td><td>SUPPORT, LINER </td><td>20</td><td>ADEHK </td><td>XA </td></tr>
<tr><td>/1 </td><td>7575K28G04</td><td> </td><td> </td><td>. </td><td>ENGINE, LOFT</td><td>5</td><td>BCFGJLMN </td><td>PAFBZ </td></tr>
<tr><td>/1 </td><td>7575K28G04</td><td> </td><td> </td><td>. </td><td>ENGINE, LOFT b</td><td>5</td><td>BCFGJLMN </td><td>PAFBZ </td></tr>
</table>

The code:

Code:

Sub Mod_12_BOM2TO()
'...Sub IncrementalMatchSumTotQtyBOM2TO()
'...THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, 
'...IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH OF THE MATCHES FOUND.
'...EX: IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'...ROLLED UP ON THE "BOM Worksheet" SHEET WHILE USING THE "TO" SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.

'========================================================================================
'..first section inserts the formula into COL E, starting with row 5 of the BOM
'..then copies it down as follows:  =SUMIF(TO!$B$8:$B$100,BOM Worksheet!P5,TO!$G$8:$G$100) 
'=========================================================================================

    
    Sheets("BOM Worksheet").Select
    
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,BOM Worksheet!RC16,TO!R8C7:R100C7)"
    Range("E5").Select
    
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,BOM Worksheet!RC[11],TO!R8C7:R100C7)"
    
'========================================================================================
'..2nd section locates the Part #'s that incurred change and turns them BOLD font on the BOM
'.. 
'=========================================================================================          

    
'Sub BoldParts()Dim cell As Range
Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic

    
    
End Sub

So frustrated!

pleeseemailme · Mar 3, 2014

I'm not sure if this will work because it doesn't work with the dummy data. But, try this code with your real data and let me know:

Code:

Sub Mod_12_BOM2TO()'...Sub IncrementalMatchSumTotQtyBOM2TO()
'...THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES,
'...IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH OF THE MATCHES FOUND.
'...EX: IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'...ROLLED UP ON THE "BOM Worksheet" SHEET WHILE USING THE "TO" SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.


'========================================================================================
'..first section inserts the formula into COL E, starting with row 5 of the BOM
'..then copies it down as follows:  =SUMIF(TO!$B$8:$B$100,BOM Worksheet!P5,TO!$G$8:$G$100)
'=========================================================================================


    
    Sheets("BOM Worksheet").Select
    
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    Range("E5").Select
    
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7)"
    
'========================================================================================
'..2nd section locates the Part #'s that incurred change and turns them BOLD font on the BOM
'..
'=========================================================================================


    
'Sub BoldParts()Dim cell As Range
Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic


    
    
End Sub

I also notice that the loop has an error in the dummy data. It is set to count the values in column P, yet there are no values; they are all text. So the loop is skipped completely. in order for this to work, all values in column P must be numeric.

pleeseemailme · Mar 3, 2014

You could change the line 'Dim Nmbr As Double' to 'Dim Nmbr As String' and that will work with alphanumeric values. It won't work with errors and it will input numbers as text. I'm not sure if that's a problem, but let me know.

ChrisOK · Mar 3, 2014

Nope, when I ran it with the following code, it zeroes out ALL existing counts/QTYs within COL E of the BOM Worksheet and does not bring over any Counts/Qtys found on the TO sheet.

The Column P you are referring to is a PART # column and is only used as a matching qualifier.
Example: IF Part # in Col P of the BOM Worksheet finds a match to the PART # Col B of the "TO" worksheet,
.............THEN UPDATE THE QTY stored in COL E of the BOM Worksheet with whatever the QTY is in COL G of the TO sheet.
......................*CAVEAT: If more than one match is found, then incrementally update the SUM of all the findings found on the TO sheet and place that total QTY into COL E of the BOM Worksheet.

Example:
............Part#123 of the BOM is found on the TO sheet w/ QTY of 10
....................ACTION: (it pastes "10" into COL E of the BOM Worksheet)
The code moves to next row and finds:
............Part#555 of the BOM is found on the TO sheet w/ QTY of 25
............it keeps moving down the TO sheet looking for any other matches for PN#555 and finds another with a QTY of 10
....................ACTION: (it pastes the SUM of 25+10 as "35" into COL E of the BOM Worksheet)
It is also smart enough to overcome any text that might be found...
...........Example:
............Part#777 of the BOM is found on the TO sheet w/ QTY of "REF" (the cell is actually holding a code called "REF")
............it will simply copy the word "REF" into the TO Col E that it is supposed to be copying QTY into -- and keeps moving...
............(there will never be an instance where it finds two rows when "REF" is present... therefore, the SUM functionality shouldnt encounter any problems trying to SUM a REF+REF.)

Code:

Sub Mod_12_BOM2TO()
'Sub IncrementalMatchSumTotQtyBOM2TO()
'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH
'OF THE MATCHES FOUND.
'IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'ROLLED UP ON THE PARTS SHEET WHILE USING THE INVENTORY SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.

    
    Sheets("BOM Worksheet").Select
    
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    Range("E5").Select
    
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7)"


  Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic

    
End Sub

pleeseemailme · Mar 3, 2014

I can't find anything wrong with the code. It sounds like the error is within the formula entered in the first part of the code. But I can't find anything wrong with the formula either. The formula I have in E6 in my test is: =SUMIF(TO!$B$8:$B$100,'BOM Worksheet'!P6,TO!$G$8:$G$100) and it returns a value of 50. You are saying your formula in column E is returning all zeros?

ChrisOK · Mar 3, 2014

I checked my cell E6 and it matches your formula identically.
Puzzled, I opened another test file and re-ran the code and got 50 like you --- so something must be wrong with this new test file... Grrr
I will compare the 2 test files side by side and figure out what's going on -- (will advise)
Thanks, hope you're having warmer weather down south! Freezin' up here with snow and ice! (but looking forward to an April trip down to HOU then fishing in Galveston)...

Incrementally Update QTY with each MATCH while Looping Challenge?

ChrisOK

Well-known Member

ChrisOK

Well-known Member

Excel Facts

pleeseemailme

Board Regular

ChrisOK

Well-known Member

pleeseemailme

Board Regular

ChrisOK

Well-known Member

pleeseemailme

Board Regular

pleeseemailme

Board Regular

ChrisOK

Well-known Member

pleeseemailme

Board Regular

ChrisOK

Well-known Member

Similar threads

Forum statistics

Share this page

Incrementally Update QTY with each MATCH while Looping Challenge?

Well-known Member

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock