Match all values in one column and return consolidated results from matched rows

[BKGLTS]

Hello,

I have a spreadsheet with multiple sheets.
Sheet1 Column A has alpha numeric values in it like M1, M2, L1, various words, various numbers, etc. along with empty cells between them.
There may be duplicates values as well which is normal for the data and I need those duplicates accounted for (not just the first one for example).
I need a formula that I can put on Sheet2 that will generate a consolidated list of what is on Sheet1 based on certain filter criteria.
Already tried pivot tables and that didn't work.
I need the formula to be automatic in that whenever data is changed on Sheet1 it is automatically reflected on Sheet2.

For Example:

SHEET1
[TABLE="class: cms_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"]Dog[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

SHEET2
I need the formula on Sheet2 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="class: cms_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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1114322-find-all-matches-consolidate-them.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).