Populating Data

[judge]

Hi,

I am hoping someone might be able to help.

I have an Excel spreadsheet that gets populated with data from an external source. This data is contained on a single sheet called “Data”. Using other sheets I have created statistics (all in the same spreadsheet), so for example, I have sheet 1 (data), sheet 2 (statistics) and sheet 3 (client reports).

However, in my data I have clients with many requirements, so for example, client A wants 10 cars and 2 vans, client B wants 3 cars and client C wants 6 cars and 2 bikes. So on my client report, I want to be able to list all the clients from sheet 1 (data) and only display my client name once (unique value), then next to each client I will show the total vehicles they require (by using COUNTIF).

How can I do this, any ideas?

Thanks

[Dav]

how is the data structured

Client Cars Bikes Vans as colums?

or
Client Vehicle number as columns

And do you know who the clients are or is the list growing so needs to be populated each month


Regards

Dav

[judge]

Example - Date Sheet 1

Client Name <> Area <> Vehicle

Microsoft <> Surrey <> Car
Microsoft <> Surrey <> Bike
IBM <> Hampshire <> Car
IBM <> Hampshire <> Car
IBM <> Hampshire <> Bike
Sun <> Midlands <> Car
Sun <> Midlands <> Van

So in sheet 3 (client reports) I want it to look like this:

Client Name <> Total

Microsoft <> 2
IBM <> 3
Sun <> 2

and yes the list in sheet one (client name) will grow.

- If microsoft asked to have another van then the total would be auto updated to 3.
- If a new customer comes on board called HP, then sheet 3 would auto update with the new client (and total).

Many thanks for looking.

[Dav]

Given the List will grow the easy way would be just to have a pivot report of

columnA (cilent) as a row and count of columnC (vehicle) as the value. then just refresh the table when you need to run the report


data_pivot table report is where you need to look

regards

Dav

[Avner]

Will Pivot Table With Counting help ?