HELP - Unique Clients / Sessions report in Excel

p666james

New Member
Joined
May 12, 2005
Messages
6
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 12, 2005
Messages
6
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

New Member
Joined
May 12, 2005
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

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
Top