Best Approach to Summarise Data

[averilp]

Hi,

I have a couple of data sets that I need to create an overview for and I’m struggling how to group, count, sum etc. various data sets. Essentially, I have different data sets for:

Revenue Data
Sales & Pipeline Data
Consultant Engagement Data

What I have been racking my brain over is the best and easiest way to summarise this information in the format required. The summary sheet just needs to have basic information on all the key accounts and this has been a time consuming mostly manual process (parts that weren’t manual were nested volatile formulas) which I thought I’d have a crack at fixing up

For each client I need to show among other things:
1. No of Engaged Consultants
2. Pipeline (number of open opportunities in this financial year)
3. Pipeline (value of open opportunities in this financial year)
4. Sales previous financial year
5. Sales YTD (current FY)
6. Revenue previous financial year
7. Revenue YTD (current FY)

With regards to the Engaged Consultants column…
I’ve been trying to write a custom function that should return a value (count) of the number of unique individuals whose names come up against each of the clients including sub clients from the consultant report. This uses the same Client Name as the Sales data.

I’ve been bashing my head against the wall trying to do this and it has honestly turned into a monster of loops and arrays etc. when I don’t think it needs to be, and now I think I’ve made a big mess of it because it doesn't work!

My thinking was that from the summary sheet we need to:
1. Lookup/find each possible “sub client” name in the Mapping sheet
2. Lookup/find each of these sub client names in the Consultant Data
3. Count each unique consultant name (must be unique, because what we want is a snapshot of the number of consultants we have on site) out of the consultant data. This data will have multiple entries per consultant per client if they are actively working on more than one project on client site.

I then need to basically do the same for Sales and Revenue data. I just really need a starting point then I should be able to adapt the ConsultantCount code to the Pipeline, Sales, Revenue etc.

Background
I have extracted the Revenue Data via VBA from the format it comes in (Grouped, Outlines, Merged Cells and other yucky stuff) so now I get a dump of data that has the following fields:

YEAR
PERIOD
PPR CLIENT NAME
SIMPLIFIED CLIENT NAME
PROJECT
PRACTICE
VALUE TYPE
VALUE

I have had to put in the SIMPLIFIED CLIENT NAME for a couple of reasons:

1) Because I am dealing with historic information as well as moving forward, there are quite a lot of changes to things like Client Name when the billing entity name might have changed, been misspelled etc. e.g. NSW Roads and Traffic Authority is now NSW Roads and Maritime Services
2) Some sub-clients need to be rolled up into this “simplified” or parent account. E.g. for the summary sheet I am trying to create, we only want one entry for say “Telstra” and not broken down into the various subsidiaries we’ve done work for

The Sales/Pipeline information uses a different client name again so I created a MAPPING sheet that lists all the iterations of the Revenue client name the Simplified client name and the Sales client name. e.g.

SIMPLIFIED NAME | SALESFORCE NAME | PPR NAME
Telstra | Telstra Corporation Ltd | TELS001 - Telstra (Main Account)
Telstra | Telstra Corporation Ltd | TELS003 - Telstra - CSA Agreement
Telstra | Telstra Corporation Ltd | TELS002 - Telstra - PSA Agreement
Telstra | Telstra Corporation Ltd | TELS001 - Telstra (Main Account)
Telstra | Telstra Bigpond | BIGP001 - Telstra Bigpond
Telstra | Telstra - PSA Panel | TELS002 - Telstra - PSA Panel

 

[Trevor G]

Have you looked at Pivot Tables, which version of Excela are you using?

 

[averilp]

Thanks for your response.

Yes, I use Pivot Tables for summarising info from the individual data sets but I'm trying to pull together 3 quite different data sets that really only have one field in common (and that is subject to change so hence the need for the MAPPING sheet).

I'm about to start from scratch again because I think I've been working too closely with it and therefore can't see the wood for the trees so to speak.

 

[averilp]

OK, so I've started from scratch but it's not really working - especially the second part which I need to get a count of the number of unique consultants where the client name matches any of matching ones from the mapping sheet.

I'm using Excel 2010

Any help is much appreciated. I realise that the below is probably a very clunky way of getting there but it's all I could come up with.

Function GetConsultantCount(strAccount As String) As Integer
'Dim iConsultantCount As Integer
Dim arClientMatches()     'array for matching clients
Dim iClientArr As Integer 'array counter
Dim c   'reference for looping through cells in MAPPING sheet
Dim cLoop      'reference for looping through cells in CONSULTANT sheet
Dim i   'reference for looping through cells in CONSULTANT sheet
Dim iloop      'reference for looping through cells in CONSULTANT sheet
Dim arConsultantMatches()
Dim iConsultantArr As Integer
Dim bFound As Boolean

iClientArr = 0

'Firstly, find each instance of the Simplified Client name
'that matches the input and add to an array
With Sheets("MAPPING")
For Each c In .Range("A:A")
    If c = strAccount Then
        For cLoop = 0 To iClientArr
            ReDim Preserve arClientMatches(iClientArr)
            If c.Offset(0, 1).Value <> arClientMatches(cLoop) Then
                iClientArr = iClientArr + 1
                ReDim Preserve arClientMatches(iClientArr)
                arClientMatches(iClientArr) = c.Offset(0, 1).Value
            End If
        Next cLoop
    End If
Next c
End With

'Now,create a new array for the consultants associated with this client
'find each instance of the Simplified Client name from the arClientMatches array
'and then look for the consultant name associated with that row and IF the consultant name
'ISN'T already somewhere in the array, add the consultant to a new array
'
'THIS IS THE BIT THAT'S NOT WORKING

Erase arConsultantMatches
ReDim Preserve arConsultantMatches(iConsultantArr)
iConsultantArr = 0
iloop = 0

With Sheets("ConsultantDATA")
For i = 1 To iClientArr
    For Each c In .Range("P:P")
        If c = arClientMatches(iClientArr) Then
            'ReDim Preserve arConsultantMatches(iConsultantArr)
            For iloop = 0 To iConsultantArr
                ReDim Preserve arConsultantMatches(iConsultantArr)
                If c.Offset(0, 2).Value <> arConsultantMatches(iloop) Then
                    iConsultantArr = iConsultantArr + 1
                    ReDim Preserve arConsultantMatches(iConsultantArr)
                    arConsultantMatches(iConsultantArr) = c.Offset(0, 2).Value
                End If
            Next iloop
        End If
    Next c
Next i
End With

GetConsultantCount = iConsultantArr
End Function

 

[averilp]

I now have this working (after a fashion) but of course it takes a VERY long time to work with all the loops etc even just for summarising 65 Clients... if anyone can come up with a way of speeding this up, that would be fab!

Consultant Data sheet has about 220 rows of data in it at the moment and the Mapping sheet has just under 200 as well and the below can take a couple of minutes to calculate which isn't ideal because the other functions that I need to write also (that I was going to base on the below code) are driven from sheets that have 95,000 rows and 5,000 rows.

Function GetConsultantCount(strAccount As String) As Integer
Dim arClientMatches()     'array for matching clients
Dim iClientArr As Integer 'array counter
Dim c   'reference for looping through cells in MAPPING sheet
Dim cLoop      'reference for looping through cells in CONSULTANT sheet
Dim i   'reference for looping through cells in CONSULTANT sheet
Dim iloop      'reference for looping through cells in CONSULTANT sheet
Dim arConsultantMatches()
Dim iConsultantArr As Integer
Dim iFound As Integer

iClientArr = 0
cLoop = 0

'Firstly, find each instance of the Simplified Client name
'that matches the input and add to an array
With Sheets("MAPPING")
For Each c In .Range("A:A")
    If c = strAccount Then
        ReDim Preserve arClientMatches(iClientArr)
        For cLoop = 0 To UBound(arClientMatches)
            iFound = 0
                If c.Offset(0, 1).Value = arClientMatches(cLoop) Then
                    iFound = iFound + 1
                End If
        Next cLoop
        If iFound = 0 Then
            iClientArr = iClientArr + 1
            ReDim Preserve arClientMatches(iClientArr)
            arClientMatches(iClientArr) = c.Offset(0, 1).Value
        End If

    End If
Next c
End With

'Now,create a new array for the consultants associated with this client
'find each instance of the Simplified Client name from the arClientMatches array
'and then look for the consultant name associated with that row and IF the consultant name
'ISN'T already in the array, add the consultant to a new array
Erase arConsultantMatches
iConsultantArr = 0
ReDim Preserve arConsultantMatches(iConsultantArr)
iloop = 0

With Sheets("ConsultantDATA")
For i = 1 To iClientArr
    For Each c In .Range("P:P")
    'If c Like "*Telstra*" Then Stop
    
        If c = arClientMatches(i) Then
            iFound = 0
            ReDim Preserve arConsultantMatches(iConsultantArr)
            For iloop = 0 To UBound(arConsultantMatches)
                If c.Offset(0, 2).Value = arConsultantMatches(iloop) Then
                    iFound = iFound + 1
                End If
            Next iloop
            
            If iFound = 0 Then
                iConsultantArr = iConsultantArr + 1
                ReDim Preserve arConsultantMatches(iConsultantArr)
                arConsultantMatches(iConsultantArr) = c.Offset(0, 2).Value
                iFound = 0
            End If
        End If
    Next c
Next i
End With

GetConsultantCount = iConsultantArr
End Function