Looking up final value that can be tracked in table

Hi,

I have my data in Table 1 and the lookup criteria in Table 2, which represents specific rows taken from Table 1. I want to use the row number for these rows (as listed in column N) to get the results that I have listed in Table 3. Please see attached workbook.

The rows that are colored in the same color are related in that they belong to the same Group (column A) and Code (column B) and that they are linked by timestamps (column D and E). For the linked rows, the timestamps work in such a way that the Changed timestamp (column E) of one row will be the Created timestamp (column D) of a row that represents a newer entry. There may be any number of linked rows for a given row, but the final - and thus most recent - linked row will always have the Code (column C) "false". All preceding linked rows will have the Code (column C) "true".

It'd be great if someone knew how to track these results down with a formula.

Best regards,
Marbleking

Hello!

I have come up with a solution to this problem that uses iteration. It requires that iterative calculation is enabled:

Options -> Formulas -> Enable iterative calculation

I have put the following formula in cell R4:

Please Login or Register  to view this content.

And the following formula in cell Q4:

Please Login or Register  to view this content.

Please see updated workbook attached.

PS: If someone is able to come up with a non-iterative solution, e.g. something involving array multiplications etc., that would be nice.

Best regards,
Marbleking

Hi

Try this in K4 (drag down and forward K4:M7)

Formula:

Please Login or Register  to view this content.

Use in O4 (drag down O4:O7)

Formula:

Please Login or Register  to view this content.

Note: You can use real True or False in column C and in that case formula in O4 must be =IF(J4,INDEX...

See the file for clarification

Hi José Augusto and thank you,

Your solution yields the correct results in this example. Your solution in O4:O7 rests on the assumption that for a given set of criteria in columns H and I where column J is "True", the correct results can be found by looking up the most recent timestamp which has the same criteria as given in columns H and I. But that may not always be the case in these data and I may have failed to capture that nuance in the example. It is true only when one can trace the timestamps in zig-zag between Created and Changed across the connected entries. I.e., there may be that the entry with the most recent timestamp actually is connected to a different "lineage" that coincidentally also share the criteria in columns H and I. Before I tried the iteration solution I tried to see if it was possible to use the "zero sum" that occurs if the relevant connected Created and Changed timestamps were substracted from each other, but that attempt stalled.

Best regards,
Marbleking

Hi,

See attached workbook. I have inserted a line in row 17 (purple) which helps illustrate the difference in results that may occur; please compare columns Q4:Q7 against S4:S7.

Best regards,
Marbleking

Yes, that is correct, XOR LX!

Thank you, XOR LX!

It's interesting to see the Frequency function in use. The solution yields wrong result for row 5 (second entry) of table 2; 1800,00% instead of 1,60%, matching row 17 in table 1 instead of the correct row 25 as given in table 2. Also, it looks like the frequency function, when in use for entries in table 2 that are "true", skips to the last entry in table 1 for which the criteria matches those given in column H, I and J of table 2 before locating the connected entry in table 1 which have "false". How does it make sure that the several identified "true" matches in table 1 are related, so that it skips to the correct last "true" entry? There is a link between row 21 and row 24 in table 1 through cells E21 and D24 because these are the same timestamps, but if E21 and D24 were not the same values then they wouldn't be related.

Regards

Please see updated workbook. I added a few more "purple" lines, this time in row 25 and 26. In table 2, cells J5 and J6, the criterion is "false". These lines do not require said "tracing", as lines with "false" always will be "final destination". Thus, the results found in M5:M6 (and row numbers pointing to table 1 in N5:N6) will be equal to what is expected to be found in table 3. In table 2, cells J4 and J7, the criterion is "true" and so these need "tracing" to arrive at their corresponding "false" lines in table 1 and return the corresponding results. In the updated workbook, you'll also see how jumping directly to the most recent "true" line in table 1 that happens to match with columns H and I of table 2 may yield wrong results.

Regards

Good catch; I have that line in there to account for the theoretical possibility, but I have never seen it in the actual data I am working with. If it creates a problem, you could change the criterion to "false", otherwise it can be kept in place as it is, if it can be accommodated into a solution.

Regards

That's awesome. I think you've nailed it. I need to work through this in detail tomorrow, and come back to you, but I'm very optimistic this time.

If you'd like to modify it into a version that yields the "traced back results" too, that'd be cool.

Thank you very much.

Regards

Originally Posted by XOR LX

No brain cells left for that part right now!

I'm impressed that someone had brain cells for the first part, so I understand. Have a nice evening in Turin.

Hi, XOR LX! I have tested your formula with various data today and it produces the correct results consistently, although there are some magic in there that I haven't yet been able to decipher. Very happy with this. Regards, Marbleking

Good afternoon, XOR LX!

I am again looking for a related formula to what you provided in this example, but this time I need the "traced back results" as indicated in the earlier attachment. Would you be able to modify the "traced forward results" formula to accommodate this, please?

Please Login or Register  to view this content.

Is it also possible to make it a dynamic array formula that automatically adjusts to the table size instead of having to be pulled down from row to row?

Best regards,
Marbleking

Hi,

I have attached an updated workbook that illustrates relationships between various data points. I am looking for a dynamic array formula that spills the results in Table 5. The correct results can be seen in Table 4. These results have been obtained using an iteration solution (Options -> Formulas -> Enable iterative calculation). These results come from looking at lines in Table 1 that belongs to the same Group and Code and where the timestamp seen in the column "Created" can be traced back to similar lines which have that same timestamp in their "Changed" column, in a zig-zag fashion. (See rows 25, 21 and 18 in column G as an example).

Table 2 and Table 3 shows solutions to the related problem of tracing forwards instead of backwards. The array solution in Table 3 was created by XOR LX and can serve as a good starting point.

I hope someone are able to come up with a solution to this.

Thanks,

Marbleking