Using the Excel formula, I am trying to find values and match values with previous occurrences. The total repetition of values is fetched from another table using vlookup function. Along with this another condition is added to check if the time difference in match value is within 1 hour limit. If it is less than one hour, the value can't be repeated but if it is greater than 1 hour. the value can be repeated.
I used the formula
=IFERROR( INDEX(D4:F4, MATCH(1, (COUNTIF(G$1:$G3, D4:F4) <= VLOOKUP(D4:F4, J$2:K$7, 2, FALSE)) * (LEN(D4:F4) > 0) * ( (COUNTIFS(G$1:$G3, D4:F4) = COUNTIFS($G$1:$G3, D4:F4)) * (IFERROR(MATCH(D4:F4, G$1:G3, 0), 0) >= 0) * (IFERROR(MATCH(1, (ABS(A4 - INDEX(A$1:A3, MATCH(D4:F4, G$1:$G3, 0))) > TIME(1, 0, 0)), 0), 0) >=0) ), 0) ), "")
In this, row 3 should have value of either 328 or d5e because 13a is found in g1 and also time difference between a3 and a1 is less than 1 hour.
Any help to fix this formula is greatly apprecialted.
I used the formula
=IFERROR( INDEX(D4:F4, MATCH(1, (COUNTIF(G$1:$G3, D4:F4) <= VLOOKUP(D4:F4, J$2:K$7, 2, FALSE)) * (LEN(D4:F4) > 0) * ( (COUNTIFS(G$1:$G3, D4:F4) = COUNTIFS($G$1:$G3, D4:F4)) * (IFERROR(MATCH(D4:F4, G$1:G3, 0), 0) >= 0) * (IFERROR(MATCH(1, (ABS(A4 - INDEX(A$1:A3, MATCH(D4:F4, G$1:$G3, 0))) > TIME(1, 0, 0)), 0), 0) >=0) ), 0) ), "")
In this, row 3 should have value of either 328 or d5e because 13a is found in g1 and also time difference between a3 and a1 is less than 1 hour.
Any help to fix this formula is greatly apprecialted.