if, sumproduct, help by date

[Jim]

Hello,

This first chart is where I am entering names by day (sheet one). Please
notice that not all fields are filled in (this will vary by day).

Name 1 Name 2 Name 3 Name 4
1/1/2006
1/2/2006
1/3/2006
1/4/2006
1/5/2006 Smith Jones Cooper
1/6/2006 Smith Jones
1/7/2006 Jones Cooper
1/8/2006
1/9/2006 Jones Cooper
1/10/2006
1/11/2006 Jones
1/12/2006 Smith Jones Cooper Smith
1/13/2006 Jones
1/14/2006 Cooper Smith
1/15/2006 Smith Jones Cooper Smith
1/16/2006

What I need help with is writing a formula that will bring the names from
sheet 1 into a table I will keep on sheet 2. The names need to come over by
weekending: for example in the chart below I need to have the names listed on
1/15 first, then the 14th, 13th and so on all the way to the 9th. There
could be as many as 20 names per day (line), and I also need help getting
past hte null cells. Sheet two will have a start and end date listed on the
page.

Week ending: January 15, 2006
Smith Jones Cooper
Smith Cooper Smith
Jones Smith Jones

Thanks

 

[Biff]

Hi!

Your bottom "chart" doesn't seem to jive with your explanation and the data
posted in the top "chart".

Based on my understanding, wouldn't it be:

Week ending: January 15, 2006
15 = Smith Jones Cooper Smith
14 = Cooper Smith
13 = Jones
12 = Smith Jones Cooper Smith
11 = Jones
9 = Jones Cooper

This is fairly easy to do but requires "complicated formulas" that may
effect the overall performance of your file.

How big of a dataset has to be searched on Sheet1 in order to extract the
required data?

If there may be up to 20 names per day, then you'll need 20*7 formulas.
That's not a lot if this is all your file is doing.

Biff

 

[Jim]

So what is the formula?

Hi!

Your bottom "chart" doesn't seem to jive with your explanation and the data
posted in the top "chart".

Based on my understanding, wouldn't it be:

Week ending: January 15, 2006
15 = Smith Jones Cooper Smith
14 = Cooper Smith
13 = Jones
12 = Smith Jones Cooper Smith
11 = Jones
9 = Jones Cooper

This is fairly easy to do but requires "complicated formulas" that may
effect the overall performance of your file.

How big of a dataset has to be searched on Sheet1 in order to extract the
required data?

If there may be up to 20 names per day, then you'll need 20*7 formulas.
That's not a lot if this is all your file is doing.

Biff

 

[Biff]

After tinkering around with this it's not as complicated as I first thought.

Here's a sample file:

http://s57.yousendit.com/d.aspx?id=06V8UWPP3PSHD2OAYNMC3WX7B3

The file is based on your posted sample. If you actually have up to 20 names
per date then all you need to do is increase the range sizes and copy the
formulas to cover that range.

Biff