Forum Discussion
Excel formula to find the range of rows containing a value in a specific column.
Hi there.
Apologies in advance for such a simple question.
Data set is historic records for users with a number of records for each user. I have a unique user ID for each user and I want to find the range of rows containing the user ID (in the user ID column). I have sorted the data based on User ID first and then effective date of the record (row).
The goal is to extract the "Title" from the "Title" column in the most recent record/row for each user (based on User ID) to get the most recently known title.
I figured this out in the past but can't remember the core function I used to identify the 1st and last row for a given User ID (I remember the base worksheet needed to be sorted first).
Any help will be appreciated!
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.
6 Replies
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.
