Trying to create a formula to work on values in column E and column F
I want the formula to look at the value in column E and see where the value next appears historically in either column E or column F. On finding the next occurrence of the value I would like to read the time and date in column A and column B and record it in the row of the initial search value.
The time and date to be recorded should go in column G and column H respectively.
I would like to carry out the same operation on the values in column F. Again looking to see where the next value appears in column E or column F. On finding the next historical occurrence of the value I would like to read the time and date in column A and column B and this time record in the row of the initial search value but place the time and date in column I and column J respectively.
BEFORE
AFTER
NB: dates are UK format
thanks for any solutions. Will want to apply the formulas over several thousand rows.
cross posted at another site but although I advised the formula supplied worked , it didn't when copying down through several thousand rows.
link to cross post, as per forum rules, Time and Date of Next Value
I want the formula to look at the value in column E and see where the value next appears historically in either column E or column F. On finding the next occurrence of the value I would like to read the time and date in column A and column B and record it in the row of the initial search value.
The time and date to be recorded should go in column G and column H respectively.
I would like to carry out the same operation on the values in column F. Again looking to see where the next value appears in column E or column F. On finding the next historical occurrence of the value I would like to read the time and date in column A and column B and this time record in the row of the initial search value but place the time and date in column I and column J respectively.
BEFORE
Time and Date of Next Value in a 2 column search.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Date | Time | Value 1 | Value 2 | Time 1 | Date 1 | Time 2 | Date 2 | ||||
2 | 17:08 | 11/06/2019 | watch | pen | ||||||||
3 | 18:05 | 10/06/2019 | belt | umbrella | ||||||||
4 | 16:40 | 09/06/2019 | pipe | shoes | ||||||||
5 | 16:03 | 08/06/2019 | shoes | watch | ||||||||
6 | 14:28 | 07/06/2019 | lighter | umbrella | ||||||||
7 | 14:03 | 06/06/2019 | pen | belt | ||||||||
8 | 13:54 | 05/06/2019 | umbrella | pipe | ||||||||
9 | 12:50 | 04/06/2019 | watch | lighter | ||||||||
BEFORE |
AFTER
Time and Date of Next Value in a 2 column search.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Date | Time | Value 1 | Value 2 | Time 1 | Date 1 | Time 2 | Date 2 | ||||
2 | 17:08 | 11/06/2019 | watch | pen | 16:03 | 08/06/2019 | 14:03 | 06/06/2019 | ||||
3 | 18:05 | 10/06/2019 | belt | umbrella | 14:03 | 06/06/2019 | 14:28 | 07/06/2019 | ||||
4 | 16:40 | 09/06/2019 | pipe | shoes | 13:54 | 05/06/2019 | 16:03 | 08/06/2019 | ||||
5 | 16:03 | 08/06/2019 | shoes | watch | 12:50 | 04/06/2019 | ||||||
6 | 14:28 | 07/06/2019 | lighter | umbrella | 12:50 | 04/06/2019 | 13:54 | 05/06/2019 | ||||
7 | 14:03 | 06/06/2019 | pen | belt | ||||||||
8 | 13:54 | 05/06/2019 | umbrella | pipe | ||||||||
9 | 12:50 | 04/06/2019 | watch | lighter | ||||||||
AFTER |
NB: dates are UK format
thanks for any solutions. Will want to apply the formulas over several thousand rows.
cross posted at another site but although I advised the formula supplied worked , it didn't when copying down through several thousand rows.
link to cross post, as per forum rules, Time and Date of Next Value