hard hard hard to solve..............

  • Thread starter Thread starter wooo
  • Start date Start date
W

wooo

The following data is the record of customer which sent
in one month.It is a simplied sample. If I would like to
find out which customer is sent more than 3 times per
month, how can i do that, the difficult is the occur
times of the customer is duplicated at the same day. how
can i show the customers which were sent more than 3
times.
can it shown by report form? query form?

customer invoice sent date
ABC 001 1-JUL-2003
ABC 002 1-JUL-2003
XYZ 003 1-JUL-2003
ZOO 004 1-JUL-2003
--------------------------------------------
ABC 005 5-JUL-2003
XYZ 006 5-JUL-2003
ZOO 007 5-JUL-2003
--------------------------------------------
ABC 008 12-JUL-2003
ABC 009 12-JUL-2003
XYZ 010 12-JUL-2003
ZOO 011 12-JUL-2003
ZOO 012 12-JUL-2003
 
wooo said:
The following data is the record of customer which sent
in one month.It is a simplied sample. If I would like to
find out which customer is sent more than 3 times per
month, how can i do that, the difficult is the occur
times of the customer is duplicated at the same day. how
can i show the customers which were sent more than 3
times. ....
--->ABC was sent more than 3 times in July.
Click to expand...

ABC was sent 7 invoices on 4 different days. Is that 7 times or 4 times?
Presumably you mean the latter (4) because the former (7) could be given by
a simple COUNTIF or SUMPRODUCT.

If your table, excluding the dashed lines but including the top row of
column headings, were in A1:C15, then you could add a 4th column, D, that
gave the number of times each customer appeared in different days. This
would involve some redundancy, but enter the following array formula in
D2:D15.

=MMULT(TRANSPOSE((A2:A15=TRANSPOSE(A2:A15))/
MMULT(--((A2:A15&TEXT(C2:C15,"yyyymmdd"))
=TRANSPOSE(A2:A15&TEXT(C2:C15,"yyyymmdd"))),
ROW(A2:A15)^0)),ROW(A2:A15)^0)

Then you could use an advanced filter to pull each Customer for which the D
column was >3.
 
If you wanted to accomplish this via SMicrosoft query it would look
something like this

Select Customer, Count(*)
from name of spreadsheet or data table

where invoice sent date between 1-JUL-2003 and 31-JUL-2003
having count(*)>2
 
Back
Top