Find all matches and consolidate them

B

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
82
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
 
Sort by date Sort by votes
I am having trouble with the pivot table reporting the values in the data range correctly. Must the range be sorted to work properly.
My range cannot be sorted. I also tried creating a table and basing the pivot table from that table and same issue.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the file put the result of the pivot table and elsewhere put the result you need.
 
Upvote 0
with you example (post#1) via Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#5B9BD5]C[/td][td][/td][td=bgcolor:#70AD47]C[/td][td=bgcolor:#70AD47]SumB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]M1[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]Twigs[/td][td][/td][td=bgcolor:#E2EFDA]Twigs[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M1[/td][td]
1​
[/td][td]Berries[/td][td][/td][td]Berries[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]M2[/td][td]
3​
[/td][td]Twigs[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FirstChar = Table.AddColumn(Source, "First Characters", each Text.Start([A], 1), type text),
    Group = Table.Group(FirstChar, {"C", "First Characters"}, {{"SumB", each List.Sum([B]), type number}}),
    RC = Table.RemoveColumns(Group,{"First Characters"})
in
    RC[/SIZE]

or post more representative example
 
Upvote 0
with total count

is this acceptable for you?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Count[/td][td=bgcolor:#F79646]DCscription[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]M[/td][td=bgcolor:#FDE9D9]
4​
[/td][td=bgcolor:#FDE9D9]Aerial - 1/4" Preform Straight Splice[/td][/tr]

[tr=bgcolor:#FFFFFF][td]M[/td][td]
4​
[/td][td]Aerial - 1/4" Strand Vice [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
6​
[/td][td=bgcolor:#FDE9D9]Aerial Field Engineer[/td][/tr]

[tr=bgcolor:#FFFFFF][td]L[/td][td]
6​
[/td][td]Aerial Sr. Lineman[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
6​
[/td][td=bgcolor:#FDE9D9]HDD Superintendent[/td][/tr]

[tr=bgcolor:#FFFFFF][td]E[/td][td]
6​
[/td][td]F150/1500 Pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]E[/td][td=bgcolor:#FDE9D9]
6​
[/td][td=bgcolor:#FDE9D9]F250/2500 Pickup[/td][/tr]

[tr=bgcolor:#FFFFFF][td]E[/td][td]
6​
[/td][td]17 SUV Escape Ford SE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]S[/td][td=bgcolor:#FDE9D9]
4​
[/td][td=bgcolor:#FDE9D9]Trench Urban[/td][/tr]

[tr=bgcolor:#FFFFFF][td]S[/td][td]
4​
[/td][td]Trench Rural[/td][/tr]
[/table]

or you still want separated tables for each Qty?
 
Upvote 0
here is your example with Power Query

Example file

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty4[/td][td][/td][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Aerial - 1/4" Preform Straight Splice[/td][td=bgcolor:#FDE9D9]M[/td][td=bgcolor:#FDE9D9]
2​
[/td][td][/td][td=bgcolor:#FDE9D9]F150/1500 Pickup[/td][td=bgcolor:#FDE9D9]E[/td][td=bgcolor:#FDE9D9]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aerial - 1/4" Strand Vice [/td][td]M[/td][td]
2​
[/td][td][/td][td]F250/2500 Pickup[/td][td]E[/td][td]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Total[/td][td][/td][td]
4​
[/td][td][/td][td=bgcolor:#FDE9D9]17 SUV Escape Ford SE[/td][td=bgcolor:#FDE9D9]E[/td][td=bgcolor:#FDE9D9]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]Total[/td][td][/td][td]
6​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty4[/td][td][/td][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty2[/td][td=bgcolor:#F79646]Qty4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Trench Urban[/td][td=bgcolor:#FDE9D9]S[/td][td=bgcolor:#FDE9D9]
2​
[/td][td][/td][td=bgcolor:#FDE9D9]Aerial Field Engineer[/td][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
2​
[/td][td=bgcolor:#FDE9D9]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Trench Rural[/td][td]S[/td][td]
2​
[/td][td][/td][td]Aerial Sr. Lineman[/td][td]L[/td][td]
2​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Total[/td][td][/td][td]
4​
[/td][td][/td][td=bgcolor:#FDE9D9]HDD Superintendent[/td][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
2​
[/td][td=bgcolor:#FDE9D9]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]Total[/td][td][/td][td][/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
wrong tables above, example file is correct and tables should be like here

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty4[/td][td][/td][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Aerial - 1/4" Preform Straight Splice[/td][td=bgcolor:#FDE9D9]M[/td][td=bgcolor:#FDE9D9]
4​
[/td][td][/td][td=bgcolor:#FDE9D9]F150/1500 Pickup[/td][td=bgcolor:#FDE9D9]E[/td][td=bgcolor:#FDE9D9]
3​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Aerial - 1/4" Strand Vice [/td][td]M[/td][td]
4​
[/td][td][/td][td]F250/2500 Pickup[/td][td]E[/td][td]
3​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Total[/td][td][/td][td]
8​
[/td][td][/td][td=bgcolor:#FDE9D9]17 SUV Escape Ford SE[/td][td=bgcolor:#FDE9D9]E[/td][td=bgcolor:#FDE9D9]
3​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]Total[/td][td][/td][td]
9​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty4[/td][td][/td][td=bgcolor:#F79646]DCscription[/td][td=bgcolor:#F79646]First Characters[/td][td=bgcolor:#F79646]Qty2[/td][td=bgcolor:#F79646]Qty4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Trench Urban[/td][td=bgcolor:#FDE9D9]S[/td][td=bgcolor:#FDE9D9]
4000​
[/td][td][/td][td=bgcolor:#FDE9D9]Aerial Field Engineer[/td][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
3​
[/td][td=bgcolor:#FDE9D9]
24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Trench Rural[/td][td]S[/td][td]
2000​
[/td][td][/td][td]Aerial Sr. Lineman[/td][td]L[/td][td]
3​
[/td][td]
24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Total[/td][td][/td][td]
6000​
[/td][td][/td][td=bgcolor:#FDE9D9]HDD Superintendent[/td][td=bgcolor:#FDE9D9]L[/td][td=bgcolor:#FDE9D9]
3​
[/td][td=bgcolor:#FDE9D9]
24​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]Total[/td][td][/td][td]
9​
[/td][td]
72​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
[FONT=&quot]I can't open or download your file:

We're sorry. We can't open the workbook in the browser because it uses these unsupported features:

• Microsoft Excel version 5.0 module sheets[/FONT]
[FONT=&quot]You might want to contact the author for more information.[/FONT]
 
Upvote 0
what is your excel version?

you need to download/save the file on your HD or Desktop, not open in browser!
then open in Excel
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
146
hagia_sofia
hagia_sofia
S
  • Solved
Diagonal Sum in excel table via VBA code
2
Replies
11
Views
485
myall_blues
myall_blues
S
  • Solved
Macro adding Sum if value is True
Replies
1
Views
93
sstarnes
S
E
  • Question Question
Filling Modules in a Trapezoid Shape Using Excel VBA
Replies
1
Views
228
Fluff
Fluff
N
  • Solved
Formula to consolidate Data and remove entries with no data in that year
2
Replies
10
Views
340
Newbie73
N

Forum statistics

Threads
1,223,705
Messages
6,173,989
Members
452,541
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top