Consolidate specific data, based on criteria, from multiple sheets into single sheet

[ABennett757]

I read through other posts regarding consolidating data from multiple sheets into a single "master sheet" however, I only want to consolidate data that meets a specific criteria and didn't see this question posted previously so started a new thread. Essentially I have multiple sheets within a workbook and each sheet has a parts list which is in a data table (the data tables are formatted the same for all parts lists). Each data table has a column called "Order Status" which will always be in column W of the parts list worksheets and will also always have the column header name of "Order Status". What I'm trying to do is to generate a consolidated list of ONLY the parts where the order status equals "ready" to effectively create a list of parts that need to be ordered. The data that I want to consolidate based on the criteria in column W is in columns J:U and Y. I could move the data in column Y to be within a continuous range if that is helpful (so data range would be J:V in that scenario).

One little wrinkle is that the first parts list worksheet is the 6th sheet in the workbook (the previous sheets are a different type). So I need the ability to start compiling the data from the 6th sheet to the last sheet. Ideally I would have a cell reference to specify that the first parts list is sheet "6". This way if another sheet were unexpectedly added at the front, the user could change the 6 to a 7 in a specific cell within the consolidated parts to order list.

And finally, a nice additional feature would be to have a button on the consolidated parts to order list that would allow the user to change the order status of all the items that were just consolidated from "ready" to "ordered". I would want a confirmation pop-up box where the user would have to click "Yes" before this change actually takes place to avoid accidentally changing this data.

Thanks in advance for any help here. This will be a real time saver for my team if I can get this working!

 

[GraH]

With Power Query (get & Transform) you can consolidate all tables from all worksheets from a workbook.
Inside it you can filter out for example sheetnames, tablenames you do not need to consolidate. Same for the status. Columns you do not need in the consolidation can be removed as well.
Tuns of examples can be found via google.

The nice additional feature sounds like a macro/VBA code is required. Though I can imagine an alternative inside the consolidated table as well. Where you can allow to change the status, color it differently via conditional formatting, and with custom formatting invite the user to confirm the change in another column. Even when you do a refresh of the query to consolidate again, the rows changes in the consolidation can be considered correctly and would re-appear in the result with the corrected status. Hence you can always track those lines that have an inconsistent status in the raw data and the consolidated table. This trick requires a bit of tweaking, but it is do-able.

 

[vcoolio]

Hello ABennett,

If you placed all the parts worksheets into an array, you wouldn't need to worry about any additional worksheets that might be added which are different to the parts sheets. The VBA code would only ever deal with the array. For example:-

Option Explicit
Sub TestConsolidate()

Dim ws As Worksheet, ar As Variant, i As Long
ar = Array("Parts A", "Parts B", "Parts C", "Parts D")


Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).Clear

                For i = 0 To UBound(ar)
                Set ws = Sheets(ar(i))
                       With ws.[A1].CurrentRegion
                                .AutoFilter 23, "Ready"
                                Union(.Columns("J:U"), .Columns("Y")).Offset(1).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                                .AutoFilter
                       End With
                Next i

Application.ScreenUpdating = True

End Sub

As for changing the status to "Ordered", I'm assuming that you want this to happen in all the "Parts" source worksheets. A similar code to the above could do this (with a warning message box included):-

Sub StatusChange()

Dim ws As Worksheet, ar As Variant, i As Long
ar = Array("Parts A", "Parts B", "Parts C", "Parts D")

Application.ScreenUpdating = False

If MsgBox("Are you sure that the status is ready to be changed to 'Ordered'?", vbCritical + vbYesNo, "WARNING") = vbYes Then

                For i = 0 To UBound(ar)
                Set ws = Sheets(ar(i))
                       With ws.[A1].CurrentRegion
                                .AutoFilter 23, "Ready"
                                .Columns("W").Offset(1).Resize(.Rows.Count - 1) = "Ordered"
                                .AutoFilter
                       End With
                Next i
Else: Exit Sub
End If

Application.ScreenUpdating = True

End Sub

Below is a link to a sample workbook with these two codes implemented just to show you how it could work:-
ABennett's Sample

Click on the "Consolidate" button to transfer the relevant data to the "Consolidate" sheet. Change the amount of times that "Ready" appears in the status column of each part sheet then click on the "Consolidate" button again to see how the "Consolidate" sheet refreshes.
Click on the "Change Status" button to see the status change to "Ordered" in each "Part" sheet for the data that was just consolidated.

I hope that this helps or at least puts you in the right direction.

Cheerio,
vcoolio.

 

[Donury236]

Hi,

Didn't want to start a new thread as a couple hours scouring the net and I think that this approach is what I need. I am just a bit muddled on altering the code.
I have a sheet for all my LOLER equipment to track servicing.
I have 21 sheets (Alphabetical, no Q, and the last 6 are consolidated) that contain the client and equipment info and they are alphabetical.
There is already a formula in place to mark a certain column in the table based on a date range reference to the "next service date". So it returns Due, Serviced, or UNKNOWN is we have not yet been given info.

I want to have a sheet that will tell me what is "DUE" to be serviced over the sheets that hold the records. So for it to search all 21 sheets and then list all the ones due.

Is the code given by vcoolio simple enough to to alter for this?