vba code / macro required to merge and add up data

bryanrobson

New Member
Joined
Aug 19, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
The attached file is just a sample and the original has much more data but I have deleted rows out of it and also changed some of the data as it is sensitive information.

I need the code to use the start month of the billing period and the end month of the billing period by using the Start sheet and selecting the month from the drop down lists in columns E and G

For some sites there may be more than 1 row. For example, row 35 and 36 and there are others. This is because while we are at the site, we have done 2 jobs or more.

No Visits in column F is the number of visits to that site booked (not necessarily completed yet). But, if a site has more than 1 row for example row 35 and 36, then the number of visits is 3 not 3 + 3.

Then the 1 indicated in cells January to December represent when the work has been booked in and green if it has been completed. Sometimes the work may not get done as the engineer couldnt go, it was cancelled or the engineer arrived on site and couldnt do the work. So these will not be green.
The cells also have comments stating the date when the work was done.
e.g. Clean done 20/04/2022

What I need is to consolidate the data explained above. I need the same column headings on an output sheet. But, firstly, one line only for each site. The value of the number of site visits are not to be added together (just use the number in that cell). Then add up the numbers in January to December only if the cell is green and only if those months fall within the start month and end month selected in the drop down on the Start sheet. Also include the inserted comment eg Clean done 20/04/2022 in a cell.

I have included a sheet called Sample Output. So, basically if the period to be billed is June, July and August then this would be the expected results.

Many thanks in advance

test.xlsm
ABCDEFGHIJKLMNOPQR
1Company Report
2Site Visits
3
4
5Head OfficeSite NoSite NameSite AddressEngineers NameNo VisitsJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
6Head Office A956Site ASite Address Site AEngineer A3111
7Head Office A956Site ASite Address Site AEngineer A3111
8Head Office A956Site ASite Address Site AEngineer A3111
9Head Office A956Site ASite Address Site AEngineer A3111
10Head Office A1082Site BSite Address Site BEngineer F3111
11Head Office A1408Site CSite Address Site CEngineer B3111
12Head Office A1453Site DSite Address Site DEngineer P3111
13Head Office A1453Site DSite Address Site DEngineer P3111
14Head Office A1464Site ESite Address Site EEngineer H3111
15Head Office A1466Site FSite Address Site FEngineer L3111
16Head Office A1489Site GSite Address Site GEngineer T3111
17Head Office A1490Site HSite Address Site HEngineer O3111
18Head Office A1782Site ISite Address Site IEngineer S3111
19Head Office A1866Site JSite Address Site JEngineer E3111
20Head Office A1978Site KSite Address Site KEngineer K3111
21Head Office A1981Site LSite Address Site LEngineer V3111
22Head Office A2016Site MSite Address Site MEngineer L3111
23Head Office A3639Site NSite Address Site NEngineer F3111
24Head Office A3769Site OSite Address Site OEngineer L3111
25Head Office A4453Site PSite Address Site PEngineer C3111
26Head Office A4998Site QSite Address Site QEngineer S3111
27Head Office A9662Site RSite Address Site REngineer R3111
28Head Office B249Site SSite Address Site SEngineer D3111
29Head Office B347Site TSite Address Site TEngineer S3111
30Head Office B494Site USite Address Site UEngineer K3111
31Head Office B612Site VSite Address Site VEngineer D3111
32Head Office B820Site WSite Address Site WEngineer K3111
33Head Office B822Site XSite Address Site XEngineer F3111
34Head Office B824Site YSite Address Site YEngineer W3111
35Head Office B832Site ZSite Address Site ZEngineer M3111
36Head Office B845Site AASite Address Site AAEngineer L3111
37Head Office B852Site ABSite Address Site ABEngineer S3111
38Head Office B867Site ACSite Address Site ACEngineer U3111
39Head Office B867Site ACSite Address Site ACEngineer U3111
40Head Office B892Site ADSite Address Site ADEngineer P3111
41Head Office B988Site AESite Address Site AEEngineer G3111
42Head Office B1114Site AFSite Address Site AFEngineer T3111
43Head Office B1130Site AGSite Address Site AGEngineer E3111
44Head Office B1227Site AHSite Address Site AHEngineer M3111
45Head Office B1735Site AISite Address Site AIEngineer T3111
46Head Office B1765Site AJSite Address Site AJEngineer S3111
47Head Office B1765Site AJSite Address Site AJEngineer S3111
48Head Office B2000Site AKSite Address Site AKEngineer F3111
49Head Office B2081Site ALSite Address Site ALEngineer A3111
50Head Office B2097Site AMSite Address Site AMEngineer T3111
Data
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top