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.
Riny_van_Eekelen Hi, I've just pulled in data from the last month and I realise that some where there are blank cells it is pulling that in as a repeat call. At least I think that is what is happening. The data isn't being pulled through to the pivot table at despite being refreshed.
Jane_at_Stamma Can't really help without seeing the file.
Riny_van_Eekelen here you go
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