Sum/Merge only certain duplicates.

[wAtcHer1]

I apologize if I am not wording this correctly. I am new to this. I am hoping for a VBA that can solve this issue. Currently I am not using one. I normally find VBA’s online to use, I do not write them myself. The system we have at work is Microsoft 365 and we are still running Windows 10 Pro. I have a column/row with duplicate identifiers (SKU) and another column with pricing (EACH). I need to merge the duplicate identifiers that only have the same pricing and leave the duplicate with different pricing in place. My system will not let me download XL2BB so can only provide an image.

 

[kweaver]

Welcome to Mr. Excel,

You'll have better success with help if you post your sample sheet and expected result(s) using XL2BB. Otherwise, anyone trying to assist has to retype and recreate your example.

 

[wAtcHer1]

Welcome to Mr. Excel,

You'll have better success with help if you post your sample sheet and expected result(s) using XL2BB. Otherwise, anyone trying to assist has to retype and recreate your example.

Thank you for you response. I will try again to download XL2BB. The system at work is locked. It would not let me download. Will attempt a work around.

 

[alansidman]

Use Dropbox.Com or Box.Net and provide the link to that file.

 

[wAtcHer1]

here is the drop box link

Sum Spreadsheet.xlsm

Shared with Dropbox

www.dropbox.com

 

[alansidman]

Here is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"WORK FORMS", "SKU", "EACH"}, {{"Total QTY", each List.Sum([QTY]), type number}, {"Total Cost", each List.Sum([COST]), type number}})
in
    #"Grouped Rows"

Sum Spreadsheet.xlsm
	A	B	C	D	E
1	WORK FORMS	SKU	QTY	EACH	COST
2	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	2	$5.75	$11.50
3	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	2	$5.75	$11.50
4	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	1	$5.75	$5.75
5	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	3	$5.75	$17.25
6	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	4	$6.75	$27.00
7	15-24" Tinsel Work Wreath Form: Lime	XX750437	1	$5.75	$5.75
8	15-24" Tinsel Work Wreath Form: Lime	XX750437	2	$5.75	$11.50
9	15-24" Tinsel Work Wreath Form: Lime	XX750437	1	$5.75	$5.75
10	15-24" Tinsel Work Wreath Form: Lime	XX750437	3	$5.75	$17.25
11	15-24" Tinsel Work Wreath Form: Lime	XX750437	3	$6.75	$20.25
12
13
14	WORK FORMS	SKU	EACH	Total QTY	Total Cost
15	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	5.75	8	46
16	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	6.75	4	27
17	15-24" Tinsel Work Wreath Form: Lime	XX750437	5.75	7	40.25
18	15-24" Tinsel Work Wreath Form: Lime	XX750437	6.75	3	20.25
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUM(C2*D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Cell Value	duplicates	text	NO
B2:B6	Cell Value	duplicates	text	NO
B7:B12	Cell Value	duplicates	text	NO

 

[wAtcHer1]

I have never worked in Power Query so am going to spend some time figuring out how to do this. Certainly looks like it will do what I want. Thank you.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[kweaver]

Here's a non-vba approach if you don't mind having to rearrange one of the columns (which could be done via VBA).

Sum Spreadsheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	WORK FORMS	SKU	QTY	EACH	COST
2	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	2	$5.75	$11.50		15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	$5.75	8	$46.00
3	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	2	$5.75	$11.50		15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	$6.75	4	$27.00
4	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	1	$5.75	$5.75		15-24" Tinsel Work Wreath Form: Lime	XX750437	$5.75	7	$40.25
5	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	3	$5.75	$17.25		15-24" Tinsel Work Wreath Form: Lime	XX750437	$6.75	3	$20.25
6	15-24" Tinsel Work Wreath Form: Burlap	XX7504W4	4	$6.75	$27.00
7	15-24" Tinsel Work Wreath Form: Lime	XX750437	1	$5.75	$5.75
8	15-24" Tinsel Work Wreath Form: Lime	XX750437	2	$5.75	$11.50
9	15-24" Tinsel Work Wreath Form: Lime	XX750437	1	$5.75	$5.75
10	15-24" Tinsel Work Wreath Form: Lime	XX750437	3	$5.75	$17.25
11	15-24" Tinsel Work Wreath Form: Lime	XX750437	3	$6.75	$20.25
Sheet1

Cell Formulas
Range	Formula
H2:I5	H2	=UNIQUE(CHOOSE({1,2},B2:B11,D2:D11))
J2:J5	J2	=SUMIFS($C$2:$C$11,$B$2:$B$11,$H2,$D$2:$D$11,$I2)
K2:K5,E2:E11	K2	=I2*J2
G2:G5	G2	=XLOOKUP(H2,$B$2:$B$11,$A$2:$A$11)
Dynamic array formulas.

 

[wAtcHer1]

Thank you both, I am going to try the formula. However, I still would like to learn more about Power Query.