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 · Jul 18, 2022

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

alansidman · Jul 20, 2022

To learn more about PQ, click on the link in my signature.

gzesasaki · Jul 20, 2022

Peter_SSs said:
Please answer all of the questions.

oppss sorry sir,
Data should be sorted according to Column D and
Colums J, K, N have to be summed..

hope i have answers correctly

thank you

Peter_SSs · Jul 21, 2022

gzesasaki said:
Colums J, K, N have to be summed..

Thank you.
Try this with a copy of your data.

VBA Code:

Sub Combine_Rows()
  Dim r As Long
  
  Application.ScreenUpdating = False
  Range("A5", Range("N" & Rows.Count).End(xlUp)).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlNo
  For r = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -1
    If Range("D" & r).Value = Range("D" & r - 1).Value Then
      Range("L" & r).Resize(, 3).Copy
      Range("L" & r - 1).PasteSpecial Operation:=xlAdd
      Rows(r).Delete
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

gzesasaki · Jul 21, 2022

Peter_SSs said:

Thank you.
Try this with a copy of your data.

VBA Code:

Sub Combine_Rows()
  Dim r As Long
 
  Application.ScreenUpdating = False
  Range("A5", Range("N" & Rows.Count).End(xlUp)).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlNo
  For r = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -1
    If Range("D" & r).Value = Range("D" & r - 1).Value Then
      Range("L" & r).Resize(, 3).Copy
      Range("L" & r - 1).PasteSpecial Operation:=xlAdd
      Rows(r).Delete
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

thank you sir for the code.

the data are sorting according to column perfectly but rows with the same value in column D are not adding/combining and so columns J,K N are not adding too.

regards,
gzesasaki

Peter_SSs · Jul 22, 2022

gzesasaki said:
rows with the same value in column D are not adding/combining and so columns J,K N are not adding too.

They are for me. First thing to check is for two items that look the same in column D but are not combining, check that say one of them has a space character at the end and one does not.

But in any case also make this change in the code.

Rich (BB code):

Sub Combine_Rows()
  Dim r As Long
  
  Application.ScreenUpdating = False
  Range("A5", Range("N" & Rows.Count).End(xlUp)).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlNo
  For r = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -1
    If Range("D" & r).Value = Range("D" & r - 1).Value Then
      Range("L" & r).Resize(, 3).Copy
      Range("L" & r - 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
      Rows(r).Delete
    End If
  Next r
  Application.ScreenUpdating = True
End Sub

gzesasaki · Jul 24, 2022

Peter_SSs said:
They are for me. First thing to check is for two items that look the same in column D but are not combining, check that say one of them has a space character at the end and one does not.

But in any case also make this change in the code.

Rich (BB code):

Sub Combine_Rows() Dim r As Long Application.ScreenUpdating = False Range("A5", Range("N" & Rows.Count).End(xlUp)).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlNo For r = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -1 If Range("D" & r).Value = Range("D" & r - 1).Value Then Range("L" & r).Resize(, 3).Copy Range("L" & r - 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd Rows(r).Delete End If Next r Application.ScreenUpdating = True End Sub

hi sir,
i have check and it is something to do with the string in column D, now the other rows with the same value are getting deleted but the values on column J, K and N are not adding.

thank you in advance again sir..

Peter_SSs · Jul 24, 2022

The code from post #15 worked for me with the sample data from post #5. Are you saying that is not the case for you?

If it works for you for that particular sample but not some other data then please provide a small set of XL2BB sample data where it does not work for you.

gzesasaki · Jul 25, 2022

Peter_SSs said:
The code from post #15 worked for me with the sample data from post #5. Are you saying that is not the case for you?

If it works for you for that particular sample but not some other data then please provide a small set of XL2BB sample data where it does not work for you.

hi sir please find the attached

RELIEF sales for test.xlsm

A

B

C

D

H

I

J

K

L

M

N

O

P

2

3

tax_month

seq_no

tin

registered_name

address1

address2

gsales

gtsales

gesales

gzsales

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

Exempt

Zero-Rated

Taxable (Net of VAT)

Tax Rate

Total Output Tax

5

31/05/2022

1

241556496000

ANSON @HOME INC.

#23 ADB AVE.ORTIGAS CENTER PASIG CITY

74,710.67

-

12.00

8,965.28

6

31/05/2022

2

000106840000

ANSON EMPORIUM CORPORATION

ALABANG TOWN CENTER-LANDMARK BLDG.,

MAKATI AVENUE, AYALA CENTER, MAKATI CITY

56,350.08

-

12.00

6,762.01

7

31/05/2022

3

006899069000

ANSON MARKETING AND COMMERCIAL INC.

2ND FLOOR CASH & CARRY MALL

FILMORE ST.,MAKATI CITY

93,207.17

-

12.00

11,184.86

8

31/05/2022

4

000106840000

ANSON'S EMPORIUM CORPORATION

ANSON MAKATI -LANDMARK BLDG.,

MAKATI AVENUE, AYALA CENTER, MAKATI CITY

43,892.83

-

12.00

5,267.14

9

31/05/2022

5

158810907000

AUDIBLE ILLUSIONS ENTERPRISE

50 D. TIMOG AVENUE, QUEZON CITY

0.00

-

12.00

0.00

10

31/05/2022

6

220363969000

AUDIO WORLD TECHNOLOGY INC.

749 BANAWE AVE. CORNER SCOUT ALCARAZ

QUEZON CITY

20,678.58

-

12.00

2,481.43

11

31/05/2022

7

AVX AUDIO SYSTEM

SEA SCAPE VILLAGE

ATANG DELA RAMA ST.

0.00

-

12.00

0.00

12

31/05/2022

8

BETTER COMPONENT INCORPORATED

JUAN LUNA ST. BRGY 29-C POBLACION DISTRICT DAVAO CITY 8000

206,785.75

-

12.00

24,814.29

13

31/05/2022

12

COOL PLANET

(0.00)

-

12.00

(0.00)

14

31/05/2022

13

000124374

HONDA CARS PHILS. INC. LAGUNA

105 SOUTH MAIN AVENUE LAGUNA

TECHNOPARK STA. ROSA LAGUNA

0.00

-

12.00

0.00

15

31/05/2022

14

240570218000

JFK POWER SALES & CONSTRUCTION

43 Garnet St. Posadas Village

Bgy. Sucat Muntinlupa City

974,852.67

-

12.00

116,982.32

16

31/05/2022

15

005589352000

KEVIN KARLO CORP.

1518 Felix Huertas St. Sta. Cruz Manila

St. Sta. Cruz Manila

1,095,334.83

-

12.00

131,440.18

17

31/05/2022

16

SOUND GALLERY

MAKATI CITY

(0.00)

-

12.00

(0.00)

18

31/05/2022

17

000086204000

STAR APPLIANCE CENTER INC.-BACOOR

BACOOR CAVITE

BACOLOD CITY

48,000.00

-

12.00

5,768.78

19

31/05/2022

19

000086204000

STAR APPLIANCE CENTER INC.-BATANGAS

BRGY PALIOCAN WEST BATANGAS

CITY

(2,331.67)

-

12.00

(279.80)

20

31/05/2022

23

000086204000

STAR APPLIANCE CENTER INC.-DAVAO LANANG

Premiere J.P. Laurel Ave., Brgy San Antonio

Agdao Dist.,Davao City Davao Del Sur

134,478.83

-

12.00

16,137.46

21

31/05/2022

24

000086204000

STAR APPLIANCE CENTER INC.-SOUTHMALL

ZAPOTE ROAD LAS PINAS

120,424.33

-

12.00

14,450.92

22

31/05/2022

25

000086204000

STAR APPLIANCE CENTER INC.-MALL OF ASIA

1103-1104 GRD.FLR.SM MOA JW DIOKNO

DPT-1A PASAY CITY

66,474.58

-

12.00

7,976.95

23

31/05/2022

26

000086204000

STAR APPLIANCE CENTER INC.-MANILA

CONCEPCION COR. ARROCEROS AND

SAN MARCELINO ST. BESIDE

43,702.17

-

12.00

5,244.26

24

31/05/2022

27

000086204000

STAR APPLIANCE CENTER INC.-MEGA A

SM MEGAMALL BLDG. COR. EDSA

BRGY. WACK-WACK, MANDALUYONG CITY

0.00

-

12.00

0.00

25

31/05/2022

28

000086204000

STAR APPLIANCE CENTER INC.-MEGA B

SM MEGAMALL BLDG. EDSA COR. DOÑA

JULIA VARGAS AVENUE WACK-WACK GREENHILLS,CITY OF

380,680.50

-

12.00

45,681.66

26

31/05/2022

29

000086204000

STAR APPLIANCE CENTER INC.-N.E. III

SM NORTH EDSA LOWER GRD. FLOOR

33,740.75

-

12.00

4,048.89

27

31/05/2022

30

000086204000

STAR APPLIANCE CENTER INC.-PAMPANGA

SAN FERNANDO PAMPANGA

64,887.08

-

12.00

7,786.45

28

31/05/2022

31

000086204000

STAR APPLIANCE CENTER INC.-PASIG

8012 E. RODRIQUEZ JR.AVE., BRGY UGONG PASIG CITY

(9,375.00)

-

12.00

(1,125.00)

29

31/05/2022

32

000086204000

STAR APPLIANCE CENTER INC.-SAN LAZARO

FELIX HUERTAS COR.A.H.LACSON

STA. CRUZ MANILA

86,821.58

-

12.00

10,418.59

30

31/05/2022

33

000086204000

STAR APPLIANCE CENTER INC.-SEASIDE CEBU

SM CITY CEBU S.R.P.

GF SM CITY SEASIDE MAMBALING ROAD,SOUTH ROAD

34,129.50

-

12.00

4,095.54

31

31/05/2022

34

000086204000

STAR APPLIANCE CENTER INC.-SOUTHMALL

ZAPOTE ROAD LAS PINAS

42,948.92

-

12.00

5,153.87

32

31/05/2022

35

010128681000

SYMPHONY AUDIO VIDEO CORP.

UNIT 3023, 3RD FLR. SM SEASIDE CITY CEBU,

MAMBALING, CEBU CITY 6000

403,232.17

-

12.00

48,387.86

33

31/05/2022

36

00041571300000

TOYOTA MOTOR PHILIPPINES CORPORATION

Toyota Special Economic Zone,

Sta Rosa-Tagaytay Highway, Sta Rosa City Laguna

0.00

-

12.00

-

34

31/05/2022

37

005-437-336

UNITED EVANGELICAL CHURCH OF LEGASPI

LEGASPI, ALBAY

103,660.75

-

12.00

12,439.29

35

31/05/2022

38

WALK IN

0.00

-

12.00

-

36

31/05/2022

39

223359036007

WESTERN GRAND CENTRAL CO.,INC. - FESTIVAL

3RD FLOOR,FILINVEST CORPORATE CITY

ALABANG,MUNTINLUPA CITY 1770

437,547.08

-

12.00

52,505.65

37

31/05/2022

40

223359036006

WESTERN GRAND CENTRAL CO.,INC. - STA LUCIA

PHASE 3 BASEMENT LEVEL STA.LUCIA

EAST GRAND MALL MARCOS HIGHWAY

309,874.08

-

12.00

37,184.89

38

31/05/2022

1

HONDA PHILIPPINES INC

TANAUAN CITY, BATANGAS

10,267.83

-

12.00

1,232.14

39

31/05/2022

2

LOYOLA HOUSE OF STUDIES

ATENEO DE MANILA UNIVERSITY,

1,339.25

-

12.00

160.71

40

31/05/2022

3

WALK IN

29,062.08

-

12.00

3,487.45

Sales

Peter_SSs · Jul 25, 2022

Looks like the layout of the data has changed since the beginning when it went from column A to column N.
Now it goes to column P.

Can you confirm which columns need to be added for this changed layout?

gzesasaki · Jul 25, 2022

Peter_SSs said:
Looks like the layout of the data has changed since the beginning when it went from column A to column N.
Now it goes to column P.

Can you confirm which columns need to be added for this changed layout?

it is true sir, it has changed and sorry for that I have deleted some columns on my first attachement I sent you..

The columns are J, K and P.

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

New Member

alansidman

Well-known Member

Excel Facts

gzesasaki

New Member

Peter_SSs

MrExcel MVP, Moderator

gzesasaki

New Member

Peter_SSs

MrExcel MVP, Moderator

gzesasaki

New Member

Peter_SSs

MrExcel MVP, Moderator

gzesasaki

New Member

Peter_SSs

MrExcel MVP, Moderator

gzesasaki

New Member

Similar threads

Forum statistics

Share this page

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)?

New Member

Well-known Member

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock