Frequency per month (advanced filtering?)

  • Thread starter Thread starter daniel.bash
  • Start date Start date
D

daniel.bash

Hi,

I have a long list looking like this:

Box number Ship
date Dealer code
343
2008-12-12 SE1212
344
2008-12-12 SE1212
345
2008-12-12 SE1212
346
2008-12-12 SE1212
348
2008-12-12 SE1213
349
2008-12-12 SE1213
350
2008-12-12 SE1213
351
2008-12-12 SE1213
352
2008-12-12 SE1213

All boxes that have the same ship date and dealer code are shipped out
with the same shipment:

Box 344-346 => one shipment 2008-12-12 to SE1212
Box 348-352 => one shipment 2008-12-12 to SE1213

What I am trying to do is to see how many times a dealer received a
shipment per month.

I have tried to make a pivot table, but soon realised that in the
pivot table I could only get the count of boxes per month. Not the
count of shipments per month.

I would like to remove all but one box per dealer per day in my data
in order to create the correct pivot table (shipments/dealer/month).

Above example would then be shown as:

Box number Ship
date Dealer code
343
2008-12-12 SE1212
352
2008-12-12 SE1213


Does anyone know how to do this? Or have a solution to my problem.
Much appreciated if I could get some help on this one (I have been
scratching my head for a while...)
 
Hi,

I have a long list looking like this:

Box number                        Ship
date                                   Dealer code
343
2008-12-12                                 SE1212
344
2008-12-12                                 SE1212
345
2008-12-12                                 SE1212
346
2008-12-12                                 SE1212
348
2008-12-12                                 SE1213
349
2008-12-12                                 SE1213
350
2008-12-12                                 SE1213
351
2008-12-12                                 SE1213
352
2008-12-12                                 SE1213

All boxes that have the same ship date and dealer code are shipped out
with the same shipment:

Box 344-346 => one shipment 2008-12-12 to SE1212
Box 348-352 => one shipment 2008-12-12 to SE1213

What I am trying to do is to see how many times a dealer received a
shipment per month.

I have tried to make a pivot table, but soon realised that in the
pivot table I could only get the count of boxes per month. Not the
count of shipments per month.

I would like to remove all but one box per dealer per day in my data
in order to create the correct pivot table (shipments/dealer/month).

Above example would then be shown as:

Box number                        Ship
date                                   Dealer code
343
2008-12-12                                 SE1212
352
2008-12-12                                 SE1213

Does anyone know how to do this? Or have a solution to my problem.
Much appreciated if I could get some help on this one (I have been
scratching my head for a while...)
Click to expand...

Oops, the formatting on my example did not show correctly. The headers
of the table are:

Box number
Ship date
Dealer code

/Daniel
 
To remove all but unique records use the Advanced Filter function. To do
this:

(i) Insert two rows above your list
(ii) Duplicate the headers from your list (now in row 3) into row 1
(iii) Run the Advanced Filter option and populate List Range with B3:C12 and
Criteria Range with B1:C2 and tick the "Unique records only" option box

Your pivot table will now only return the filtered items in the list.
 
Hi,

I have a long list looking like this:

Box number                        Ship
date                                   Dealer code
343
2008-12-12                                 SE1212
344
2008-12-12                                 SE1212
345
2008-12-12                                 SE1212
346
2008-12-12                                 SE1212
348
2008-12-12                                 SE1213
349
2008-12-12                                 SE1213
350
2008-12-12                                 SE1213
351
2008-12-12                                 SE1213
352
2008-12-12                                 SE1213

All boxes that have the same ship date and dealer code are shipped out
with the same shipment:

Box 344-346 => one shipment 2008-12-12 to SE1212
Box 348-352 => one shipment 2008-12-12 to SE1213

What I am trying to do is to see how many times a dealer received a
shipment per month.

I have tried to make a pivot table, but soon realised that in the
pivot table I could only get the count of boxes per month. Not the
count of shipments per month.

I would like to remove all but one box per dealer per day in my data
in order to create the correct pivot table (shipments/dealer/month).

Above example would then be shown as:

Box number                        Ship
date                                   Dealer code
343
2008-12-12                                 SE1212
352
2008-12-12                                 SE1213

Does anyone know how to do this? Or have a solution to my problem.
Much appreciated if I could get some help on this one (I have been
scratching my head for a while...)
Click to expand...

Dear all,

I came up with a solution to my own problem :-) Maybe someone else
will have the same problem. So I'm now posting the answer to my own
question... The solution is to combine: ship date and dealer code, in
a new a new column then use advanced filter and filter unique values.

To combine the values type =A1&A2 in cell A3 (unique shipment
reference per day)
When: A1 = ship date, A2 = dealer code

After advanced filter is used to filter out duplicates in A3 then only
unique shipments/dealer/day remains.

/Daniel
 
Hello,

Your data organisation is a little bit unfortunate. I would suggest to
have all data for a record in one row. Then you could easily apply a
special filter, a pivot table or my UDF Pfreq:
http://www.sulprobil.com/html/pfreq.html
(please see also Sfreq - http://www.sulprobil.com/html/sfreq.html)

I am not saying that the task is impossible with your data
organisation - you already got some hints on how to proceed - but
further enhancements or additional data might make your (or your
colleagues) task almost unbearable.

Regards,
Bernd
 
Hello,

Your data organisation is a little bit unfortunate. I would suggest to
have all data for a record in one row. Then you could easily apply a
special filter, a pivot table or my UDF Pfreq:http://www.sulprobil.com/html/pfreq.html
(please see also Sfreq -http://www.sulprobil.com/html/sfreq.html)

I am not saying that the task is impossible with your data
organisation - you already got some hints on how to proceed - but
further enhancements or additional data might make your (or your
colleagues) task almost unbearable.

Regards,
Bernd
Click to expand...

Dear all,

I've tried advanced filtering (Test) and some Excel trickery [very
smart thinking] (Herbert Seidenberg). Both methods work like a charm.
Since I have so much data (over 65 000 rows) it seems like the
advanced filtering is the fastest option.

I haven't tried Bernd's method. It seems a bit more advanced...

Thank you all once again.

/Daniel
 
Back
Top