VLOOKUP Across Multiple Sheets - Same array

[Pippy79]

Hi,

I have multiple order sheets where i want to collate the totals in one summary sheet so i know the total orders per item.

is there a multiple vlookup function i can do. I want to summarise column D with the items in Column A.

	A	B	C	D
1​	Product	Recommended Retail Price	Wholesale price (excl VAT)	Quantity
2​	123​			10
3​	456​			20

Thanks

 

[KRice]

How many worksheets would be involved and is there anything common with the sheet names...something that could be used as a selection criterion? Or are all of the worksheets in the workbook to be considered? I ask because it might be easier to use Power Query to load in the relevant worksheets and build a consolidated list. That could be done in just a few steps. In this recent post...

Filter

Hi Team, I have 100+ questions for each Excel sheet and I need to filter questions that start with "(OP19) CALL TO ACTION" only among 100 Questions. Can anyone help me with this Regards, Sanjeev

www.mrexcel.com

...I describe an approach for transforming content that exists across multiple sheets in a source workbook. In your case, you might need to filter the worksheets to be processed so that only the relevant ones are considered.

In a source workbook, I mocked up three worksheets (Pippy1, Pippy2, and Pippy3) with some data and then used Power Query to consider only worksheets beginning with "Pippy" to produce this output:

Book2
	A	B	C
1	Sheet	Product	Quantity
2	Pippy1	123	10
3	Pippy1	456	20
4	Pippy2	279	49
5	Pippy2	283	37
6	Pippy3	458	13
7	Pippy3	467	17
Pippy1

The code used:

let
    Source = Excel.Workbook(File.Contents("C:\Users\...your file path and name...\MrExcel_20220912.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Pippy")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column4"}, {"Column1", "Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pippy1", type text}, {"Product", type any}, {"Quantity", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Pippy1", "Sheet"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Product] <> "Product"))
in
    #"Filtered Rows1"

 

[Pippy79]

Hi Kirk,

Thanks for your response, the sheets do have common wording such as "Order Form 2022" then they are followed by the purchaser. In terms of amount of sheets theres around 10-15.

The power query above looks quite big and "scary". is there anything less intimidating?

Thanks

 

[KRice]

It's not too bad. The file available at the link below already contains the M Code, although you will need to edit one line...the "Source" line needs to be updated to point to your workbook. You can follow the instructions in the post I linked to earlier, or you can take some shortcuts.

MrExcel_20220912_Pippy79.xlsx

Shared with Dropbox

www.dropbox.com

Download the file and place it in the same folder with your Order Forms workbook. While in that folder (probably in Windows File Explorer), right click on the Order Forms workbook and choose "Copy as path". That copies the full path name and file name of your Order Forms workbook to your clipboard.

Then open the file I provided and click on the results table. Then in the top level menu, click Query > Edit. Power Query should open. On the right, you will see a navigation window showing the various steps in the query. Click on the first step (named "Source") and look in the formula bar. You should see:
= Excel.Workbook(File.Contents("C:\Users\...path name and file name"), null, true)
Select everything between the quotation marks and paste your path and file name there. Then click the check mark (to the left of the formula bar) to confirm the change. That's all!

You can click on each of the steps in the navigation pane to watch the transformation of your data. If you want to change the column headings, double click on the heading and type a new heading. If you want to sort the columns differently, or group them somehow that could be done in Power Query too. After confirming that the last step produces the desired output, click on Home > Close and Load and the query table will be loaded back into the Excel workbook.

Any subsequent changes to the Order Forms sheets will be reflected in the summary table by clicking in the summary table and choosing Data > Refresh All > Refresh All.