# Can someone help me to interpret data easier?



## BDexcel (Dec 15, 2022)

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 (Dec 15, 2022)

Hi

Does below pivot table help in what you want?

Book7ABCDEFGHIJKL1Cut NumberCur NameControlReview ApplicableWaiver in Place21Mr 1AYesNoRow LabelsCur NameControlCount of Waiver in Place32Mr 2BYesYesYesMr 2B143Mr 3DYesNoD154Mr 4CYesYesMr 4C265Mr 5GYesNoNoMr 1A174Mr 4CYesYesMr 3D184Mr 4CYesNoMr 4C192Mr 2DYesYesMr 5G110Grand Total8111213141516Sheet1


----------



## BDexcel (Dec 15, 2022)

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 (Dec 15, 2022)

BDexcel said:


> 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 (Dec 15, 2022)

What about something like having an input to where a user can type in customer # and then autopopulate a list of what they have?  
Book1ABCDEFGHIJK1Cut NumberCur NameControlReview ApplicableWaiver in PlaceCustomer InputCur NameControlReview ApplicableWaiver in Place21Mr 1AYesNo4Mr 4CYesYes32Mr 2BYesYesMr 4CYesYes43Mr 3DYesNoMr 4CYesNo54Mr 4CYesYes65Mr 5GYesNo74Mr 4CYesYes84Mr 4CYesNo92Mr 2DYesYesSheet1Cell FormulasRangeFormulaH2:K4H2=IFERROR(FILTER(B2:E9,A2:A9=G2),"")Dynamic array formulas.


----------



## keef2 (Dec 15, 2022)

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.

Book1ABCDEFGHIJK1Cut NumberCur NameControlReview ApplicableWaiver in PlaceCustomer #Cur NameControlReview ApplicableWaiver in Place21.00Mr 1AYesNo1Mr 1AYesNo32.00Mr 2BYesYes2Mr 2BYesYes43.00Mr 3DYesNo2Mr 2DYesYes54.00Mr 4CYesYes3Mr 3DYesNo65.00Mr 5GYesNo4Mr 4CYesYes74.00Mr 4CYesYes4Mr 4CYesYes84.00Mr 4CYesNo4Mr 4CYesNo92.00Mr 2DYesYes5Mr 5GYesNoSheet1Cell FormulasRangeFormulaG2:K9G2=SORT(A2:E9,1,1)Dynamic array formulas.


----------



## BDexcel (Dec 16, 2022)

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 (Dec 16, 2022)

BDexcel said:


> 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 (Dec 16, 2022)

Sufiyan97 said:


> 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 (Dec 16, 2022)

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

Book3ABCDEFGHIJKL1Cut NumberCur NameControlReview ApplicableWaiver in PlaceCustomer InputCur NameControlReview ApplicableWaiver in Place21Mr 1AYesNo4Mr 4CYesYes32Mr 2BYesYesMr 4CYesYes43Mr 3DYesNoMr 4CYesNo54Mr 4CYesYesMr 4CYesNo65Mr 5GYesNoMr 4CYesNo74Mr 4CYesYes84Mr 4CYesNo92Mr 2DYesYes104Mr 4CYesNo114Mr 4CYesNo121314Sheet2Cell FormulasRangeFormulaH2:K6H2=IFERROR(FILTER(B2:E11,A2:A11=G2),"")Dynamic array formulas.


----------



## BDexcel (Dec 15, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

BDexcel said:


> 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 (Dec 16, 2022)

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

Book1ABCDEFGHIJK1Cut NumberCur NameControlReview ApplicableWaiver in PlaceCustomer InputCur NameControlReview ApplicableWaiver in Place21Mr 1AYesNo4Mr 4CYesYes32Mr 2BYesYesMr 4CYesYes43Mr 3DYesNoMr 4CYesNo54Mr 4CYesYesMr 4CYesNo65Mr 5GYesNoMr 4DYesYes74Mr 4CYesYesMr 4AYesYes84Mr 4CYesNoMr 4CYesNo92Mr 2DYesYesMr 4CYesNo104Mr 4CYesNo114Mr 4DYesYes124Mr 4AYesYes134Mr 4CYesNo144Mr 4CYesNoSheet1Cell FormulasRangeFormulaH2:K9H2=IFERROR(FILTER(B2:E14,A2:A14=G2),"")Dynamic array formulas.


----------

