Is it possible ?

R

russlock

New Member
Joined
Sep 17, 2003
Messages
26
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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.
 
Upvote 0
russlock said:
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.
Click to expand...







Do you mean something like this?




This Data

Excel 2010
ABC
ExplosionSaturns Rings
Dog Ate PostmanJoe Blogs Engineering
ExplosionEuropa
Late DeliverySaturns Rings
Dog Ate PostmanJoe Blogs Engineering
ExplosionEuropa
Dog Ate PostmanEuropa
Order LostBetty Café
Order LostMoon
Order LostJoe Blogs Engineering
Wrong ItemJoe Blogs Engineering
Wrong PlanetMars Station
Late DeliveryBetty 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
ABCDEFGHIJKLMN
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?!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Assign dates as late as possible without duplicates
Replies
1
Views
149
AlphaFrog
AlphaFrog
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
D
  • Question Question
Copy rows to a new workbook based on a date range of last three months from today()
Replies
8
Views
530
dandee14k
D
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top