Incrementally Update QTY with each MATCH while Looping Challenge?

[ChrisOK]

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.....
===============================

 

[pleeseemailme]

it will be good weather then.

 

[ChrisOK]

I've checked all the cells for format type, checked the formulas, checked to see if zero was a valid result (but it's not) and don't see any variance that would be causing the one sheet to bring QTYs over and the other to zero everything out?

The 2nd puzzling thing is -- I've found that in doing "F8" through line by line, after hitting F8 on this line of code:

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

and arriving onto this line of code: Application.Calculation = xlCalculationManual, it instantly generates all the zeros in Col E and as a result of all the zeros, Col H and Col J generates "#DIV/0!" errors.

Obviously, need to figure out WHY it generates zeros in all cells of Col E but it brings to surface an issue that could arise when zero is a true result.

Is there a way to correct DIVs from happening within the pre-existing formulas sitting in H & J using a line of code to repaste in the 2 functions below with something included that would correct the DIV when a zero occurs?

Here's the formula in H & J (that are apparently not ready to handle zero results)

=IF(Prod_Yr1>0,IF((Dmd1yr*OccRatio)/(Prod_Yr1*UPA)>1,1,CEILING((Dmd1yr*OccRatio)/(Prod_Yr1*UPA),0.01)),0)

and in J
=IF(Prod_Yr1 + Prod_Yr2>0,IF((Dmd1yr + Dmd2yr*OccRatio)/((Prod_Yr1 + Prod_Yr2)*UPA)>1,1, CEILING((Dmd1yr + Dmd2yr*OccRatio)/((Prod_Yr1 + Prod_Yr2)*UPA),0.01)),0)

 

[ChrisOK]

OK. I've found the issue -- it has to do with the format of the data... but not sure how to fix it...

The small sample file I posted for you was all hand typed -- clean data ---

The real file I have, holds data generated from a database.
I tried the TRIM function on the PART # on the BOM and ran the code, no luck, got zeros
Tried the TRIM function on the other PART # on the TO, ran the code, no luck (trying to narrow down which one of the two might be the culprit)
Tried TRIM on both (BOM and TO) to mk sure BOTH were clean then, ran code -- still zeros.... confirming it was not THAT type of issue...

I had already checked the format types on both and both say: ISTEXT="TRUE"

I had already checked the other format setting and both say "GENERAL"

So I was stumped...

Then I tried taking a PART # from the BOM sheet and pasted it onto the TO sheet (also pasted Qty, etc) the ran the code and it worked!!
(with this, I made the data an exact match -- so when the BOM looked to the TO, it FOUND its match and fed the QTY back to the TO as it should.

For some reason, the data doesnt think it is finding a match, when matches ARE on the TO sheet...
So, how can I forcibly make the PN on the BOM look identical to the PN on the TO...
I tried putting the code line to force "GENERAL" but it doesnt seem to have the affect - needed in this case...

The puzzling thing is --- it already APPEARS that they are identical with BOTH being "TEXT" and BOTH being "GENERAL" --- I don't know what else to do to make them "talk" to one another...
ARGGGGGGGGHHHH!

 

[ChrisOK]

same it true with the hand-typed numbers in the TO in COL G.....
if they are handtyped, they are fed over to the BOM sheet perfectly
if not, they don't get fed to the BOM sheet

 

[pleeseemailme]

Can you provide any of the actual data you are using? Even small portions of it? If not, try using the EXACT formula to see if Excel thinks they are the same.

 

[ChrisOK]

Here's an extremely small clip with important info changed. The key columns in question are original and will "fail" (zero out) when the code it run.
Here's the BOM Worksheet tab
<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: 12345</th><th>End Itm NSN: 55555555PR</th><th> </th><th> </th><th> </th><th> </th><th>End Itm Noun:</th><th> </th><th>xyz</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </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><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TO: 2-222-222</td><td> </td><td> </td><td> </td><td>0</td><td> </td><td>0</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </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><td>Fig Ref</td><td>Action Code</td><td>DDR</td><td>Notes</td><td>Collab</td><td>FHZ OHB</td></tr>
<tr><td>356012345</td><td>MR716</td><td>100%</td><td>100%</td><td>1</td><td>100%</td><td>0</td><td>0%</td><td>32</td><td>100%</td><td>A</td><td>EA</td><td>D</td><td>N</td><td>bolt</td><td>1364M49P01</td><td>88888</td><td>2-88-6</td><td> </td><td>0.0000</td><td> </td><td>Y</td><td>0</td></tr>
<tr><td>456055555</td><td>MR716</td><td>100%</td><td>100%</td><td>1</td><td>100%</td><td>0</td><td>0%</td><td>32</td><td>100%</td><td>A</td><td>EA</td><td>D</td><td>N</td><td>nut</td><td>1364M25P01</td><td>88888</td><td>2-88-9</td><td> </td><td>0.0000</td><td> </td><td>Y</td><td>0</td></tr>
<tr><td>456077777</td><td>MR716</td><td>100%</td><td>100%</td><td>10</td><td>100%</td><td>0</td><td>0%</td><td>320</td><td>100%</td><td>A</td><td>EA</td><td>D</td><td>N</td><td>clamp</td><td>1674M58P001A</td><td>88888</td><td>2-89-5</td><td> </td><td>1.0575</td><td> </td><td>Y</td><td>0</td></tr>
</table>

Then the TO tab
<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>55-55555 - CHAPTER xyz- Parts List</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th></tr>
<tr><td>xxx</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Fig33-33-333</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>INDEX NO. SHEET NO.</td><td>PART NO.</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </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>Feb-88</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>aaa</td><td> </td><td> </td><td> </td></tr>
<tr><td>/1 </td><td>1461M87G05</td><td> </td><td> </td><td>. </td><td>bbb</td><td>REF </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>/1 </td><td>1363M55G03</td><td> </td><td> </td><td>. . </td><td>ccc</td><td>1 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>5/1 </td><td>1461M35P001A</td><td> </td><td> </td><td>. . </td><td>ddd</td><td>10 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>6/1 </td><td>1364M49P01</td><td> </td><td> </td><td>. . </td><td>eee</td><td>1 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>7/1 </td><td>AN123335</td><td> </td><td> </td><td>. . </td><td>fff</td><td>110 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>8/1 </td><td>934A853P01</td><td> </td><td> </td><td>. . </td><td>ggg</td><td>110 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>9/1 </td><td>1364M25P01</td><td> </td><td> </td><td>. . </td><td>hhh</td><td>1 </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>10/1 </td><td>1387M86P03</td><td> </td><td> </td><td>. </td><td>iii</td><td>REF </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>/1 </td><td>1387M86P02</td><td> </td><td> </td><td>. </td><td>jjj</td><td>REF </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>Figure 2-89 </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>ABNHZZ</td></tr>
<tr><td>5/1 </td><td>1461M36P001A</td><td> </td><td> </td><td>. </td><td>kkk</td><td>10 </td><td>AEFHK </td><td>ABNHZZ</td></tr>
<tr><td>/1 </td><td>1674M58P001A</td><td> </td><td> </td><td>. </td><td>lll</td><td>10 </td><td>BCDFGJ </td><td>ABNHZZ</td></tr>
<tr><td>/1 </td><td>1784M92P01</td><td> </td><td> </td><td>. </td><td>mmm</td><td>AR </td><td> </td><td>ABNHZZ</td></tr>
</table>

The code:

Sub Mod_12_BOM2TO()
'Sub IncrementalMatchSumTotQtyBOM2TO()
    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    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

Keep in mind, the formulas are fine, we've verified that the formulas work and are identical, its the data itself that makes all the difference in the world.
If I leave it as original data generated by a mainframe system it fails trying to use this data.
If I were to hand type those critical columns with new hand-typed data, the exact same formulas and code work fine.
The critical columns are:
E...QTY of BOM
P...PART # OF bom

B...PART # of TO
G...QTY of TO

If these columns are main frame generated the code won't work
If these columns are hand-typed, it will work fine

Spent the whole day on this -- hopefully you see a way to make the code clean these 4 columns in a way it will work with the code...

 

[pleeseemailme]

So, with your sample data and the code I was able to obtain accurate results. The formula summed fine and all seems to be well. I'm not sure what is going on with the data base import, but whatever it is, it got fixed in the copy/paste to the forum. Also, I noticed the bold part of the macro is not working right, the below code should fix that.

Sub Mod_12_BOM2TO()'Sub IncrementalMatchSumTotQtyBOM2TO()
    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    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 String
Dim x As Double
x = Sheets("TO").Range("B" & Rows.Count).End(xlUp).Row
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.CountA(Sheets("BOM Worksheet").Range("P:P")) + 3
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & x + 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

Finally, all I can think of for the cells-that-look-the-same-but-really-aren't is a couple of debugging formulas. You say you've already tried TRIM, and that will fix most everything that looks the same. You can try CLEAN. You can also try using LEN to see if both cells contain the same number of characters. I don't think it will help but you can try the following formula as an array formula (ctrl+shift+enter to exit the cell, not just enter). =COUNT(IF(EXACT(P5,TO!$B$9:$B$21),1,"")). You should get a pair of braces encompassing the formula. If the count is greater than 0 then matches exist and I'm not sure why the code isn't working.

The best way to implement the TRIM and CLEAN functions is with code, use:

Sub fixit()
Dim cell As Range
For Each cell In Sheets("TO").Range("B8:B100")
cell.Value = WorksheetFunction.[COLOR=#ff0000]Clean[/COLOR](cell.Value)
Next cell
End Sub

But swap 'clean' for 'trim' as needed. Also, fix the range as needed.

I hope this helps. Let me know.

 

[ChrisOK]

How'd you get lost all of a sudden? LOL -- J/K
There's no issues w/ the formula.
VBA should (and is) pasting/down the formula into Column E of the BOM Worksheet (formerly known as Parts).
Yes, it should do it all at once (all the way down).
At that time, the formula performs a lookup/match and extracts data from the TO sheet (formerly known as Inventory) and places it into Col E of BOM Worksheet.
If more than one PN# match is found, it will add up the Tot Sum of all the Qty finds and place that total in Col E.

The problem I'm finding is that because the data is generated from a mainframe system, THE FORMULA is not/(CAN NOT) work properly.
It thinks there are no matches when there are...
Because it THINKS there are no matches, it is returning zeros all the way down...

When I tried hand-typing the data in those columns the formula is utilizing, the formulas worked fine...
When I tried leaving the data alone (straight export from the main frame system) the formulas did not work...

The conclusion:
***Need a way to clean/reformat the data in those critical columns so the function can work appropriately.***

.... Columns E (QTY/UPA) & P (PN#) on the BOM Worksheet (Parts)
.... Columns B (PN#) & G(Qty) on the TO sheet (Inventory)

I tried using "TRIM" (manually) to clear any ghost spacing - but that didn't seem to get the formula working..
I tried changing the Format Type to :General - but that didnt seem to work either --
The only thing that DID WORK was when I simply clicked in a cell and re-typed the Part Number manually and re-typed the Qty manually in both sheets.
Instantly, the formulas that had already been pasted into COL E using VBA began to work...

 

[pleeseemailme]

Did you try any of the debugging text functions or formulas I mentioned in my last post? The only thing I can think of is that there are some hidden characters in the the output data. Using the above formulas might help you find the error. Trying to do it all with vba you could try this:

Sub Mod_12_BOM2TO()'Sub IncrementalMatchSumTotQtyBOM2TO()
    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    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 String
Dim x As Double
x = Sheets("TO").Range("B" & Rows.Count).End(xlUp).Row
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Sheets("TO").Range("B8:B1000")
cell.Value = WorksheetFunction.Trim(WorksheetFunction.Clean(cell.Value))
Next cell


For i = 2 To WorksheetFunction.CountA(Sheets("BOM Worksheet").Range("P:P")) + 3
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & x + 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

 

[ChrisOK]

UPDATE:
I just finished testing the TRIM, CLEAN and LEN with suprising results:

I trimmed the PN and QTY columns of the BOM sheet
AND trimmed PN and QTY columns of the TO sheet
Performed: Paste+Special Values the result, then
Ran the code = failed

I cleaned the PN and QTY columns of the BOM sheet
AND cleaned PN and QTY columns of the TO sheet
Performed: Paste+Special Values the result, then
Ran the code = failed

Performed LEN on
PN of BOM to PN of BOM (# of characters matches)
QTY of BOM to QTY of TO (# of characters DID NOT MATCH!!)
The TO's QTY is one character count higher all the way down...

Here's what I don't understand,
Why didn't the CLEAN or TRIM fix the issues???

Now that we know there is a mismatch (there's a ghost character in there that's keeping it from being a match),
AND we know that TRIM and CLEAN are not valid for eradicating this ghost character, what WILL force it to be an exact match?

Last, I tried the last batch of vba code you posted, but same results of the UPA(QTY) column getting zeroed out.

Any ideas how to correct the ghost character issue?