Sorting large Lists
 

I have been trying to sort large lists of data using various formulas but to
no avail.

The problem I have is my worksheets consist of approx 50 columns for
products and 365 rows for the days of the year for each client.

Because ordering patterns are not the same, I want to see when the last time
a client had ordered:
Example 1: Product no1, product no2 with product no18
Example 2: Product no3, product no10 with product no20

The problem I have encountered is that what ever formula I try, it always
returns the last day the product was ordered not when it was last order with
say product 1 and 2.

Sorting large Lists
 

"Sir Percy B" <Sir Percy wrote in message
...
I have been trying to sort large lists of data using various formulas but
to
no avail.

The problem I have is my worksheets consist of approx 50 columns for
products and 365 rows for the days of the year for each client.

Because ordering patterns are not the same, I want to see when the last
time
a client had ordered:
Example 1: Product no1, product no2 with product no18
Example 2: Product no3, product no10 with product no20

The problem I have encountered is that what ever formula I try, it always
returns the last day the product was ordered not when it was last order
with
say product 1 and 2.

===================
Can you provide a sample of data? Perhaps 10 columns with 5 rows.

ryanb.

Sorting large Lists
 

Ryan as requested:

I have setup this example for you and copied direct from excel so I hope
this will assist.


Product Product Product Product Product Product Product
Day A B C D E F G
01-Jan-06 A B D F
02-Jan-06 A B G
03-Jan-06
04-Jan-06 A B C E F
05-Jan-06
06-Jan-06 D F
07-Jan-06 A B C
08-Jan-06 A G
09-Jan-06 A C D F
10-Jan-06

At a Glance you can see that when A&B was last ordered (07-Jan-06) only
Product C was also ordered.
By Filtering A & B I can see the last order and the other products that had
previously been ordered with A&B

Filtered A&B
Product Product Product Product Product Product Product
Day A B C D E F G
01-Jan-06 A B D F
02-Jan-06 A B G
04-Jan-06 A B C E F
07-Jan-06 A B C


Example of what the end result I am seeking:

Example: 1

Product Ordered
A:B A B C D E F G
Last
Order 07-Jan-06 07-Jan-06 07-Jan-06 01-Jan-06 04-Jan-06 04-Jan-06 02-Jan-06

Note: A&B last order date was 07/01/06, since then A,C,D,F were ordered on
09/01/06 all formulas I have tried
will normally return the actual last order dates not just when A&B were
last ordered.

Example 2:

Product Ordered
A:G A B C D E F G
Last Ordered 08-Feb-01 02-Jan-06 08-Jan-06


The Formula I am trying to establish here is one that automatically updates
daily without
having to use filters and coincides with order date entry against other
products.

Thankyou for your reply, I hope this helps and look forward to your
response.

Sorting large Lists
 

"Sir Percy B" wrote in message
...

Product Product Product Product Product Product Product
Day A B C D E F G
01-Jan-06 A B D F
02-Jan-06 A B G
03-Jan-06
04-Jan-06 A B C E F
05-Jan-06
06-Jan-06 D F
07-Jan-06 A B C
08-Jan-06 A G
09-Jan-06 A C D F
10-Jan-06

At a Glance you can see that when A&B was last ordered (07-Jan-06) only
Product C was also ordered.
By Filtering A & B I can see the last order and the other products that
had
previously been ordered with A&B

Filtered A&B
Product Product Product Product Product Product Product
Day A B C D E F G
01-Jan-06 A B D F
02-Jan-06 A B G
04-Jan-06 A B C E F
07-Jan-06 A B C


Example of what the end result I am seeking:

Example: 1

Product Ordered
A:B A B C D E F G
Last
Order 07-Jan-06 07-Jan-06 07-Jan-06 01-Jan-06 04-Jan-06 04-Jan-06
02-Jan-06

Note: A&B last order date was 07/01/06, since then A,C,D,F were ordered on
09/01/06 all formulas I have tried
will normally return the actual last order dates not just when A&B were
last ordered.

Example 2:

Product Ordered
A:G A B C D E F G
Last Ordered 08-Feb-01 02-Jan-06 08-Jan-06


The Formula I am trying to establish here is one that automatically
updates
daily without
having to use filters and coincides with order date entry against other
products.

======================
You can use DMAX() to do this. See attached workbook, or if you prefer I
can email it to you. Sorry for posting an attachment all, but it was too
much to key into a text message.

ryanb.

Sorting large Lists
 

Ryanb,

Thanks for your help, after a few layout changes and a few Dmax formula
examples I have manged to get the exact results needed.

Great Help
Thanks again.

"Sir Percy B" wrote:

I have been trying to sort large lists of data using various formulas but to
no avail.

The problem I have is my worksheets consist of approx 50 columns for
products and 365 rows for the days of the year for each client.

Because ordering patterns are not the same, I want to see when the last time
a client had ordered:
Example 1: Product no1, product no2 with product no18
Example 2: Product no3, product no10 with product no20

The problem I have encountered is that what ever formula I try, it always
returns the last day the product was ordered not when it was last order with
say product 1 and 2.