Finding like data in multiple worksheets

[jaywebb846]

Hi I have a workbook we use for prep and production at the Lobos Stadium here in New Mexico and the workbook has about 12 worksheets that are associated with the stands we have that call production(requisition product). There are like products on several of the worksheets and I am using a standard naming convention on all products to make it easier for master production sheet. What I need to do is find all like products in all stands and then sum those quantities and copy them onto a master sheet. For example there may be BBQ pulled pork in 8 of the 12 stands and they all want 5 pans of pork, the unit of measure is the same for all stands on this product. So I need to have a macro that on button click will search all sheets for pork and then sum that quantity and copy pork 5 pans onto the master sheet. Then repeat looking for all other like items and do the same thing and continue this process on down the line on all sheets until a master production sheet is achieved. I assume I will need some sort of array to store the data for the sum procedure but I am very new at excel. Currently I just have a formula in each cell on my master that just sums the same cells and is mapped to the master sheet and that is fine until something changes in one or more sheets then I have to go back through and change all my sheets and formulas to correct the issue after adding or removing a production item.. I hope this is clear enough of an explanation and I hope someone can help me with this as we are starting a new season with all new menus which means all new production and several days of fixing formulas to get mapping correct again..

Thanks

Jay

 

[My Aswer Is This]

jay we need specific details.
You said:

So I need to have a macro that on button click will search all sheets for pork and then sum that quantity and copy pork 5 pans onto the master sheet.​

I have these questions:

1.What is the name of the sheet where you have all these items we need to search for and in what column are the search values.

2. Where on the other sheets will we find these values
3. Where is the quanity we will need to look for
4. Where do we return the results of our search?

We always need sheet names and column numbers.
Do not say column Name and column quanity.
Please say search value will be in column A and quanity will be column B
Please provide these details and I may be able to help you with a Vba script .

 

[jaywebb846]

Hi thanks for your reply.. there are 12 sheets the names will be changing just not sure when. There are 5 columns on each sheet we will be searching.. the column with item name is column A the location is column B, Unit of measure is column C, order quantity is column D. Location is used as an additional qualifier there are two values on it warehouse one is Kit chen. I only need information where this value equals "Kit chen". The destination Sheet is called "Master Production".. the information that needs to be recorded is item name (Column A), Unit of measure(Column B), and quantity ordered(Column C).. . I hope this 8s what you need.. the number and name of sheets may change in the future as of now there are 13 total including the Master Production Sheet.

Thank you

Jay

PS no space in Kit chen

 

[jaywebb846]

Hi also the first two rows on all sheets contain headers not data.. all sheets are in the same workbook. All data begins on row 3. I apologize the issue with "k i t c h e n" threw me off a bit..so just to clarify the location cell has a possible two values either warehouse or "k i t c h e n " we only need data if the value equals "k i t c h e n " had to space out the word " k i t c h e n" to keep it from turning into asterisks..

 

[Peter_SSs]

I apologize the issue with "k i t c h e n" threw me off a bit..

You don't need to apologise. It is a forum setting relating to a lot of spam we were getting. So, in my text & code below you will need to replace the asterisks with "that word" (in lower case only in the code).

Make sure that you test in a copy of your workbook. Since you didn't really specify what to do, my code removes everything from the 'Master Production' worksheet and enters a total "*******" quantity of all products found in the workbook.

Sub Get_Quantities()
  Dim d As Object
  Dim ws As Worksheet
  Dim r As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each ws In Worksheets
    If ws.Name <> "Master Production" Then
      With ws
        For r = 3 To .Range("A" & .Rows.Count).End(xlUp).Row
          If LCase(.Cells(r, 2).Value) = "*******" Then
            d(.Cells(r, 1).Value) = d(.Cells(r, 1).Value) + .Cells(r, 4).Value
          End If
        Next r
      End With
    End If
  Next ws
  With Sheets("Master Production")
    .UsedRange.ClearContents
    With .Range("A1:B1")
      .Value = Array("Item", "Quantity")
      .Offset(1).Resize(d.Count).Value = Application.Transpose(Array(d.Keys, d.Items))
      .EntireColumn.AutoFit
    End With
  End With
End Sub

 

[jaywebb846]

Hi

Thank you for your help.. I am getting a type mismatch error when I run the code at point in red:

With .Range("A1:B1")
.Value = Array("Item", "Quantity")
.Offset(1).Resize(d.Count).Value = Application.Transpose(Array(d.Keys, d.Items))
.EntireColumn.AutoFit

What I am trying to accomplish is to basically get all data from the Columns "Item" , "Location", "Unit of Measure" and "Quantity" from the worksheets and place it on the master production sheet and at the same time I also need to sum anything that is like data before it is copied to the Master Production sheet so it is not duplicated on the Master Production sheet. Pork may be in ten stands and Popcorn in only one stand on the Master Production Sheet, so I need a total of all Pork with the "Unit of Measure" and a total of all Popcorn with the "Unit of Measure". The master Production sheet is set up from a visual stand point just like the other sheets. Column 1 (Item), Column 2(Location), Column 3(Unit of Measure), Column 4 ( Quantity) Column 5(Unit Cost) Column 6 (Total Cost). SO I need to find all Items and drop them on the Master Production Sheet in this order and have the Get Data Sub actually perform a sum calculation before it copies data in Column 4( Quantity) Cell on the Master Production Sheet. Row One on all sheets is the sheet Name and row 2 are Column Names. I hope this better explains my situation.

Item

Location

Unit of Measure

Quantity

Unit Cost

Total Cost

<tbody>
</tbody>

Thank you so much for your help and our University of New Mexico Lobos appreciate it.."Go Lobos!"

 

[jaywebb846]

Hey it does work it just drops data in the wrong columns and it overwrites my Master Production sheet name and column headers. THe error i was getting is because i did not use lower case on "That word" Can we get it to bring the Unit of Measure to the Master Production SHeet and place them Item, Unit of Measure and Quantity in the correct columns?

THanks

 

[Peter_SSs]

.. it overwrites my Master Production sheet name and column headers.

That part isn't surprising to me since I said that would happen.

Since you didn't really specify what to do, my code removes everything from the 'Master Production' worksheet ..

OK, I'm assuming the following 3 things. If any of these assumptions are incorrect then you will need to clarify what actually is the case and how to deal with that situation.

- All sheets, including 'Master Production', have the headings shown in post 6 & in that order
- 'Master Production' can and should have any existing data, except rows 1 & 2, removed
- A particular item will have the same unit of measure and same unit cost on all the preliminary sheets

Give this version a try. Don't forget to replace "******* in the code with the lower case word in question.

Sub Get_Quantities_v2()
  Dim d As Object
  Dim ws As Worksheet
  Dim r As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each ws In Worksheets
    If ws.Name <> "Master Production" Then
      With ws
        For r = 3 To .Range("A" & .Rows.Count).End(xlUp).Row
          If LCase(.Cells(r, 2).Value) = "*******" Then
            s = Join(Application.Index(.Cells(r, 1).Resize(, 3).Value, 1, 0), ",") & ",," & .Cells(r, 5).Value
            d(s) = d(s) + .Cells(r, 4).Value
          End If
        Next r
      End With
    End If
  Next ws
  With Sheets("Master Production")
    .UsedRange.Offset(2).ClearContents
    With .Range("A3:F3").Resize(d.Count)
      With .Columns(1)
        .Value = Application.Transpose(Array(d.Keys))
        .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
      End With
      .Columns(4).Value = Application.Transpose(Array(d.Items))
      .Columns(6).FormulaR1C1 = "=RC[-2]*RC[-1]"
      .EntireColumn.AutoFit
    End With
  End With
End Sub

 

[jaywebb846]

Hi

Thank you so much for your help.. Yes all sheets have the same column headers, including the master production sheet.
Master Production will be initially blank and have no data other than Name and column headers in rows 1 and 2
Unit of measures will be uniform across the board for Like items, Pork will always have the same unit of measure when it is found on any particular sheet.
Candy will have the same unit of measure but its unit of measure will or can be different than pork. The initial procedure actually worked well i just needed more data brought across. The Total columns data does not need to be populated as i have a formula on the Master Production sheet to handle the calculations once the quantity data is populated. As of right now I am getting an odd outcome on the first row of data It is placing Pork on the Sheet twice. Once with only a single amount and the second time it is summed. It appears to be handling anything that has a unit cost differently than items that do not have a unit cost associated with them. Again there does not need to be a calculation on the unit cost and the total quantity. I just need the data brought from that cell. The original procedure actually did very well on giving me an accurate count and consolidating all like data. Just needed it to not delete my column headers and bring over all the data except the Total Cost Column. The below table is what i am getting.. What I should be getting is Pork 40, Limes 32 and Candy 1. I think the calculation is throwing it off. I am so sorry I am not getting you the info you need to help me. It did have everything summed correctlly on the original procedure the only issue was the data it was deleting and not bringing all the data i needed. I do not need total cost columns data at all I have a formula to handle that on the Master..

I hope i am getting you what you need to help..

Item	Location	Unit of Measure	Quantity	Unit Cost	Total Cost
Pork	*******		5	1.00	5
Limes	*******		4	1.00	4
Pork	*******		35
Limes	*******		28
Candy	*******		1

<tbody>
</tbody>

That part isn't surprising to me since I said that would happen.


OK, I'm assuming the following 3 things. If any of these assumptions are incorrect then you will need to clarify what actually is the case and how to deal with that situation.

- All sheets, including 'Master Production', have the headings shown in post 6 & in that order
- 'Master Production' can and should have any existing data, except rows 1 & 2, removed
- A particular item will have the same unit of measure and same unit cost on all the preliminary sheets

Give this version a try. Don't forget to replace "******* in the code with the lower case word in question.

Sub Get_Quantities_v2()
  Dim d As Object
  Dim ws As Worksheet
  Dim r As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For Each ws In Worksheets
    If ws.Name <> "Master Production" Then
      With ws
        For r = 3 To .Range("A" & .Rows.Count).End(xlUp).Row
          If LCase(.Cells(r, 2).Value) = "*******" Then
            s = Join(Application.Index(.Cells(r, 1).Resize(, 3).Value, 1, 0), ",") & ",," & .Cells(r, 5).Value
            d(s) = d(s) + .Cells(r, 4).Value
          End If
        Next r
      End With
    End If
  Next ws
  With Sheets("Master Production")
    .UsedRange.Offset(2).ClearContents
    With .Range("A3:F3").Resize(d.Count)
      With .Columns(1)
        .Value = Application.Transpose(Array(d.Keys))
        .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
      End With
      .Columns(4).Value = Application.Transpose(Array(d.Items))
      .Columns(6).FormulaR1C1 = "=RC[-2]*RC[-1]"
      .EntireColumn.AutoFit
    End With
  End With
End Sub

****** id="cke_pastebin" style="position: absolute; top: 201px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Total Cost

<tbody>
</tbody>

 

[Peter_SSs]

So, trying to simplify all that, is each one of these correct? Please clarify if any one is incorrect.

1. You only want the code to bring across 'Item', 'Location', 'Unit of Measure' and (Total) 'Quantity'?
2. Those values should go into columns A:D of 'Master Production', starting at row 3?
3. The code doesn't need to clear out any old data from 'Master Production' in columns A:D from row 3 down?