Database Functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJT - 13 Nov 2006 16:23 GMT

I am using a table w/ the following data in columns A - C:

Prospect Name Week Created Status
1 11/6/06 Open
2 11/6/06 Open
3 11/6/06 Open
4 11/6/06 Open
5 11/6/06 Open
2 11/13/06 Open
5 11/13/06 Closed
6 11/13/06 Open
7 11/13/06 Open
8 11/13/06 Open

Each week I add five names to the list. As you can see some of those names
are repeats from the previous week (e.g., "2" & "5"). Is there a database
function that will tell me which names fell off the list from week to week
(e.g."1", "3" & "4") and which were added (e.g. "6", "7", "8")?

Also, is there a LOOKUP type function that will refer to the most recent
occurence of a "Prospect Name" and return the "Status" (e.g. a search for "5"
would result in "Closed"?

Thank you in advance for your assistance

Reply to this Message

Biff - 13 Nov 2006 22:08 GMT

>Also, is there a LOOKUP type function that will refer to the most recent
>occurence of a "Prospect Name" and return the "Status" (e.g. a search for
>"5"
>would result in "Closed"?

Try this:

E2 = lookup value = 5

=LOOKUP(2,1/(A2:A11=E2),C2:C11)

>Is there a database function that will tell me
>which names fell off the list from week to week
>(e.g."1", "3" & "4") and which were added (e.g. "6", "7", "8")?

Possibly. Do you want to check the MOST RECENT data with the data from the
the previous week only? In other words, assume your sample data extended to
5 entries for 11/20/06. You want to check those entries against the entries
for 11/13/06 only? Are the dates *ALWAYS* a week apart like: 11/6, 11/13,
11/20, 11/27? If that's the case we can probably come up with something.

Biff

>I am using a table w/ the following data in columns A - C:
>
[quoted text clipped - 22 lines]
>
> Thank you in advance for your assistance

Reply to this Message

SJT - 14 Nov 2006 19:42 GMT

Thanks for your help. The dates are always a week apart and yes, I would
like to check the most recent data from the previous week only.

> >Also, is there a LOOKUP type function that will refer to the most recent
> >occurence of a "Prospect Name" and return the "Status" (e.g. a search for
[quoted text clipped - 45 lines]
> >
> > Thank you in advance for your assistance

Reply to this Message