Sum Column B based on Column A using Excel VBA Macro

Rakesh Kamani · Feb 25, 2020

There are 4 worksheets in the workbook. Command button in sheet 1, project name and actual hours in sheet 2, project name and planned hours based on month and year in sheet 3, final report display on sheet 4. (Name of project, actual hours and planned hours).

When I click on the Command button on Sheet 1, Report Display on Sheet 4 based on Sheet 2 and Sheet 3.

How to write a VBA in this case?

Thank you in advance.

DanteAmor · Feb 27, 2020

Hi @Rakesh Kamani.

I put here a first code considering the data as your images. Adding the columns from March to June.

Dante Amor

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Project Name

product

Division

Hours

Phase

2

M312101

Key

TD-Pu

4

1

3

M309401

Doors

TDP

6

2

4

M309201

Metal

TDS

3

5

M309401

Iron

MR

4

6

M312101

Metal

EX

2

5

Actual_Hours

Dante Amor

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Project

2020/01

2020/02

2020/03

2020/04

2020/05

2020/06

2

M327905-A M SYC SBW

138.5

125.62

152

140

125.32

362.14

3

Total:

4

M309201-A M SYC SBW

25

65

85

32.5

145.63

85

5

Total:

6

M309401-A MM SYMC SBW

145.32

36.14

23.56

125

365

12

7

Total:

8

M327905-A M SYC SBW

25.23

365

45.12

21.4

25.36

21.3

9

Total:

10

M312101- SYMC SBW

54.3

25.63

241

2.53

3

14.23

11

Total:

12

13

M327905-A MM_2

25.3

23.1

25.36

45.85

78.06

65

Planned_Hours

Dante Amor

A

B

C

1

Project

Hours

Planned

2

M312101

6

260.76

3

M309401

10

525.56

4

M309201

3

348.13

AH_Vs_PH_Report

VBA Code:

Sub Report()
  Dim sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
  Dim a As Variant, ky As Variant, nSum As Double
  Dim dic As Object, i As Long, lr As Long
  Dim c As Range, r As Range, f As Range, cell As String
  
  Set sh2 = Sheets("Actual_Hours")
  Set sh3 = Sheets("Planned_Hours")
  Set sh4 = Sheets("AH_Vs_PH_Report")
  sh4.Rows("2:" & Rows.Count).ClearContents
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = sh2.Range("D2:N" & sh2.Range("D" & Rows.Count).End(3).Row).Value2
  
  For i = 1 To UBound(a)
    dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10)
  Next
  Set r = sh3.Range("D:E")
  
  For Each ky In dic.keys
    nSum = 0
    Set f = r.Find(ky, , xlValues, xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        nSum = nSum + WorksheetFunction.Sum(sh3.Range(sh3.Cells(f.Row, "K"), sh3.Cells(f.Row, "R")))
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      lr = sh4.Range("A" & Rows.Count).End(3).Row + 1
      sh4.Range("A" & lr).Value = ky
      sh4.Range("B" & lr).Value = dic(ky)
      sh4.Range("C" & lr).Value = nSum
    End If
  Next
End Sub

I understand that several things are missing, but I hope you can try this and move forward with whatever you need.

Rakesh Kamani · Feb 28, 2020

thank you Amor,

The above image you can consider 1 project, because project ID is M327905.
you can consider and calculate Automotive/Private separately .

Rakesh Kamani · Feb 28, 2020

DanteAmor said:
Hi @Rakesh Kamani.

I put here a first code considering the data as your images. Adding the columns from March to June.

Dante Amor
A B C D E F G H I J K L M N
1 Project Name product Division Hours Phase
2 M312101 Key TD-Pu 4 1
3 M309401 Doors TDP 6 2
4 M309201 Metal TDS 3 3
5 M309401 Iron MR 4 4
6 M312101 Metal EX 2 5
Actual_Hours

Dante Amor
A B C D E F G H I J K L M N O P Q R
1 Project 2020/01 2020/02 2020/03 2020/04 2020/05 2020/06
2 M327905-A M SYC SBW 138.5 125.62 152 140 125.32 362.14
3 Total:
4 M309201-A M SYC SBW 25 65 85 32.5 145.63 85
5 Total:
6 M309401-A MM SYMC SBW 145.32 36.14 23.56 125 365 12
7 Total:
8 M327905-A M SYC SBW 25.23 365 45.12 21.4 25.36 21.3
9 Total:
10 M312101- SYMC SBW 54.3 25.63 241 2.53 3 14.23
11 Total:
12
13 M327905-A MM_2 25.3 23.1 25.36 45.85 78.06 65
Planned_Hours

Dante Amor
A B C
1 Project Hours Planned
2 M312101 6 260.76
3 M309401 10 525.56
4 M309201 3 348.13
AH_Vs_PH_Report

VBA Code:

Sub Report() Dim sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet Dim a As Variant, ky As Variant, nSum As Double Dim dic As Object, i As Long, lr As Long Dim c As Range, r As Range, f As Range, cell As String Set sh2 = Sheets("Actual_Hours") Set sh3 = Sheets("Planned_Hours") Set sh4 = Sheets("AH_Vs_PH_Report") sh4.Rows("2:" & Rows.Count).ClearContents Set dic = CreateObject("Scripting.Dictionary") a = sh2.Range("D2:N" & sh2.Range("D" & Rows.Count).End(3).Row).Value2 For i = 1 To UBound(a) dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10) Next Set r = sh3.Range("D:E") For Each ky In dic.keys nSum = 0 Set f = r.Find(ky, , xlValues, xlPart) If Not f Is Nothing Then cell = f.Address Do nSum = nSum + WorksheetFunction.Sum(sh3.Range(sh3.Cells(f.Row, "K"), sh3.Cells(f.Row, "R"))) Set f = r.FindNext(f) Loop While Not f Is Nothing And f.Address <> cell lr = sh4.Range("A" & Rows.Count).End(3).Row + 1 sh4.Range("A" & lr).Value = ky sh4.Range("B" & lr).Value = dic(ky) sh4.Range("C" & lr).Value = nSum End If Next End Sub

I understand that several things are missing, but I hope you can try this and move forward with whatever you need.

Thank you DanteAmor

Rakesh Kamani · Mar 2, 2020

DanteAmor said:
Hi @Rakesh Kamani.

I put here a first code considering the data as your images. Adding the columns from March to June.

Dante Amor
A B C D E F G H I J K L M N
1 Project Name product Division Hours Phase
2 M312101 Key TD-Pu 4 1
3 M309401 Doors TDP 6 2
4 M309201 Metal TDS 3 3
5 M309401 Iron MR 4 4
6 M312101 Metal EX 2 5
Actual_Hours

Dante Amor
A B C D E F G H I J K L M N O P Q R
1 Project 2020/01 2020/02 2020/03 2020/04 2020/05 2020/06
2 M327905-A M SYC SBW 138.5 125.62 152 140 125.32 362.14
3 Total:
4 M309201-A M SYC SBW 25 65 85 32.5 145.63 85
5 Total:
6 M309401-A MM SYMC SBW 145.32 36.14 23.56 125 365 12
7 Total:
8 M327905-A M SYC SBW 25.23 365 45.12 21.4 25.36 21.3
9 Total:
10 M312101- SYMC SBW 54.3 25.63 241 2.53 3 14.23
11 Total:
12
13 M327905-A MM_2 25.3 23.1 25.36 45.85 78.06 65
Planned_Hours

Dante Amor
A B C
1 Project Hours Planned
2 M312101 6 260.76
3 M309401 10 525.56
4 M309201 3 348.13
AH_Vs_PH_Report

VBA Code:

Sub Report() Dim sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet Dim a As Variant, ky As Variant, nSum As Double Dim dic As Object, i As Long, lr As Long Dim c As Range, r As Range, f As Range, cell As String Set sh2 = Sheets("Actual_Hours") Set sh3 = Sheets("Planned_Hours") Set sh4 = Sheets("AH_Vs_PH_Report") sh4.Rows("2:" & Rows.Count).ClearContents Set dic = CreateObject("Scripting.Dictionary") a = sh2.Range("D2:N" & sh2.Range("D" & Rows.Count).End(3).Row).Value2 For i = 1 To UBound(a) dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10) Next Set r = sh3.Range("D:E") For Each ky In dic.keys nSum = 0 Set f = r.Find(ky, , xlValues, xlPart) If Not f Is Nothing Then cell = f.Address Do nSum = nSum + WorksheetFunction.Sum(sh3.Range(sh3.Cells(f.Row, "K"), sh3.Cells(f.Row, "R"))) Set f = r.FindNext(f) Loop While Not f Is Nothing And f.Address <> cell lr = sh4.Range("A" & Rows.Count).End(3).Row + 1 sh4.Range("A" & lr).Value = ky sh4.Range("B" & lr).Value = dic(ky) sh4.Range("C" & lr).Value = nSum End If Next End Sub

I understand that several things are missing, but I hope you can try this and move forward with whatever you need.

Good Morning Dear Dante Amor,

This coding you provided is very useful, but it is the only projected result are that has a common project in Sheet 2 and Sheet 3..

Can you please provide which is available project id's in sheet2 and sheet3..
previous code is perfectly working but the thing is common project only displayed. in this case I need all projects Id's .

Thank you for this in advance.

Thanks,
Rakesh Kamani

DanteAmor · Mar 2, 2020

Rakesh Kamani said:
Can you please provide which is available project id's in sheet2 and sheet3..
previous code is perfectly working but the thing is common project only displayed. in this case I need all projects Id's .

Which ones do you want:
Projects available on sheet2 that are not on sheet3?
Projects available on sheet3 that are not on sheet2?
And how to identify which records on sheet3 are projects?

Rakesh Kamani · Mar 3, 2020

DanteAmor said:
Which ones do you want:
Projects available on sheet2 that are not on sheet3?
Projects available on sheet3 that are not on sheet2?
And how to identify which records on sheet3 are projects?

Projects available on sheet2 that are not on sheet3? - Yes (example: D325897, M845214,)
Projects available on sheet3 that are not on sheet2? - Yes (example: D548521 A MM SYN MAC, M796548 D M SY MODE)
And how to identify which records on sheet3 are projects? (you can consider single project :- M327905 M DELL MOTHER, M327905 E DELL KEY, M327905)

Thank you Amor

DanteAmor · Mar 3, 2020

I mean, in the column you have these texts:
- Total
- M33801
- Automotive / private

How to know, of those texts what is project?

Rakesh Kamani · Mar 4, 2020

DanteAmor said:
View attachment 8140

I mean, in the column you have these texts:
- Total
- M33801
- Automotive / private

How to know, of those texts what is project?

We currently consider the M333801 WABCO Electrical Project ID. Please ignore the automotive / private and Total.

Put a comment for automotive / private if that is possible. There is no need for automotive / private values right now. if you can arrange this value please consider like project ID and put it comment, in feature its useful for me.

For your help, I thank you for Advance.

DanteAmor · Mar 4, 2020

visually I can know what a project is. but I mean if there is a pattern to recognize which texts are projects.

Rakesh Kamani · Mar 4, 2020

DanteAmor said:
visually I can know what a project is. but I mean if there is a pattern to recognize which texts are projects.

The project pattern is
sheet2 project ID like below
9000226
M312101
M309401
9150016
D001876
AV17045
P313720
S_V1910

Sheet3 project ID like below
M315904 PEP CMC
M327701 Tata industries wireless
M332801 M SY SYN Marc
M332801 D CMS port
M332701 KIA Dashboard
M332701 B KIA Rearwheel
P313720 D MM SYN Break
M312101 Mark Der

Sheet4 project ID display like below
9000226
M312101
M309401
9150016
D001876
AV17045
P313720
S_V1910
M315904
M327701
M332801
M332701

Can you set the value of output hours in an integer, the previous code planned value is displayed (3.4 + 2.2 = 5.6) in this case no decimal value is required. Can you give something like (3.4 + 2.2 = 6)?

Sum Column B based on Column A using Excel VBA Macro

Rakesh Kamani

New Member

DanteAmor

Well-known Member

Excel Facts

Rakesh Kamani

New Member

Rakesh Kamani

New Member

Rakesh Kamani

New Member

DanteAmor

Well-known Member

Rakesh Kamani

New Member

DanteAmor

Well-known Member

Rakesh Kamani

New Member

DanteAmor

Well-known Member

Rakesh Kamani

New Member

Similar threads

Forum statistics

Share this page

Sum Column B based on Column A using Excel VBA Macro

New Member

Well-known Member

Excel Facts

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

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