"SUM" columns if specific characters are same

Panoos64 · Jan 18, 2019

Hi all, I would like to use a VBA code so that to “SUM” the rows from col. “B” to “N” if the left 7 characters in col. “A” are same. I present below the original data and expected result. Thank you all in advance

Original data

 [TABLE="class: MsoNormalTable, width: 457"]
<tbody>[TR]
[TD="width: 28"][/TD]
[TD="width: 188"]

A

[/TD]
[TD="width: 108, colspan: 2"]

B

[/TD]
[TD="width: 85"]

C

[/TD]
[TD="width: 113"]

D

[/TD]
[TD="width: 80"]

E

[/TD]
[TD="width: 80"]

F….

[/TD]
[TD="width: 80"]

N

[/TD]
[/TR]
[TR]
[TD="width: 28"]

4

[/TD]
[TD="width: 188"][/TD]
[TD="width: 108, colspan: 2"]

January

[/TD]
[TD="width: 85"]

February

[/TD]
[TD="width: 113"]

March

[/TD]
[TD="width: 80"]

April

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

TOTALS

[/TD]
[/TR]
[TR]
[TD="width: 28"]

5

[/TD]
[TD="width: 208, colspan: 2"]

2020010-EMPL 10

[/TD]
[TD="width: 89"]

2050

[/TD]
[TD="width: 85"]

2050

[/TD]
[TD="width: 113"]

2050

[/TD]
[TD="width: 80"]

2100

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

8250

[/TD]
[/TR]
[TR]
[TD="width: 28"]

6

[/TD]
[TD="width: 188"]

2020020-EMPL 20

[/TD]
[TD="width: 108, colspan: 2"]

1750

[/TD]
[TD="width: 85"]

1750

[/TD]
[TD="width: 113"]

1780

[/TD]
[TD="width: 80"]

1800

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

7080

[/TD]
[/TR]
[TR]
[TD="width: 28"]

7

[/TD]
[TD="width: 188"]

2020010-EMPL 12

[/TD]
[TD="width: 108, colspan: 2"]

250

[/TD]
[TD="width: 85"]

300

[/TD]
[TD="width: 113"]

210

[/TD]
[TD="width: 80"]

320

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

1080

[/TD]
[/TR]
</tbody>[/TABLE]


Expected result

[TABLE="class: MsoNormalTable, width: 455"]
<tbody>[TR]
[TD="width: 28"][/TD]
[TD="width: 188"]

A

[/TD]
[TD="width: 108"]

B

[/TD]
[TD="width: 80"]

C

[/TD]
[TD="width: 113"]

D

[/TD]
[TD="width: 80"]

E

[/TD]
[TD="width: 80"]

F….

[/TD]
[TD="width: 80"]

N

[/TD]
[/TR]
[TR]
[TD="width: 28"]

4

[/TD]
[TD="width: 188"][/TD]
[TD="width: 108"]

January

[/TD]
[TD="width: 80"]

February

[/TD]
[TD="width: 113"]

March

[/TD]
[TD="width: 80"]

April

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

TOTALS

[/TD]
[/TR]
[TR]
[TD="width: 28"]

5

[/TD]
[TD="width: 188"]

2020010-EMPL 10

[/TD]
[TD="width: 108"]

2300

[/TD]
[TD="width: 80"]

2350

[/TD]
[TD="width: 113"]

2260

[/TD]
[TD="width: 80"]

2420

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

9330

[/TD]
[/TR]
[TR]
[TD="width: 28"]

6

[/TD]
[TD="width: 188"]

2020020-EMPL 20

[/TD]
[TD="width: 108"]

1750

[/TD]
[TD="width: 80"]

1750

[/TD]
[TD="width: 113"]

1780

[/TD]
[TD="width: 80"]

1800

[/TD]
[TD="width: 80"]

……

[/TD]
[TD="width: 80"]

7080

[/TD]
[/TR]
</tbody>[/TABLE]

Scott T · Jan 18, 2019

Book1

A

B

C

D

E

F

G

4

January

February

March

April

TOTALS

5

2020010-EMPL 10

2,050.00

2,100.00

8,250.00

6

2020020-EMPL 20

1,750.00

1,780.00

1,800.00

7,080.00

7

2020010-EMPL 12

250.00

300.00

210.00

320.00

1,080.00

8

9

10

2020010-EMPL 10

11

2020020-EMPL 20

Sheet1

Cell Formulas
Range	Formula
B10	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A10,7)),B$5:B$7)
B11	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A11,7)),B$5:B$7)
C10	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A10,7)),C$5:C$7)
C11	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A11,7)),C$5:C$7)
D10	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A10,7)),D$5:D$7)
D11	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A11,7)),D$5:D$7)
E10	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A10,7)),E$5:E$7)
E11	=SUMPRODUCT(--(LEFT($A$5:$A$7,7)=LEFT($A11,7)),E$5:E$7)
G10	=SUM(B10:E10)
G11	=SUM(B11:E11)

Panoos64 · Jan 18, 2019

Hi Scott, the formula is worked, but is there any way to use VBA? I would like the code to run through col. "A" and where find codes which are matching the 7 left characters, should "SUM" the amounts from col. "B" till col. "N" (for each month) and appears only the row with "SUM".
However thank you for your support. Hv a great day

Scott T · Jan 18, 2019

Yes this can be done by VBA. I am not clear on where the results should be placed. Column B to N is 13 columns since it is one for each month then should it not be B to M?

Fluff · Jan 18, 2019

How about

Code:

Sub panoose64()
   Dim Cl As Range
   Dim Vu As String
   Dim a(1 To 14) As Variant
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))
         Vu = Left(Cl.Value, 7)
         If Not .Exists(Vu) Then
            .Add Vu, Application.Index(Cl.Resize(, 14), 1, 0)
         Else
            a(1) = .Item(Vu)(1)
            For i = 2 To 14
               a(i) = .Item(Vu)(i) + Cl.Offset(, i - 1).Value
            Next i
            .Item(Vu) = a
         End If
      Next Cl
      Sheets("Sheet2").Range("a2").Resize(.Count, 14).Value = Application.Index(.Items, 0, 0)
   End With
End Sub

Panoos64 · Jan 21, 2019

Is great job Fluff !! Is what i was required. Thank you so much for your support and your time once again. Hv a great, lovely day

Fluff · Jan 21, 2019

You're welcome & thanks for the feedback

"SUM" columns if specific characters are same

Panoos64

Well-known Member

Excel Facts

Scott T

Well-known Member

Panoos64

Well-known Member

Scott T

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

"SUM" columns if specific characters are same

Panoos64

Well-known Member

Excel Facts

Scott T

Well-known Member

Panoos64

Well-known Member

Scott T

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

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