Incrementally Update QTY with each MATCH while Looping Challenge?

C

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
601
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.....
===============================
 
Sort by date Sort by votes
I'm still taking shots in the dark here, but let me know if this does what you want.

Code: 
Sub TryAgain()Dim y As Integer
y = Range("[COLOR=#ff0000]D3[/COLOR]").Value
    Range("G2").Select
    ActiveCell.Formula = _
        "=SUMIFS(INVENTORY!$A2:A" & y & _
        ",INVENTORY!$B2:$B" & y & ",PARTS!B2)"
Range("[COLOR=#ff0000]D3[/COLOR]").Value = Range("[COLOR=#ff0000]D3[/COLOR]").Value + 1
    
End Sub

Please replace D3 with a named range referencing some cell in the workbook that won't be affected by the regular use of the sheet. Then, place the number '2' in the cell.

This is just to test to see if this is the direction you want to go. Thanks.
 
Last edited:
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sorry, meant to remove the Range("G2").Select line. Please remove it and then run the macro with the cell you want to test selected. I think that would be column C in 'parts'
 
Upvote 0
lol - God Bless Your Soul for still workin' at it ---
I'm pulling hair out on this end too -- trust me -- SO CLOSE!

Here's another stab at trying to paint a visual for you:
Here's the formula that needs to be pasted into C2 of the "PARTS" sheet and copied down until there's no more adjacent data.
=SUMIF(INVENTORY!$C$2:$C$5,PARTS!B2,INVENTORY!$A$2:$A$5)

But the middle part of my formula keeps converting itself to this:
=SUMIF(INVENTORY!$C$2:$C$5,PARTS!XFD2,INVENTORY!$A$2:$A$5)
If you know how to fix that proplem within the formula - then we are golden...


(which messes up the result)


Here's the BEFORE view:
===============================
...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.....
===============================
===============================

Here's the AFTER view:
(what the sheet should look like after the macro runs)
===============================
...SHEET: "PARTS"
1..DESC......PART#.....QTY....
-----------------------------
2..bolt......12345......25......(5+5+15) found 3 matches & summed the 3 QTYs found in Col A of INVENTORY
3..clamp.....78787......0.......(found no matches)
4..screw.....12345......25......(5+5+15) found 3 matches & summed the 3 QTYs found in Col A of INVENTORY

===============================
===============================

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

I'm looking at doing something like this -- if I can keep the formula from trashing on me!
Code: 
Sub PasteFormulaCopyDown()
'
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC[-3],INVENTORY!R2C1:R5C1)"
    Range("C2").Select
    
    
    Range("C2").FormulaR1C1 = "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC[-3],INVENTORY!R2C1:R5C1)"
    Range("c2:c2").AutoFill Destination:=Range("c2:c" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    Range("C2").AutoFill Destination:=Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillValues
     
    
End Sub
 
Upvote 0
Ok. Would =SUMIF(INVENTORY!$C$2:$C$5,PARTS!$B2,INVENTORY!$A$2:$A$5) Work? This should keep the B2 from turning into XFD2. If we got this then the Macro for the Bold will be easier.
 
Upvote 0
what would this change to --- within the code:
"=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC[-3],INVENTORY!R2C1:R5C1)"
 
Upvote 0
Ok, this works!
Not sure if it is the most efficient way - but it works.... now, to have it BOLD Part#s that found matches between the two sheets:

Code: 
Sub IncrementalMatchSumTotQty()
'
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC2,INVENTORY!R2C1:R5C1)"
    Range("C2").Select
    
    
    Range("C2").FormulaR1C1 = "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC2,INVENTORY!R2C1:R5C1)"
    Range("c2:c2").AutoFill Destination:=Range("c2:c" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    Range("C2").AutoFill Destination:=Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillValues
    
    
    
End Sub
 
Upvote 0
Cleaned up version (below):
Always want to share what I've learned so others looking to learn / who need help will also benefit:
Only thing left to add to this is the capability to BOLD Part # matches found on both sheets to show the analyst that all bolded items have been validated as having a match.
Anything left unbolded show the analyst that these did not require updates to their parts forecast:
Will update this post when that challenge is tackled....

Code: 
Sub IncrementalMatchSumTotQty()
'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"
'TOTALS ARE ROLLED UP ON THE PARTS SHEET WHILE USING THE INVENTORY SHEET AS JUST A LOOKUP TABLE FOR COLLECTING QTYS TO SUM.


    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)"
    
    'THE BELOW CODE ALSO WORKS INSTEAD OF THE SINGLE ROW ABOVE, BUT THE ONE ABOVE WORKS FASTER AND KEPT THE FORMULA FROM CORRUPTING!!
    'KEEPING BOTH AS OPTIONS AND AS "FYI" FOR FUTURE REFC
    'Range("C2").FormulaR1C1 = "=SUMIF(INVENTORY!R2C3:R5C3,PARTS!RC2,INVENTORY!R2C1:R5C1)"
    'Range("C2:C2").AutoFill Destination:=Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    'Range("C2").AutoFill Destination:=Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillValues
    
    
End Sub
 
Upvote 0
ChrisOK,

This code will make matching values bold in the Inventory sheet.
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)
        If cell.Value = Nmbr Then
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

What it will do is start with B2 in parts and check all values in C:C in inventory for a match. Then it will loop to B3 and do it again.

What it won't is make any numbers bold in 'parts'. The reason for this is: If I follow the same logic above in 'parts' then every part number will turn bold. Perhaps I am missing something.
 
Last edited:
Upvote 0
nope, it did (some highlighting) but did not do all the necessary highlighting..
It only highlighted Part# 12345 found in C2 and C4 but did not highlight the last one found in C5 (all of these are on the "Inventory" sheet).
The bold functionality should bold the corresponding Part# on the "Parts" sheet.

Think of it as a process of eliminataion starting with the PARTS list... Starting with C2 on that Parts list which is: Part# 12345.
If I find Part# 12345 on the INVENTORY sheet, I'm going to cross it off this PARTS list (and) cross it off the INVENTORY list showing that all matches have been found.
In this case, instead of crossing them off, we are making them bold.
 
Upvote 0
Hmmm...its working in my tests. 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)
    Sheets("Parts").Range("B" & i).Font.Bold = True
    For Each cell In Sheets("Inventory").Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Merge QTY & AMOUNT for each part of item for each range
Replies
2
Views
62
Amer Omar
A
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
461
skillilea
S
Y
  • Question Question
sum total in specific column when it complete 1 quantity in column H
Replies
0
Views
410
yoon
Y
K
  • Question Question
Array SUM or SUMPRODUCT function error
Replies
1
Views
498
KratosZG
K
R
  • Question Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
472
Domenic
D

Forum statistics

Threads
1,223,447
Messages
6,172,203
Members
452,448
Latest member
Tupacandres

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top