Forum Discussion
Excel formula to find the range of rows containing a value in a specific column.
That's great to know you found the solution, thank you for the update.
The limitation of it is that it works only on sorted data. More universal one like here
could be
=INDEX($C$2:$C$10, MATCH(AGGREGATE(14,6,1/($E$2:$E$10=E2)*$D$2:$D$10,1),$D$2:$D$10,0))Here AGGREGATE() finds the latest date for the ID, MATCH() returns position of such row and INDEX() returns Title for that row.
Based on two columns only, ID and Title, Excel can't recognizes which record is the latest. Perhaps you have some other fields. Better if you provide small sample file to illustrate how data is structured.
SergeiBaklan Thanks!
Yes...I have other fields. I'll nest the logic and know how to cover the age reference.
I just can't remember the approach to the range.
I've mocked up the scenario and sorted the data. The records include the data as it existed at the time the record was captured/changed in the system.
My thought was to create a formula that first "collected" the rows for a user ID (I'm calling this the row range), I'd look for the most recent record, and then grab the value from the cell with the most recent record in the "Title" column.
The Green is the target info (Output of the logic). The blue is the most recent date, and the User ID is used to define the row range (or Cell Range).
I've sorted the data by User ID first and then by effective date second so that the user records are contiguous with the most recent record appearing in the lowest numbered row. I vaguely remember that my past technique relied on a similarly sorted table. But I cant remember which formula technique I used.
Any suggestions would be great. My last approach is escaping me!
@Sergei Baklan I'd love suggestions on the row range, I think I figured out the simpler approach I performed last time. I think I used a simple IF statement that looks at the logic test comparing the current User ID to the prior (row) User ID. If not the same, then that row is the most recent record and so I pull the Title from that row's Title cell.
That's great to know you found the solution, thank you for the update.
The limitation of it is that it works only on sorted data. More universal one like here
could be
=INDEX($C$2:$C$10, MATCH(AGGREGATE(14,6,1/($E$2:$E$10=E2)*$D$2:$D$10,1),$D$2:$D$10,0))Here AGGREGATE() finds the latest date for the ID, MATCH() returns position of such row and INDEX() returns Title for that row.