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
1	Date	Reason	Location
2	22/01/2017	Explosion	Saturns Rings
3	17/10/2017	Dog Ate Postman	Joe Blogs Engineering
4	20/09/2017	Explosion	Europa
5	01/05/2017	Late Delivery	Saturns Rings
6	09/03/2017	Dog Ate Postman	Joe Blogs Engineering
7	24/08/2017	Explosion	Europa
8	21/06/2017	Dog Ate Postman	Europa
9	08/09/2017	Order Lost	Betty Café
10	01/05/2017	Order Lost	Moon
11	11/05/2017	Order Lost	Joe Blogs Engineering
12	25/08/2017	Wrong Item	Joe Blogs Engineering
13	14/05/2017	Wrong Planet	Mars Station
14	03/04/2017	Late Delivery	Betty Café

<tbody>
</tbody>

​

Presented Like This

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Count of Reason	Mth
2	Row Labels	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Grand Total
3	Dog Ate Postman	1		2		1	1				2	1	1	9
4	Explosion	1	1			1			1	1			1	6
5	Late Delivery		1		1	1		1			1			5
6	Order Lost					3				1				4
7	Wrong Item								1			1	1	3
8	Wrong Planet					1						1		2
9	Grand Total	2	2	2	1	7	1	1	2	2	3	3	3	29

<tbody>
</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!!