Is it possible ?

[russlock]

good evening all. I wonder if it is possible. I have a customer complaint sheet with a list of complaint reasons. I then fill in complaint with date, reason and various other info.
at the top of the sheet I have the months of the year and I want to populate how many times during a given month that a reason occurs.

i.e
14/2/18, late delivery, joe blogs engineering.
20/2/18, wrong item, betty café

so, in the column for months along the top and all the reasons down the left, it would now show number 1 for both "late delivery" and "wrong item", and these would increment the more is added and them populate the March month once a date is entered in march.

long shot, I know; but thanks.

 

[Johnny Thunder]

Are the date, "Late Delivery" and Company Info all in separate columns? Also, if the data is in fact all in separate columns are there any blanks as the data flows downward.

 

[MartyS]

good evening all. I wonder if it is possible. I have a customer complaint sheet with a list of complaint reasons. I then fill in complaint with date, reason and various other info.
at the top of the sheet I have the months of the year and I want to populate how many times during a given month that a reason occurs.

i.e
14/2/18, late delivery, joe blogs engineering.
20/2/18, wrong item, betty café

so, in the column for months along the top and all the reasons down the left, it would now show number 1 for both "late delivery" and "wrong item", and these would increment the more is added and them populate the March month once a date is entered in march.

long shot, I know; but thanks.

Do you mean something like this?




This Data

Excel 2010

	A	B	C
Explosion	Saturns Rings
Dog Ate Postman	Joe Blogs Engineering
Explosion	Europa
Late Delivery	Saturns Rings
Dog Ate Postman	Joe Blogs Engineering
Explosion	Europa
Dog Ate Postman	Europa
Order Lost	Betty Café
Order Lost	Moon
Order Lost	Joe Blogs Engineering
Wrong Item	Joe Blogs Engineering
Wrong Planet	Mars Station
Late Delivery	Betty Café

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Date[/TD]
[TD="bgcolor: #D9D9D9"]Reason[/TD]
[TD="bgcolor: #D9D9D9"]Location[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]22/01/2017[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]17/10/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]20/09/2017[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]01/05/2017[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]09/03/2017[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]24/08/2017[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]21/06/2017[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]08/09/2017[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]01/05/2017[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]11/05/2017[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]25/08/2017[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]14/05/2017[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]03/04/2017[/TD]

</tbody>

​

Presented Like This

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Dog Ate Postman
Explosion
Late Delivery
Order Lost
Wrong Item
Wrong Planet
Grand Total

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Count of Reason[/TD]
[TD="bgcolor: #D9D9D9"]Mth[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #D9D9D9"]Row Labels[/TD]
[TD="bgcolor: #D9D9D9"]Jan[/TD]
[TD="bgcolor: #D9D9D9"]Feb[/TD]
[TD="bgcolor: #D9D9D9"]Mar[/TD]
[TD="bgcolor: #D9D9D9"]Apr[/TD]
[TD="bgcolor: #D9D9D9"]May[/TD]
[TD="bgcolor: #D9D9D9"]Jun[/TD]
[TD="bgcolor: #D9D9D9"]Jul[/TD]
[TD="bgcolor: #D9D9D9"]Aug[/TD]
[TD="bgcolor: #D9D9D9"]Sep[/TD]
[TD="bgcolor: #D9D9D9"]Oct[/TD]
[TD="bgcolor: #D9D9D9"]Nov[/TD]
[TD="bgcolor: #D9D9D9"]Dec[/TD]
[TD="bgcolor: #D9D9D9"]Grand Total[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]29[/TD]

</tbody>

​

If so, you can use a Pivot Table to get these results.

Just highlight the data in the first table (This Data)

Go to the INSERT ribbon

Select PIVOT TABLE

The range will be populated because you've already highlighted it.

Select New Worksheet

Click OK.


A new worksheet will be created.


On the right hand side should be a new pane where you can:

from the top area (labelled "Choose fields to add to report")

Drag DATE to Column Labels

Drag REASON to Row Labels

Drag REASON to Values




Make sure that REASON that you've just dragged into the VALUES area says [COUNT of REASON]



Finally, click on the first date that appears in the column headings so only that cell is selected.


From the PIVOT TABLE : OPTIONS ribbon, select GROUP SELECTION


Fill in the Starting At and Ending At dates.

Select how you want the data grouped (Months)

Click OK



Hope that helps?!

 

[russlock]

Absolutely perfect, works spot on and cant thank you enough MartyS & Johnny Thunder

 

[MartyS]

Absolutely perfect, works spot on and cant thank you enough MartyS & Johnny Thunder

Excellent!!