Count # of appearances

[shaibn]

Hi,

I have the following spreadsheet where I need to calculate 2 results:

- On Track
- Lost Track

The excel has the following columns:

Member ID, Created On (Date)

On Track: needs to calculate the number of consistent times in the SAME day for the member ID. In the example, member ID G1289603X has the same date appears twice, therefore we will count "2" and will place the value at the 'On Track' column.
Lost Track: need to calculate the number of inconsistent times for the SAME member during the days (Created On column). for example. member ID G1289603X has an appearance at 7/23 but also at 7/21, however, since the days are inconsistent (there is no appearance at the 7/22), we will count "1" and place it at the "Lost Track" column at the 7/23/2016 row.

There are also, as you can see, other members appear but their appearance is only 1. In this case, we should leave both columns (On Track and Lost Track) as blank as there is no additional appearance for the members as they appear only once per each day.

Anyone that can help is highly appreciated.

Attached is the Excel file.


Member ID Created On On Track Lost Track
G1289603X 7/21/2016
G1289603X 7/21/2016 2
G1289603X 7/23/2016 1
G1289603X 8/12/2016
G1289603X 8/12/2016
G1289603X 8/12/2016 3
S9117361A 7/21/2016
HMBR1 7/21/2016
HMBR303 7/21/2016
S9177922F 7/21/2016
S8435089C 7/21/2016
S8431026C 7/21/2016
S7863862A 7/21/2016
S7863862A 7/22/2016 1





Thanks.

[Glenn Kennedy]

two things.

1. Check over column C. The formula looks overcomplicated. it is!! However some of the dates aren't dates, but date/times. Thsi adds some complication.

2. can you re-explian what you want at column D. i didn't quite follow you.

[shaibn]

That is an impressive resolution Glenn - formula seems a bit complicated!!!

Thanks!

Column D: So as you can see in cell D4, the number of "lost track" is "1" because there is a delay in a day for the same member. The two first records appear on 7/21 but then there is a delay of two days to the next row. so eventually I would like to count "1" in this column whenever there is a delay of a day for the same member, or there is no additional row with the same date for the same member.

[shaibn]

Glenn,

The formula work perfectly! Thank you very much.

Had the chance to work on the D column?

Thanks.