how to add/sum two or more rows with similar cell value then put the result on the last row and delete the other similar rows (in VBA)?

[gzesasaki]

Dear Sir/Madam,

can you please help me in resolving my issue on excel and what i am trying to do is to add/sum two or more rows with similar cell value then put the result on the last row (or overwrite the first row with the cell value being summed) and then delete the other rows with cell value being summed.

i am attaching an excel png as sample..

" two rows with same value on coumn D ("WALK IN") then cell G should be added. And Im attaching also the required result..

thank you very much in advance..
gzesasaki

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Can we assume that any rows that contain the same value in column D will be next to each other like your sample?
   
   
2. Can we assume that if rows to be combined have different values in some columns (eg column B for "WALK IN") then we keep the value in the higher row (apart from the summed column)?

It generally helps get faster answers in the forum if you provide your sample data in a form that can be copied to test with rather than a picture. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[gzesasaki]

thank you for the reply sir peter,

Answer for 1, no Sir
and for number 2, Yes Sir as per the example attached..

 

[Peter_SSs]

Answer for 1, no Sir

In that case ..

1. Could we have a small set of sample data and expected results (preferably with XL2BB) that includes at least one example where the same column D value occurs in 2 or 3 disjoint rows?
   
   
2. Would it be acceptable to sort the data by column D so they are all together?

 

[gzesasaki]

RELIEF sales 092021DDDDDDD.xls
	A	B	C	D				H	I	J	K	L	M	N
1	Actions
2
3	tax_month	seq_no	tin	registered_name				address1	address2	gsales	gtsales		tax_rate	touttax
4	Tax Month	Seq. No	TIN Number	Registered Name (For Non-Individuals)				Substreet, Street, Brgy	District, City, Zip Code	Gross Taxable	Total Sales	Taxable (Net of VAT)	Tax Rate	Total Output Tax
5	30/04/2022	1	241556496000	ANSON HOME INC						25,000.00	25,000.00	-	12.00	3,000.00
6	30/04/2022	2	000106840000	ANSON EMPORIUM CORPORATION				Full Address	MAKATI AVENUE AYALA CENTER MAKATI CITY	47,928.58	47,928.58	-	12.00	5,751.43
7	30/04/2022	3	006899069000	ANSON MARKETING AND COMMERCIAL INC				Full Address	FILMORE STMAKATI CITY	42,210.58	42,210.58	-	12.00	5,065.27
8	30/04/2022	4	000534418	BOHOL I ELECTRIC COOP INC				Full Address	DELIVERYBOHECO 1 WAREHOUSE	1,241,785.75	1,241,785.75	-	12.00	149,014.29
9	30/04/2022	5	000542642000	CABANATUAN ELECTRIC CORP				Full Address	CABANATUAN CITY	2,527,928.58	2,527,928.58	-	12.00	303,351.43
10	30/04/2022	6	411502915000	ELEMAX ENTERPRISES				Full Address	NATL HIGHWAY SAN PEDRO MALLIG	54,964.25	54,964.25	-	12.00	6,595.71
11	30/04/2022	7	109537722	ENGR ERNESTO GAYYAD				Full Address	KALINGA APAYAO	(157,142.83)	(157,142.83)	-	12.00	(18,857.14)
12	30/04/2022	8	102109169	FANM ENTERPRISES				Full Address	BATASAN HILLS QUEZON CITY	260,357.17	260,357.17	-	12.00	31,242.86
13	30/04/2022	9	000124374	HONDA CARS PHILS INC LAGUNA				Full Address	TECHNOPARK STA ROSA LAGUNA	1,530,535.67	1,530,535.67	-	12.00	183,664.28
14	30/04/2022	10	240570218000	JFK POWER SALES CONSTRUCTION				Full Address	Bgy Sucat Muntinlupa City	1,164,937.50	1,164,937.50	-	12.00	139,792.50
15	30/04/2022	11	005589352000	KEVIN KARLO CORP				Full Address	St Sta Cruz Manila	21,160.75	21,160.75	-	12.00	2,539.29
16	30/04/2022	12	139130413000	MPS CONSTRUCTION AND TRADING				Full Address	ABAR 1ST SAN JOSE CITY	203,062.50	203,062.50	-	12.00	24,367.50
17	30/04/2022	13	921830722000	QSIX ELECTRICAL SALES ENGINEERING				Full Address	ILAGAN ISABELA	716,825.92	716,825.92	-	12.00	86,019.11
18	30/04/2022	14	215016032000	RBF ENTERPRISES				Full Address	DAMPOL 2ND PULILAN BULACAN	586,285.75	586,285.75	-	12.00	70,354.29
19	30/04/2022	15	00041571300000	TOYOTA MOTOR PHILIPPINES CORPORATION				Full Address	Sta RosaTagaytay Highway Sta Rosa City Laguna	3,667,099.08	3,667,099.08	-	12.00	440,051.89
20	30/04/2022	16	223359036007	WESTERN GRAND CENTRAL COINC FESTIVAL				Full Address	ALABANGMUNTINLUPA CITY 1770	35,755.58	35,755.58	-	12.00	4,290.67
21	30/04/2022	17	223359036006	WESTERN GRAND CENTRAL COINC STA LUCIA				Full Address	EAST GRAND MALL MARCOS HIGHWAY	125,345.42	125,345.42	-	12.00	15,041.45
22	30/04/2022	12	000000000001	WALK IN						125,342.42	125,342.42	-	12.00	15,041.45
23	30/04/2022	18	223359036004	WESTERN GRAND CENTRAL COINCTRINOMA				Full Address	North Avenue COR EDSA	40,938.58	40,938.58	-	12.00	4,912.63
24	30/04/2022	19	000326100005	WESTERN MARKETING CORPORATIONMORATO				Full Address	FRANCISCO DEL MONTE	16,573.67	16,573.67	-	12.00	1,988.84
25	30/04/2022	20	000000000001	WALK IN						10,000.00	10,000.00	-	12.00	1,200.00
Sales

Cell Formulas
Range	Formula
N25	N25	=(K25*0.12)

 

[Peter_SSs]

Thanks for the sample data in a 'copyable' form.

Would it be acceptable to sort the data by column D so they are all together?

.. or must the remaining rows be kept in the original relative order?


Also, there are a number of numerical columns on the right. Can you confirm which one (or ones) need to be summed as the rows are combined?

 

[alansidman]

Did a group by on the names and summed the sales and the tax with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"tin", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"registered_name"}, {{"Sales", each List.Sum([#" gsales "]), type number}, {"Taxes", each List.Sum([#" touttax "]), type number}})
in
    #"Grouped Rows"

 

[gzesasaki]

Thanks for the sample data in a 'copyable' form.


.. or must the remaining rows be kept in the original relative order?


Also, there are a number of numerical columns on the right. Can you confirm which one (or ones) need to be summed as the rows are combined?

yes sir, it is actually should be sorted according to column D.
thank you again..

 

[gzesasaki]

Did a group by on the names and summed the sales and the tax with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"tin", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"registered_name"}, {{"Sales", each List.Sum([#" gsales "]), type number}, {"Taxes", each List.Sum([#" touttax "]), type number}})
in
    #"Grouped Rows"

i am not yet familiar with power query sir..

 

[Peter_SSs]

Please answer all of the questions.