Forum Discussion
Use colour to enter a logical value
Jane_at_Stamma Transform your table to a structured Excel table and use a formula like in the "repeat" column to count the number of occurrences of the current caller ID up to the current row. If 1, then this is first time caller. Anything higher than 1, then it's a repeat caller.
By the way, I changed the conditional formatting rules a bit. See attached.
Jane_at_Stamma OK, I amended the "Repeat" formula in the master table and created a new pivot table in the "Repeat Callers" sheet. Records with a "cli" will not be counted as "Yes" and the pivot table pulls in the July data as intended. You'll notice that my table is structured differently from yours. Can't really tell why you decide to do a count of the Count column where you could count the number of CallID's (in the Values field) for all records where the "repeat" code is "Yes" (in the Filter field).
See if this is what you expect to see.
thank you Riny_van_Eekelen for your time! I updated the pivot table - lord I find them really hard - and have noticed that now I have a number of calls labelled as repeat=yes, (3) in both June and July, which aren't coloured pink, nor can I find when they are repeated. all the other months/years look fine