EXCEL DATA ANALYSIS

Question

Hi community,

I have a question on using the data analysis function in excel to help me solve a question.

Column A contains driver details , whereas column B contains the region they are driving in. However, some drivers drive in more than 1 region and there are some duplicates in the same region they are driving in. For e.g.,

Driver details Region
1111 West
1111 West
1111 North
1112 South
1113 East
1114 West
1114 West
1114 North

There are numerous drivers and the data is impossible to be counted physically. How can I use the excel functions to calculate statistics like how many unique drivers serve more than 1 region, or another issue like how many unique drivers drive exactly within 3 regions?

Thank you!

Answer 1

Hi,

Just in case you want a formula based solution, try this

In cell D2, enter this formula

=UNIQUE(A2:A9)

In cell E2, enter this formula and drag down

=COUNTA(UNIQUE(FILTER($B$2:$B$9,$A$2:$A$9=D2)))

Hope this helps.

Answer 2

Use Pivot tables to get the answer to your questions:

a)

b)

Same Pivot table, filtered "Count of Region" equals 3

You can also use Slicers to quickly filter Pivot tables:

Use slicers to filter data - Office Support

Andreas.