Find all matches and consolidate them

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
81
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,716
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,716
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,716
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,716
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:

BKGLTS

Board Regular
Joined
Aug 27, 2018
Messages
81
[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]
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,716
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:

Forum statistics

Threads
1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top