G
Guest
Hi - o.k. - I can't help asking because the posted answers to the above
question are so close to something I need.
What I'm looking for is a formula that totals all the unique dates for each
Event Code/Plex combination, example of data below. The challenge is that
there can be multiple rows with the same Event Code and Plex, each which may
have the same date(s) which may only be counted once. In the sample data
below, the formula should return a quantity of 8 for the combination of Event
Code/Plex "EART309/SHO" and 8 for "EART312/SHO".
Event CodePlex Service ID Dates Scheduled*
EART309 SHO SH2 6/7/2006, 1/5/2007
EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
6/7/2006
EART309 WOM WOM 12/9/2006
EART312 SHO SH2 6/7/2006, 1/6/2007
EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
3/31/2006, 1/6/2007
EART312 WOM WOM 12/12/2006
Some of the data is wrapped so I'll explain the format. There are 4 fields -
an Event, Plex, Service ID and Date. Above is the exact excel export of the
data from an auxiliary system. The dates are populated into a single excel
field and separated by commas within the field; however, I can parse them to
populate a single date per field if it makes the formula easier. I would have
the desired formula in the first column and cut and past all the calculation
data to the right of it. There would be duplicate totals, which is fine
since they are all the same number, since I can have multiple lines of the
same Event Code/Plex combination.
I thought maybe an Access DB would work better since I need to download the
date data and create ITD reports on a regular basis by Event. Any advice on
if a formula or Access would be better would be appreciated as well.
Thanks.
Orchid11652
question are so close to something I need.
What I'm looking for is a formula that totals all the unique dates for each
Event Code/Plex combination, example of data below. The challenge is that
there can be multiple rows with the same Event Code and Plex, each which may
have the same date(s) which may only be counted once. In the sample data
below, the formula should return a quantity of 8 for the combination of Event
Code/Plex "EART309/SHO" and 8 for "EART312/SHO".
Event CodePlex Service ID Dates Scheduled*
EART309 SHO SH2 6/7/2006, 1/5/2007
EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
6/7/2006
EART309 WOM WOM 12/9/2006
EART312 SHO SH2 6/7/2006, 1/6/2007
EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
3/31/2006, 1/6/2007
EART312 WOM WOM 12/12/2006
Some of the data is wrapped so I'll explain the format. There are 4 fields -
an Event, Plex, Service ID and Date. Above is the exact excel export of the
data from an auxiliary system. The dates are populated into a single excel
field and separated by commas within the field; however, I can parse them to
populate a single date per field if it makes the formula easier. I would have
the desired formula in the first column and cut and past all the calculation
data to the right of it. There would be duplicate totals, which is fine
since they are all the same number, since I can have multiple lines of the
same Event Code/Plex combination.
I thought maybe an Access DB would work better since I need to download the
date data and create ITD reports on a regular basis by Event. Any advice on
if a formula or Access would be better would be appreciated as well.
Thanks.
Orchid11652