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!

 

[Sufiyan97]

Hi

Does below pivot table help in what you want?

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1	Cut Number	Cur Name	Control	Review Applicable	Waiver in Place
2	1	Mr 1	A	Yes	No		Row Labels	Cur Name	Control	Count of Waiver in Place
3	2	Mr 2	B	Yes	Yes		Yes	Mr 2	B	1
4	3	Mr 3	D	Yes	No				D	1
5	4	Mr 4	C	Yes	Yes			Mr 4	C	2
6	5	Mr 5	G	Yes	No		No	Mr 1	A	1
7	4	Mr 4	C	Yes	Yes			Mr 3	D	1
8	4	Mr 4	C	Yes	No			Mr 4	C	1
9	2	Mr 2	D	Yes	Yes			Mr 5	G	1
10							Grand Total			8
11
12
13
14
15
16
Sheet1

 

[BDexcel]

Hi Sufiyan, thank you for trying but due to the volume of data it is probably not too easy to read like this.

I was thinking maybe in this situation slicers could benefit this task.

 

[Sufiyan97]

I was thinking maybe in this situation slicers could benefit this task.

yes that is or may be someone else may come up with better solution.

 

[keef2]

What about something like having an input to where a user can type in customer # and then autopopulate a list of what they have?

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
6	5	Mr 5	G	Yes	No
7	4	Mr 4	C	Yes	Yes
8	4	Mr 4	C	Yes	No
9	2	Mr 2	D	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
H2:K4	H2	=IFERROR(FILTER(B2:E9,A2:A9=G2),"")
Dynamic array formulas.

 

[keef2]

Or you could use sort function? Not sure the version of excel you are using but you should update your settings to put that in there as it is pretty important.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Cut Number	Cur Name	Control	Review Applicable	Waiver in Place		Customer #	Cur Name	Control	Review Applicable	Waiver in Place
2	1.00	Mr 1	A	Yes	No		1	Mr 1	A	Yes	No
3	2.00	Mr 2	B	Yes	Yes		2	Mr 2	B	Yes	Yes
4	3.00	Mr 3	D	Yes	No		2	Mr 2	D	Yes	Yes
5	4.00	Mr 4	C	Yes	Yes		3	Mr 3	D	Yes	No
6	5.00	Mr 5	G	Yes	No		4	Mr 4	C	Yes	Yes
7	4.00	Mr 4	C	Yes	Yes		4	Mr 4	C	Yes	Yes
8	4.00	Mr 4	C	Yes	No		4	Mr 4	C	Yes	No
9	2.00	Mr 2	D	Yes	Yes		5	Mr 5	G	Yes	No
Sheet1

Cell Formulas
Range	Formula
G2:K9	G2	=SORT(A2:E9,1,1)
Dynamic array formulas.

 

[BDexcel]

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

 

[Sufiyan97]

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

just increase range B2:E9, to cover all your data.

 

[BDexcel]

just increase range B2:E9, to cover all your data.

yeah I can do that but the results table only shows 3 lines in the area H2:K4, I need it to show more than 3 if possible

 

[Sufiyan97]

For me it's showing more than 3, can you compare your data with this

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
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	C	Yes	No
7	4	Mr 4	C	Yes	Yes
8	4	Mr 4	C	Yes	No
9	2	Mr 2	D	Yes	Yes
10	4	Mr 4	C	Yes	No
11	4	Mr 4	C	Yes	No
12
13
14
Sheet2

Cell Formulas
Range	Formula
H2:K6	H2	=IFERROR(FILTER(B2:E11,A2:A11=G2),"")
Dynamic array formulas.