Find all matches and consolidate them

[BKGLTS]

Hello,

I have a spreadsheet with multiple sheets.
Sheet1 Column A has alpha numeric values in it like M1, M2, M3, etc. along with empty cells between them. There may be duplicates but that doesn't matter.
I need a formula that I can put on Sheet2 that will generate a consolidated list of what is on Sheet1 based on certain criteria.

For Example:

SHEET1
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]M1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]Twigs[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Berries[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Twigs[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

SHEET2
I need the formula to look in Column A on Sheet1 and find all the rows that have a value starting with the letter "M", and then return the values Sheet 1 Columns B & C, but consolidated as shown here.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Twigs[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Berries[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you,
B

 

[sandy666]

I added solution with PivotTables

 

[BKGLTS]

I do not get an option to download. Right click on link and no option to download only open or save link which is an html. Clicking on the link automatically tries to open in browser.

Can you place on mediafire.com?

 

[sandy666]

again, what is your Excel version?

try zip file
no right click, just click

 

[BKGLTS]

Got it that time. Excel Office 365 v1910

 

[BKGLTS]

The Power Query seems to work great even when updating and refreshing the data.
The pivot tables explode when trying to refresh them and only show the totals after refresh.
So question is how do I copy the power query or create these power queries in the actual spreadsheet please?
Thank you!

 

[sandy666]

Pivot Table:
just re-create PT, add filter CodC, DCscription to existing Qty and it will work

Power Query:
all steps you can see: Data tab, Show Queries, in Workbook Queries you will see four tables , right click on any table and Edit then you'll see steps
all these tables are copied from the first but differently filtered

 

[BKGLTS]

Sandy666, Thank you I was able to figure it out and got them created in my actual spreadsheet. This solution seems to work very well for what I need. I think I prefer it not to automatically update because i think it slows down the spreadsheet in general. I think i will be ok manually refreshing the tables.

 

[sandy666]

with small table(s) it can be automatic update
with large (few million rows) - better manual
you can do what you want of course