Hi,
specifically using a pivot table so the table automatically adjusts when rows are added or removed from the source table, but I can't get it to work and look the way I want it to.
Thanks a lot for looking!
Hi,
specifically using a pivot table so the table automatically adjusts when rows are added or removed from the source table, but I can't get it to work and look the way I want it to.
Thanks a lot for looking!
You can't get the result you want with a pivot table. since some of the data is text.
First sort the data on name and date from small to large.
With formula's in the yellow cell.
The result is in the green cells with VLookup.
Notice the result is differant than your result.
The differance is the color, since I expect you want the color on 01-01-1970 have the same color as the last value.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Add 3 helper columns:
most recent date: =AGGREGATE(14,6,[date]/([name]=[@name]),1)
how often date = 1-1-1970: =COUNTIFS([name],[@name],[date],DATE(1970,1,1))
color at most recent date: =IF([@date]=[@[most recent date]],[@color],"")
Then in PivotTable, filter away the blank in "color at most recent date" field.
maybe
name date color Count of date Jane 01/01/1970Red 1Mark 01/01/1970Orange 1Tom 01/01/1970Green 2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks