Can someone help me to interpret data easier?

[BDexcel]

Hi everyone,

Just wondered if someone could be kind enough to help me interpret a spreadsheet of data I have please?

Basically I have a tab of customers which they may or may not have controls in place and I need to understand how I find out how many controls each customer has and if there are any waivers in place for these.

I have gave a small example below and have tried to create a pivot table etc but just cannot get it into an easy format for me to get the data I require easily. Please note the real spreadsheet I am working on has hundreds of lines hence why manual filtering etc would take too long.

For example:

The results I want to understand are how many of each control does each customer have and what wavers are there....

I need a way or table etc, to present the following information to me if possible?

Customer Mr 1 has 1 control A and no waiver in place
Customer Mr 2 has 2 controls B and D and both have waivers in place
Customer Mr 3 has 1 control D in place with no waiver
Customer Mr 4 has 3 control C's in plave and 2 have waivers
Customer Mr 5 has 1 control G with no waiver

Any help would be appreciated!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[keef2]

Hi Keef, thank you for the replies.

Can I ask in the first example you gave how do I increase the range of the table in H2:K4? So I can allow for more than 3 results?

Appreciate your help

So the filter function spills matching data only. depending on the range of your selection only the items with the customer # you input will populate automatically. So the range is automatically set depending on your input. Hope this makes sense.

 

[keef2]

Here is a slight modification to make the range of the filter function larger and some inputted data. Not sure what else you mean???

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Cut Number	Cur Name	Control	Review Applicable	Waiver in Place		Customer Input	Cur Name	Control	Review Applicable	Waiver in Place
2	1	Mr 1	A	Yes	No		4	Mr 4	C	Yes	Yes
3	2	Mr 2	B	Yes	Yes			Mr 4	C	Yes	Yes
4	3	Mr 3	D	Yes	No			Mr 4	C	Yes	No
5	4	Mr 4	C	Yes	Yes			Mr 4	C	Yes	No
6	5	Mr 5	G	Yes	No			Mr 4	D	Yes	Yes
7	4	Mr 4	C	Yes	Yes			Mr 4	A	Yes	Yes
8	4	Mr 4	C	Yes	No			Mr 4	C	Yes	No
9	2	Mr 2	D	Yes	Yes			Mr 4	C	Yes	No
10	4	Mr 4	C	Yes	No
11	4	Mr 4	D	Yes	Yes
12	4	Mr 4	A	Yes	Yes
13	4	Mr 4	C	Yes	No
14	4	Mr 4	C	Yes	No
Sheet1

Cell Formulas
Range	Formula
H2:K9	H2	=IFERROR(FILTER(B2:E14,A2:A14=G2),"")
Dynamic array formulas.