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]

Howdy,
Here is some code that will loop as you request, however I don't fully understand fully your explanation.

Sub CountParts()
Dim cell As Range
Dim j, k As Integer
j = 0
k = 0
Application.Calculation = xlCalculationManual
For i = 2 To 256
    For Each cell In Sheets("Parts").Range(i & ":" & i)
        If cell.Value = 12345 Then
        cell.Font.Bold = True
        j = j + 1
        End If
    Next cell
    Sheets("Parts").Range("A" & i).Value = j


    For Each cell In Sheets("Inventory").Range(i & ":" & i)
        If cell.Value = 12345 Then
        cell.Font.Bold = True
        k = k + 1
        End If
    Next cell
    Sheets("Inventory").Range("A" & i).Value = k
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

This code will take a while to run because it loops through every cell in each row. If you know your longest row is ends at column XX for example this could be cleaned up to run faster.

Assumptions I made:
256 = number of rows of data
That you want your output values in column A on each sheet

j will be the total of the row and previous rows in sheet 'parts'
k will be the total of the row and previous rows in sheet 'inventory'
These will output in column A

 

[ChrisOK]

Hello, thanks for trying.. I tested the code in a test file and it's not working as needed.
If you paste the "PARTS" data from above into Sheet1
and paste the "INVENTORY data from above into Sheet 2

Sheet 1 (Parts) is looking to Sheet 2 (Inventory) for a match.
Specifically, Sheet 1: the first row is a bolt, Part# 12345 w/ a Qty of 1
It looks to Sheet 2 and finds a match (using the Part#)! (at this point, both sheets should turn that part # to bold font)
Now, more importantly, the QTY must get updated on Sheet 1 only with what it found on Sheet 2.

It notices that Sheet 2's QTY is "5" so it overwrites Sheet 1's QTY of "1" and turns it to "5".

The loop continues to look to Sheet 2 for another match and finds Part # 12345 again! This time, the QTY is "5" again!
So, Sheet 1 must be updated again (original 5 + new 5 found =10). It incrementally updates that 5 by adding the new QTY to it to turn it to "10".

The loop continues to look to Sheet 2 for any more matches and finds Part #12345 a 3rd time, this time the QTY is "15".
So, Sheet 1 must be updated once again from the new QTY of "10" to (10+15=25). The new QTY on Sheet 1 for Part # 12345 is "25".

Note: Sheet 2 never incurs any SUM changes....
.........Sheet 1 is the sheet that is having its QTY updated every time it finds a match...

Every time a match is found, it should turn the Part# to bold font on both sheets to show the analyst that this item has a match on the other sheet.
But more importantly, the QTY of Sheet 1 should increase as it finds additional match quantities to add to the original Qty.

Hopefully that makes sense =-)

 

[pleeseemailme]

ChirsOK,
I think I understand what you are looking for. Here is a code that will loop through, make matching values bold and count the values to input them into column A of 'Parts'. However, what values are matching? I wasn't sure so I placed an input box at the beginning of the loop to give a value to match to. So, the downside is this will only match one part at a time, which might or might not be what you want. I've adjusted the range to only look through columns C to CC. Adjust as needed for your width.

Sub CountParts()Dim cell As Range
Dim k As Integer
Dim Nmbr As Double
Application.Calculation = xlCalculationManual
Nmbr = InputBox("Please enter part number", "Part #")
For i = 2 To WorksheetFunction.Count(Sheets("Parts").Range("B:B")) + 1
        For Each cell In Sheets("Inventory").Range("C" & i & ":CC" & i)
            If cell.Value = Nmbr Then
            cell.Font.Bold = True
            k = k + 1
            End If
        Next cell
    If Sheets("Parts").Range("A" & i - 1).Value = k Then
    Else
    Sheets("Parts").Range("A" & i).Value = k
    End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

Alternatively, this formula when pasted into A2 in 'Parts' will incrementally add each adjacent part number. Enter into A2 and press ctrl+shift+enter, then fill down.

=COUNTIFS(INVENTORY!$C2:$M2,PARTS!$B2:$B2)+SUM(IF($B1:B$2=B1,$A1:A$2,0))

This won't make bold the matching values, however.

I hope one of these are on the right track.

 

[ChrisOK]

PART# of one sheet looking for PART# match on the other sheet.
If a match is found, the bolding both PART#'S[/] occurs on both sheets
and the QTY of Sheet 2 is updated incrementally if/when multiple finds/matches are found.

There may be hundreds of line items to compare to --- therefore, inputting a value one by one would not be good... The code needs to know that it is stricting performing Part # to Part# matches.

So, using the above sample, the Part# is COLUMN B in Sheet 1 ("Parts" sheet)
and the Sheet 2 ("Inventory" sheet) is using COLUMN C to match Part# to Part#.

 

[pleeseemailme]

Are we looking only in the same rows of the sheets? ie, part 12345 in row 2 of 'parts' only look for matches in row 2 of 'inventory'?

If that is the case, aside from the bolding issue, does the formula provided work? You might need to adjust the cell references to work, something like:

=COUNTIFS(INVENTORY!$D2:$DD2,PARTS!$B2:$B2)+SUM(IF($B1:B$2=B1,$A1:A$2,0))

Where column A is the sums amounts in the 'parts' sheet.

If this is the right track we can work on a macro for the bolding only.

 

[ChrisOK]

Please refer to the original example:
The "PARTS" sheet starts with first row which happens to be PART# 12345
It looks to the "INVENTORY" sheet for a match..
Coinsidentally,it finds its FIRST match on row 2 of the "INVENTORY" sheet and extracts the QTY of 5 from the "INVENTORY" sheet and pastes it into the "PARTS" sheet.
It turns both of those BOLD.

Next, the LOOP continues to bounce against the remaining rows left in the "INVENTORY" sheet...
Row 3 is not a match, so it moves to Row 4...
Row 4 IS a match and it holds a QTY of 5...
The code needs to update the "PARTS SHEET" QTY to tell the PARTS SHEET that it found 5 more ON THE INVENTORY SHEET to add to it's original finding of 5.
5+5=10 so, the PARTS SHEET will now be incrementally updated to SUM what it currently shows with the new finding on row 4. New QTY should reflect "10" on the PARTS SHEET.
This new find also turns BOLD to reflect that it was found and appropriately accounted for...

Next, the LOOP continues to search for any more findings on the INVENTORY SHEET...
It comes to Row 5 and finds yet ANOTHER MATCH! (this QTY is 15) so, it must add that QTY of 15 to whatever the PARTS SHEET TOT currently is "10"...
New updated total on the PARTS SHEET for PART # 12345 is "25" (the original 10 + the newly found 15 ='s 25)

The LOOP continues down the rows of the INVENTORY SHEET looking for any more matches until there are no more rows of data.

THEN, it starts fresh with the NEXT available Part# row of the PARTS sheet.
This time, it is looking for Part # 78787.
The code goes to the INVENTORY SHEET, to try to find a match.
If a match is found, the qty is updated on the 1st pass with an overwrite.
If additional matches are found, the qty is SUMed together to incrementally total up all findings.

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

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

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

 

[pleeseemailme]

I can only assume that since you have not mentioned the formula in either of my requests that it must be working for you. With that being the case, the entire issue can be resolved without VBA.

Use the formula above in column A of the 'parts' sheet. Then use a custom formula in the conditional formatting rules such that each value in each row of each sheet that matches the part number (='Parts'!$B2) it turns the text Bold.

Simple solution without VBA

 

[ChrisOK]

No, the formula does not work - nor does the vba.
The vba is copying data into the wrong columns, overwriting 'Desc' content with numbers and the quantities are not summing appropriately either.
The formula is coming up with wrong results (o)r produces a "#VALUE!".

The vba is required as it is attached to a toolbar icon that runs the code to perform the automated cross analysis between sheets and instantly updates of summed quantities.
The formula method can not work because it is a multi-part process that a single function pasted downward can not accommodate.

The code, on its first pass, its job is to overwrite with its first finding (simple enough-and yes, this could be achieved w/a single function), but the point is, that the 2nd step is to continue searching and begin calculating/summing a total following the first overwritten copy/pasted qty and continue doing so on subsequent passes until exhausted.
Then, begin a new loop looking for the next part# on the "Parts" list until everything from the "Inventory" list has been rolled into the Parts list appropriately.

The analysts are working with hundreds of files and hundreds of line items. Time is of the essense, so single click icons with code attached to them to load/run code & functions is the preferred method to maintain speed.

That said: Here's an alternative using a combination of a formula and vba:
(we could throw out the inital update step that our normal manual procedure requires and simply have a look up function go to the "INVENTORY" sheet, SUM up every QTY that has a MATCH and place that total QTY SUM into Col C of the "Parts" sheet.)

This would achieve the same end result of needing total qty summed based upon findings of the "Inventory" sheet.
Part# Finds/Matches would need to be bold on both sheets.
If we went this alternative route, the vba would need to insert the formula into the appropriate column "C" of the PARTS sheet and copy it down as far as there was data.

 

[pleeseemailme]

I'm afraid I'm still as lost as a tourist visiting Vegas. If VBA can insert a formula into column C and copy it down, why can't you just put that formula there, copy it down and leave it there?
Is the list of parts in the 'parts' sheet full of duplicate values like the 'inventory' sheet is? I've assumed that it is. If not, why have VBA incrementally add the data one at a time? Once it loops through, the total value will be the same as if the formula was there, you won't have time to see the incremental values. Unless, perhaps, you're wanting the macro to run for one row only and then stop. Next, when you hit the button again it will look in the next row and stop. Again and again until you've hit the button as many times as there are rows. Is that it? And then you want to hit the button as many times as there are rows for the next part number? One part number at a time, on row at a time? Even after all these explanations, I'm not sure what you're after.