Need macro to look for person's name and specific date, then count values in an array

R

Reiper79

New Member
Joined
Jun 15, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

Can someone please help me out here?

Say I have an excel sheet that looks like this:

TrainerSiteVOC 1VOC 2VOC 3VOC 4VOC 5VOC 6VOC 7VOC 8VOC 9VOC 10VOC 11
Name 1Site 115/09/202115/09/2021
Name 1Site 114/09/202117/08/2021
Name 1Site 1114/09/2021
Name 1Site 620/09/2021
Name 10Site 1009/09/202107/09/2021
Name 10Site 1526/08/2021
Name 10Site 503/08/202103/08/2021
Name 2Site 1227/09/2021
Name 2Site 224/09/202124/09/202124/09/202124/09/202124/09/202124/09/2021
Name 2Site 217/08/2021
Name 2Site 726/08/2021
Name 3Site 1328/08/2021
Name 3Site 327/08/2021
Name 3Site 327/08/2021
Name 3Site 811/08/202111/08/202111/08/202111/08/202111/08/2021
Name 4Site 1419/08/2021
Name 4Site 421/09/202121/09/2021
Name 4Site 420/08/202120/08/202120/08/202120/08/202120/08/2021
Name 4Site 903/09/2021
Name 5Site 1020/09/2021
Name 5Site 1504/08/2021
Name 5Site 511/08/202111/08/202111/08/202111/08/202111/08/2021
Name 5Site 527/08/202127/08/202127/08/2021
Name 6Site 126/08/2021
Name 6Site 1121/09/2021
Name 6Site 619/08/202119/08/202119/08/202119/08/2021
Name 6Site 627/08/202127/08/202127/08/202127/08/2021
Name 7Site 1226/08/2021
Name 7Site 224/08/202124/08/202124/08/202124/08/202124/08/2021
Name 7Site 731/08/2021
Name 7Site 720/09/2021
Name 8Site 1319/08/2021
Name 8Site 325/08/2021
Name 8Site 822/09/2021
Name 8Site 821/09/202121/09/202121/09/2021
Name 9Site 1409/09/2021
Name 9Site 414/09/2021
Name 9Site 927/08/2021
Name 9Site 912/08/2021

I need a macro that can look through the above table, count the number of VOCs performed per trainer per date, then add a row and paste the details in another sheet that looks similar to the below:

DateTrainerCategory 1 (VOC 1)Category 2 (VOC 2)Category 3 (VOC 3)Category 4 (VOC 4+5+6)Category 5 (VOC 7)Category 6 (VOC 8+9+10)Category 7 (VOC 11)
15/09/2021Name 11100000
14/09/2021Name 10020000
20/09/2021Name 11000000
17/08/2021Name 10000010
09/09/2021Name 100100100
07/09/2021Name 100001000
26/08/2021Name 101000000
03/08/2021Name 100000011
09/09/2021Name 90100000
14/09/2021Name 91000000

Is that possible?
 
Sort by date Sort by votes
Zot said:
Once the date is not read correctly, the count would be incorrect.

Working with date sometimes can be troublesome for me. When it is capture as text, then even if they look the same it will not match either. VBA also treat date in US format. So, working with British format can also be tricky. That is why converting into date serial is better before doing anything. I don't see any problem with modification since it is very straightforward.

The best is that you can use the XL2BB tool (the icon on the right most). It is an addin that you use to select range and capture to paste to reply. This way helpers will get actual sheet with even formula intact. When pasting the capture, it looks like junk. Click Preview to see actual output and click preview again to continue writing.

Or, can you just copy part of your original data that cause the problem?
Click to expand...
This is Sheet 1 (Matrix):

Book1.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Base SiteDriverL@T UsernameTrainerInduction / OnboardingForkliftYard Truck / TugLight RigidMedium RigidHeavy RigidHeavy Combination Multi CombinationReversing - StraightReversing - OffsetSPOTPrime Mover to TrailerA-Trailer to B-TrailerRingfederDolly to TrailerRoad Train AssemblyLoad Restraint
2Perth Airport, WADriver 1NilTrainer 715/09/2021
3Hazlemere, WADriver 10NilTrainer 222/09/2021
4Villawood, NSWDriver 100NilTrainer 626/08/2021
5Mackay, QLDDriver 101NilTrainer 922/09/202122/09/202122/09/202122/09/202122/09/2021
6Mackay, QLDDriver 102NilTrainer 909/09/2021
7Kewdale, WADriver 103NilTrainer 731/08/2021
8Eastern Creek, NSWDriver 104NilTrainer 627/08/2021
9Melbourne Airport, VICDriver 105NilTrainer 302/09/2021
10Eagle Farm, QLDDriver 106NilTrainer 829/09/2021
11Perth Airport, WADriver 107NilTrainer 707/09/2021
12Bungarribee, NSWDriver 108NilTrainer 526/08/2021
13Townsville, QLDDriver 109NilTrainer 901/09/2021
14Eastern Creek, NSWDriver 11NilTrainer 627/08/2021
15Bungarribee, NSWDriver 110NilTrainer 517/08/2021
16Eastern Creek, NSWDriver 111NilTrainer 626/08/2021
17Villawood, NSWDriver 112NilTrainer 625/08/2021
18Hazlemere, WADriver 113NilTrainer 223/09/2021
19Eastern Creek, NSWDriver 114NilTrainer 624/09/2021
20Townsville, QLDDriver 115NilTrainer 903/09/2021
21Townsville, QLDDriver 116NilTrainer 902/09/2021
Matrix


This is Sheet 2 (Count by Date):

Book1.xlsx
ABCDEFGHI
1DateTrainerInduction / OnboardingForkliftYard Truck / TugDrivingSPOTCoupling / UncouplingLoad Restraint
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Count by Date


Thanks.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sorry, I forgot to mention, if it helps, my default language is set to English (Australia).
 
Upvote 0
Reiper79 said:
Sorry, I forgot to mention, if it helps, my default language is set to English (Australia).
Click to expand...
So, sorry. I bookmarked this but forget about it. My bad. :(

Your original sample and the actual worksheet are totally different for code to work. If you still need this. I'll try yo look at it tomorrow morning
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
96
Peter_SSs
Peter_SSs
BradleyS
  • Solved
Convert Age to Number format not working
Replies
7
Views
99
BradleyS
BradleyS
M
  • Solved
Formula Help.
Replies
3
Views
435
Fluff
Fluff
L
  • Solved
EXCEL FORMULA TO COUNT
Replies
6
Views
299
LAY2022
L
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
277
RoryA
RoryA

Forum statistics

Threads
1,223,727
Messages
6,174,144
Members
452,547
Latest member
Schilling

Share this page

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