Recursive lookup, get last, newest occurrence of a value

[schatham]

Excel 2010 - I have data that is in the following format. It is sorted in date order, ascending.

Excel 2010
	A	B	C
1	keyVal	date	newKeyVal
2	0-4087	2016-02-17 13:40:21.843	0B4105
3	0-4092	2016-02-17 13:40:21.970	044-619
4	0-4087	2016-02-17 13:40:22.017	044-619
5	0B4105	2016-02-17 13:40:22.017	044-619
6	0B4105	2016-02-17 13:40:23.923	0-4087
7	0-4087	2016-02-17 13:40:23.923	0-4087
8	0-4092	2016-02-17 13:40:23.923	0-4087
9	044-619	2016-02-17 13:40:23.937	0-4087
10	19504006	2016-02-17 13:41:07.220	0-4092
11	2519504006	2016-02-17 13:41:07.220	0-4092
Sheet1

In another worksheet, I have a list of key values & need a function to do a recursive lookup. In my worksheet, when a part number is entered, I need for the lookup to go through the list (above) and return the most recent <b>newKeyVal</b> for which there are no newer entries.

In the above, when <b>keyVal</b> 2519504006 is entered, the <u>correct</u> <b>newKeyVal</b> to be returned is <b> 0-4087</b>. The lookup would look up the 2519504006 value in <b>keyVal</b>, retrieve the <b>newKeyVal</b> 0-4092, then lookup <b>keyVal</b> 0-4092 & return <b>newKeyVal</b> 0-4087, since that date-time stamp is the last one in the sequence for which there is no newer entry.

The data in the table is a tracking of how the key values change. To "correct" an entry, a new <b>keyVal - newKeyVal</b> entry will be made. For example, if we have A --> B --> C, and determine that the B --> C entry is incorrect, a <u>new</u> <b>keyVal</b> of C with a <b>newKeyVal</b> of B would be entered. The sequence would then be A --> B ---> C --> B. The date time of the A --> B entry would be the oldest, and the C --> B entry would be the newest.

I have a function that doesn't quite work - it will return the correct value only if the entries are in order, but it won't return the correct value for the above. The correct <b>newKeyVal</b> for part 2519504006 should be 0-4087. A11 --> C11 & A8 --> C8.

I can post the function I have if it would be helpful.

 

[BiocideJ]

I am a bit confused.
2519504006 is the newest entry in the list. Why is 0-4092 not the correct output.

Also, you say this should be a recursive formula which seems to make sense, but I am unclear what your base condition is to stop the recursive calling.

I can follow A11 > C11 > C8
but the only thing I can think for the termination of the recursive loop is that you are also then comparing A7 and seeing C7 is the latest entry and since A7=C7 then the loop finishes, however, you don't mention this in your criteria.

 

[schatham]

The mapping of 2519504006 --> 0-4092 would only affect those items with 2519504006 as it's key. It's result, 0-4092 has also had its key re-assigned, this time to the latest entry of A8 - 0-4092 & mapping to C8 - 0-4087. The <b>newKeyVal</b> 0-4087 doesn't have a re-mapped <b>keyVal</b> that is newer than the date time in B8.

The function would need to recurse through the sequence (2519504006 --> 0-4092 & 0-4092 --> 0-4087) until there was either (1) no newKeyVal (Column C) was found in the keyVal (Column A), or (2) there was no newer mapping than the one in Row 8 (0-4092 --> 0-4087). In the above data, Row 7's mapping wouldn't be followed because the date in row 7 isn't greater than the date in row 8.

Sorry that I didn't state that more clearly.

 

[BiocideJ]

Perhaps I am just totally missing something obvious here, but I still am not understanding how the process of remapping is looking backwards to find 0-4092 ->> 0-4087 and not also then looking back to find 0-4087 ->> ???

You say "The newKeyVal 0-4087 doesn't have a re-mapped keyVal that is newer than the date time in B8."
But I see the newKeyVal 0-4092 ALSO doesn't have a re-mapped keyVal that is newer than the date time in B11.

The logic I am assumed was what you need based on what you stated would look something like this...

Given: [2519504006]
Look in column A (starting at the beginning) to find the last occurrence of 2519504006 [row 11]
Find the corresponding NewKeyVal (from that row) [0-4092]
Look in column A (starting in A12) to find the last occurrence of 0-4092 [none found]
Return: [0-4092]

 

[schatham]

Perhaps I am just totally missing something obvious here, but I still am not understanding how the process of remapping is looking backwards to find 0-4092 ->> 0-4087 and not also then looking back to find 0-4087 ->> ???

You say "The newKeyVal 0-4087 doesn't have a re-mapped keyVal that is newer than the date time in B8."
But I see the newKeyVal 0-4092 ALSO doesn't have a re-mapped keyVal that is newer than the date time in B11.

The logic I am assumed was what you need based on what you stated would look something like this...

Given: [2519504006]
Look in column A (starting at the beginning) to find the last occurrence of 2519504006 [row 11]
Find the corresponding NewKeyVal (from that row) [0-4092]
Look in column A (starting in A12) to find the last occurrence of 0-4092 [none found]
Return: [0-4092]

What I needed to do was to keep repeating the search, using the retrieved <b>newKeyVal</b> as my <b>keyVal</b> until one of two things happened:
1. The search for <b>keyVal</b> returned nothing, OR
2. The search encountered a repeating pattern (a <b>keyVal</b> referencing itself).

In doing so, I would need to find the most recent mapping. For example:
Searching for <b>keyVal</b> 2519504006, I would find 0-4092, then search <b>keyVal</b> for the most recent mapping of it. Even though it's mapped in row 3 (0-4092 --> 044-619), there's a newer one in row 8 (0-4092 --> 0-4087), so I would use row 8's data. Then, I would search <b>keyVal</b> for 0-4087. Even though it's mapped in rows 2 & 4, the newest mapping is in row 7 (0-4087 --> 0-4087). Once I would hit the repeating value, the function should exit, with my mapping of 2519504006 --> 0-4087.

I tried nearly anything I could think of, but couldn't get any of them to work correctly.

To solve my problem, I ended up adding a few columns to my worksheet, and sorting the above list in date order, from oldest to newest. In a different worksheet, I would enter the number I wanted in A2, and in D2, I had the formula:
=IFERROR(LOOKUP(2,1/(TrackingA=$A2),TrackingB),"")
where TrackingA referred to the above examples Column A (keyVal), and TrackingB referred to Column C (newKeyVal). In column E2, I would have:
=IFERROR(LOOKUP(2,1/(TrackingA=$D2),TrackingB),"")
and repeat the formula in the columns to the right, until I got either a #N/A or a repeating pattern. E2 referenced D2's value, F2 referenced E2's value, G2 referenced F2's value, and so on. For every column I added, I also added a second one, H2, I2, J2 & K2. In those columns, I had the formula:
[cell H2] =IF($D2=$E2,"",$E2)
and repeated this formula out to the right for the same number of columns. Each column compared the next pair columns (E & F, then F & G, etc). Repeating values would return an empty cell.

In B2, I had this formula:
=LOOKUP(2,1/($D2:$M2<>""),TRIM($D2:$M2))
to return the rightmost non-blank value, which in this case was 0-4087.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Number	Last Value		Lookup1	Lookup2	Lookup3	Lookup4	U1	U2	U3	U4
2	2519504006	0-4087		0-4092	0-4087	0-4087	0-4087	0-4087
Sheet1

Sort of a klunky way to do it, but I didn't see any other way that I could have the lookups trace it's change history correctly.

Thanks for your help!