Find all matches and consolidate them

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
ABC
1M1
2
Twigs
2M11Berries
3
4M23Twigs
5

<tbody>
</tbody>


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.

AB
1Twigs5
2Berries1
3
4
5

<tbody>
</tbody>


Thank you,
B
 
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

ABCCSumB
M1
2​
TwigsTwigs
5​
M1
1​
BerriesBerries
1​
M2
3​
Twigs

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?

First CharactersCountDCscription
M
4​
Aerial - 1/4" Preform Straight Splice
M
4​
Aerial - 1/4" Strand Vice
L
6​
Aerial Field Engineer
L
6​
Aerial Sr. Lineman
L
6​
HDD Superintendent
E
6​
F150/1500 Pickup
E
6​
F250/2500 Pickup
E
6​
17 SUV Escape Ford SE
S
4​
Trench Urban
S
4​
Trench Rural

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

Example file

DCscriptionFirst CharactersQty4DCscriptionFirst CharactersQty2
Aerial - 1/4" Preform Straight SpliceM
2​
F150/1500 PickupE
2​
Aerial - 1/4" Strand Vice M
2​
F250/2500 PickupE
2​
Total
4
17 SUV Escape Ford SEE
2​
Total
6
DCscriptionFirst CharactersQty4DCscriptionFirst CharactersQty2Qty4
Trench UrbanS
2​
Aerial Field EngineerL
2​
2​
Trench RuralS
2​
Aerial Sr. LinemanL
2​
2​
Total
4
HDD SuperintendentL
2​
2​
Total
6
 
Last edited:
Upvote 0
wrong tables above, example file is correct and tables should be like here

DCscriptionFirst CharactersQty4DCscriptionFirst CharactersQty2
Aerial - 1/4" Preform Straight SpliceM
4​
F150/1500 PickupE
3​
Aerial - 1/4" Strand Vice M
4​
F250/2500 PickupE
3​
Total
8
17 SUV Escape Ford SEE
3​
Total
9
DCscriptionFirst CharactersQty4DCscriptionFirst CharactersQty2Qty4
Trench UrbanS
4000​
Aerial Field EngineerL
3​
24​
Trench RuralS
2000​
Aerial Sr. LinemanL
3​
24​
Total
6000
HDD SuperintendentL
3​
24​
Total
9
72
 
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

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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