Returning the most recent set of data given some variables

It would help if you attached a sample Excel workbook, so we can play about with different formulae.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

apologies - updated op

Okay, so where do you want to enter the name you are searching for (i.e. which cell)?

Pete

Originally Posted by Pete_UK

Okay, so where do you want to enter the name you are searching for (i.e. which cell)?

Pete

Let's say b1 - it doesn't matter truthfully, naturally this is a sample data set and the full version is 20000 rows deep, so the solution needs to work across that range as it's likely the name will appear multiple times with varying dates.

My existing solution before I got this extended dataset was a =if(isnumber(search, "platinum",name column), max(range of rows above)+1,"" - this essentially made a series of increasing numbers next to the fuzzy match which I then used as a reference point to index match to another sheet. The solution still works just that it now reruns multiple dates instead of the most recent bit of data.

Thanks for for the assist Pete

Using B1, then, as the cell where you can enter part of the name (e.g. Yuu or uwa or Wilson, or Tombl and so on - it doesn't have to be at the start of the name), you can use this array* formula in B2:

=IFERROR(INDEX($K$2:$K$100,MATCH(1,($D$2:$D$100=LARGE(IF(ISNUMBER(SEARCH($B$1,$E$2:$E$100)),$D$2:$D$100),1))*(ISNUMBER(SEARCH($B$1,$E$2:$E$100))),0)),"")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter. If you do this correctly then you will see curly brackets { and } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit the formula then you must use CSE again to confirm it.

I have used ranges up to row 100 (shown in red above), but you should change these to suit however many rows that you have (and use CSE to confirm).

Put a partial name in B1 and B2 will show the value from column K that relates to the latest date for that name.

Hope this helps.

Pete

Thanks for that Pete - it works perfectly and solves the problem i described... but unfortunately i'm poor at describing problems.

There's another variable i neglected to mention as i thought i'd figure out a solution to incorporate your solution into it but i'm not able to - sorry i should've explained my entire problem more clearly.

The C column has a bunch of codes - maybe 5000 or so unique identifiers - the solution needs to somehow get the newest (by date) column K per individual unique code identifier (column C).

i've added what my current output looks like, my current methodology as well as the desired output to hopefully add more colour

Thanks and sorry about that

This extra requirement puts a totally different slant on things. I've tried a few things, but I've not been able to come up with a solution, and I'm actually away for a few days from tomorrow so I won't be able to work on this.

It might be better to close this thread and start a new one, which includes all the requirements from the outset, and perhaps a new pair of eyes will be able to see how to do it.

Pete

Sorry about that Pete,

I actually solved it - it's a bit rudimentary but for my purposes that's more than enough.

I used my existing solution that data dumps all fuzzy match results - i then flipped the data upside down and used a =IF(AND(COUNTIF('Code Array','Code reference')=1,MAX('misc array')+1,"")

As a function of coincidence the 'bottom' set of the multiple data output is also the newest data so the above function creates a new number series only for the newest data - i then used this to index match a new list only containing the latest data set.

It's... properly clunky but it works

Thanks for all your help - much appreciated

Glad you got to a solution, and thanks for the rep.

Pete