dynamical Merging rows and adding up sum

[burkeam]

I have several table/tabs queried into a single worksheet. I'm trying to merge duplicate rows with the same "Part#" and sum to "Qty Rebuild" of those parts. The "Full Description" is not identical to each other and may be removed. In the end I'm looking for this list to only show a single row of duplicate "Part#" with the total Sum of "Qty Rebuild". As a kicker it would be nice to also get the sum of "QTY. Print"

I have attempted pivot table, Query and a little VBA. If the solution is VBA I might beed more description on what is going one

#9 Furnace rebuild list 08192023.xlsx
	A	B	C	D	E	F	G
157			1	1	B6141051		Proximity Sensor Bracket , Unload Gripper
158			1	1	B6141051		Proximity Sensor Bracket , Unload Gripper
159			1	1			Pusher Rod - Ø1/2"-20 (Fine) Threaded Rod x 16 1/2" Length w/2 Nuts, Unload Gripper
160			1	1			Pusher Rod - Ø1/2"-20 (Fine) Threaded Rod x 16 1/2" Length w/2 Nuts, Unload Gripper
161			1			Onex	SAFFIL LD MAT 1.33 X 24 X 582 (97 SQ FT/CTN)-3000 Degree polycristalline fiber for expansion joints and packing
162			2	2	F-310	AFC HOLCROFT	SL AUBURN F-310 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS
163			8	8	RS1803	AFC HOLCROFT	SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS
164			6	6	RS1803	AFC HOLCROFT	SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS
165			8	8	RS1803	AFC HOLCROFT	SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS
166			4	4	RS1803	AFC HOLCROFT	SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS
167			30	25		Onex	Super Duty 9 X 4-1/2 x 3" #1 Wedge
168			175	160		Onex	Super Duty 9 X 4-1/2 x 3" #2 Wedge
169			45	40		Onex	Super Duty 9 X 4-1/2 X 3" #3 Wedge
170			1	1	D6090004		Tooling Arm Assembly, 2 jaw gripper assy
171			1	1	D6090004		Tooling Arm Assembly, 2 jaw gripper assy
Query1

 

[alansidman]

Really helpful if you had given us the column headers. How do we know which columns to work with? My crystal ball has had a malfunction and is out for repair

 

[burkeam]

Really helpful if you had given us the column headers. How do we know which columns to work with? My crystal ball has had a malfunction and is out for repair

Understood. Sorry about that and the CB not working. Not sure how to include them w XL2bb. Nevertheless here is a image
A= QTY rebuild, B= QTY on print, C=Part#

When C has duplicate want to merge and sum up all the A=Qty rebuild

again sorry and thanks

Burke

 

[alansidman]

With Power Query you can group by the Part Nr. Note that you have several parts with no part nr.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Part Nr"}, {{"Total Rebuild", each List.Sum([Qty Rebuild]), type number}})
in
    #"Grouped Rows"

Part Nr	Total Rebuild
B6141051	2
	253
F-310	2
RS1803	26
D6090004	2

 

[burkeam]

Forgive me for being a little slow and never really learned VBA


This file actually has 20+ sheet most with a table created from a print/drawing - lets call it several pages of Build Of Material(Dwg 61716 M13, M14, M14 an etc). My query then pulls out the items needed for a rebuild and this is how I get multiple part# from different tables.

For now I can deal with no part numbers and only worried about duplicates of the part#s. Not shown is the Full description which is different with each listing of the duplicate Part# as I refer to the print page. For this reason I dropped the full description or just keep the 1st occurance of the full description

=CONCATENATE(H5,", ",I5,J5,", ",K5,", ",L5)

0

6

RS1803

AFC HOLCROFT

SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS

AFC#

26656

Dwg 61716M13

Hearth Furnace XRH9

SL AUBURN F-610 ELECT. FEEDTHROUGH 5/8"-11 THREADS BOTH ENDS W/4 JAM NUTS, AFC# 26656, Dwg 61716M13, Hearth Furnace XRH9

This is my current query which was used to create the table I posted.

let
Source = Excel.CurrentWorkbook(),
#"Expanded Content2" = Table.ExpandTableColumn(Source, "Content", {"Order", "On Hand", "Qty Rebuild", "QTY. Print", "Part#", "Vendor", "Short Description", "Full Description"}, {"Order", "On Hand", "Qty Rebuild", "QTY. Print", "Part#", "Vendor", "Short Description", "Full Description"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Content2", each [Name] <> "Query1" and [Name] <> "Query1!Print_Area"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Qty Rebuild] <> null and [Qty Rebuild] <> 0)
in
#"Filtered Rows1"


your edits with some changes in the names

let
Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Part#"}, {{"Total Rebuild", each List.Sum([Qty Rebuild]), type number}})
in
#"Grouped Rows"

Not sure how I syntaxial put them together

Thank you much for the help

 

[alansidman]

If I understand your needs correctly, then

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"x", type text}, {"Y", type text}, {"name", type text}, {"cmt", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"name", each "L" & _, type text}}),
    #"Grouped Rows" = Table.Group(#"Added Prefix", {"Part#"}, {{"Total Rebuild", each List.Sum([Qty Rebuild]), type number}})
in
    #"Grouped Rows"

don't forget to use code tags when posting code for easier reading and clarification.

 

[burkeam]

Thanks again Alan,

Not sure about missing code tag as I just cut and pasted it.

Is it too much to ask to explain how your code is working else I will research - trying to learn what it is/I am doing. Once I getting it to worked out how do I use as a combined query

 

[alansidman]

Sorry, the code I provided was from another post. Apparantly, I forgot to clear the cache.

Here is my addition to your code.

let
Source = Excel.CurrentWorkbook(),
#"Expanded Content2" = Table.ExpandTableColumn(Source, "Content", {"Order", "On Hand", "Qty Rebuild", "QTY. Print", "Part#", "Vendor", "Short Description", "Full Description"}, {"Order", "On Hand", "Qty Rebuild", "QTY. Print", "Part#", "Vendor", "Short Description", "Full Description"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Content2", each [Name] <> "Query1" and [Name] <> "Query1!Print_Area"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Qty Rebuild] <> null and [Qty Rebuild] <> 0),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Part#"}, {{"Total Rebuild", each List.Sum([Qty Rebuild]), type number}})
in
#"Grouped Rows"
[/CODE

RE:  Code
If posting code snippets and/or functions, please use the corresponding language's BB code button on the editor toolbar. For example, if you are posting sample VBA code, then simply select the code snippet in the editor, and click the VBA button on the toolbar.
[IMG]https://www.mrexcel.com/board/attachments/1598935887481-png.21426/[/IMG]
Check [URL='https://www.mrexcel.com/board/help/how-to-post-your-vba-code/']this help page[/URL] for more information about posting your code.

 

[burkeam]

OK making progress and messing with the code. I still have few issues

1. it only processes 55 rows of data where as I have 111 row with only a few duplicates of parts #(will eventually have > 1,000 in the BOM)
2. Need to add in the Vender and short Description and possibly the Print Qty
3. Would like to have my original query left intact then make a few new additional sheets with newly grouped/extracted data.

Reasoning, All of these parts are the entire BOM but then break it down few different simplified lists(sheets) for rebuild i.e. Furnace, press, controls and etc.

 

[burkeam]

Hello Alan,

Really want to thank you for the time you spent aiding me. I ended up finding a good solution through pivot tables.
thanks again