You are reading a single comment by @aggi and its replies.
Click here to read the full conversation.
-
Off the top of my head in Excel, a couple of helper columns.
One a maxif which pulls out the max fy for a given id. One which is an if where the max fy equals the fy. Get rid of those where they don't match.
It's also possible to do this in powerquery but when I did similar in the past it took me a bit of experimenting to get the maxif working. It's the same principle though and a neater way, particularly if your data may change.
aggi
Say if someone extracted some data for financial years. And you have an id.
How do you say : for each id keep the row with the max fy?
I think you’d need to asssign the max fy by a lookup or switch?
I’m trying to dedupe a data set where a person can be at a place from a date.
If i combine id and the place name I’ll have a unique id. Which will let me do the “for this person and place”, how do I keep the row with the max fy?
Is my idea sensible?
It’s complicated by the fact there is a label for a full episode of stay in the place identifier. If I pivot the dataset, I can extract these easily. It’s the other recorsay I can’t get to.
R sql excel im not fussed here.
I can do a data table example later. (When I’m not cooking tea)