HELP - Unique Clients / Sessions report in Excel

[p666james]

Hi All,

I have a excel sheet of Raw Data, which shows the different sessions that have been delivered to various clients.

Each row contains the following information (There are additional fields too):-

Unique Client Reference Number;
Client Name;
Service Type (Clients can access up to 3 service types)
Nature (Type of session a client has had, up to 4 options)
Session Number (Client can access up to 4 sessions per Service Type)
Delivering Contractor (Up to 11 Contractors who could have delivered the service)
Area (We work in 10 Areas)

I can upload an anonymised version of the report if required.

From the report, I need to be able to show by Delivering Contractor and Area, how many unique clients have accessed the following:
* Had more than one Session
* Had more than one service type (And Display the Service Type)
* Total number of Unique Clients
* Total number of Unique Clients accessing only one service.

I can work out this information manually, however it takes days given the amount of data that we work with, and I would like to know if anyone knows of a VBA Script that would be able to work out such a query?

Thanks in Advance

Paul

 

[sanrv1f]

Have you tried pivot tables?

if that wont help, post some sample data and expected results as table or delimited text (not an image), see my signature for help

 

[p666james]

Hi,

Thanks for your quick reply.

I have uploaded the file in Excel and CVS Format for you:-

https://sites.google.com/site/paulfilesite/files/AnnonData.csv
&
https://sites.google.com/site/paulfilesite/files/AnnonData.xls

I basically need a page in the excel sheet which displays the following information:-

Number of Unique Clients: XYZ
Number of Clients who accessed more than one session type: XYZ

I would then need a table which shows the service type on the row header, and column header, and then shows the number of clients who have accessed more that one Service.

Universal D&P Universal Plus
Universal 100 20 20
D&P 20 200 50
Universal Plus 20 50 100

I then need to show the number of clients who had 2, 3 or 4 sessions, by the service type. So this would need to be displayed as Universal session 1: 40, Universal Session 2: 30, Universal Session 3: 20 etc.

Thanks

Paul

 

[p666james]

Hi (again)

And yes, I have tried Pivot Tables - however because the report is based on the client, these reports often go off the page and are of no use.

Thanks