Tayfun1992
New Member
- Joined
- Apr 1, 2019
- Messages
- 3
Hello friends. I have a very important project that I work on. I will be grateful if you could help me. I need to group and search the data on Excel. Below you can see the schema of the data:
Column A Column B Column C Column D Column E
1. X 1 July
2. Y 2 July
3. A 3 July
4. Z 4 July
5. B 5 July
6. Y 6 July
On the left (1., 2., etc.) row numbers. Data in column A. The dates of the data in column B are. Other columns are currently empty. Let's group the data first. So let's create the template we will search. Now let's create the template for the 6th row: 6. row "Y". What are two sequential elements before Y? "Z-B". "Y" is seen in row 2 before row 6. So who's behind here? X. We created the combination: (X-Y) + (Z-B-Y). There must be two consecutive "Z-B" before "Y" and "Y" must be sequential of "x" before 6th row. That's the combination of row 6. When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column C on row 6.
- Now, let's define the SECOND template we will search. Again we'll create for row 6: Which of the three consecutive elements before "Y"? "A-Z-B". Here we ignore the SECOND element "Z". Thus, the sequence "A-B-Y" remains in our hands. "Y" is seen on row 2 before row 6. So who's here before? "X". Now let's write the template: (X-Y) + (A-B-Y). When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column D on row 6.
- Now, let's define the THIRD template we will search. Again we'll create for row 6: Which of the three consecutive elements before "y"? "A-Z-B". Here we ignore the THIRD element "B". Thus, the sequence "A-Z-Y" remains in our hands. "Y" is seen on row 2 before row 6. So who's here before? "X". Now let's write the template: (X-Y) + (A-Z-Y). When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column E on row 6.
I want the procedure described above for all rows. I tried to explain it as simple as I could. I hope you understand. If you can create a formula for this, I will be grateful. Thank you, happy days for everyone.
Column A Column B Column C Column D Column E
1. X 1 July
2. Y 2 July
3. A 3 July
4. Z 4 July
5. B 5 July
6. Y 6 July
On the left (1., 2., etc.) row numbers. Data in column A. The dates of the data in column B are. Other columns are currently empty. Let's group the data first. So let's create the template we will search. Now let's create the template for the 6th row: 6. row "Y". What are two sequential elements before Y? "Z-B". "Y" is seen in row 2 before row 6. So who's behind here? X. We created the combination: (X-Y) + (Z-B-Y). There must be two consecutive "Z-B" before "Y" and "Y" must be sequential of "x" before 6th row. That's the combination of row 6. When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column C on row 6.
- Now, let's define the SECOND template we will search. Again we'll create for row 6: Which of the three consecutive elements before "Y"? "A-Z-B". Here we ignore the SECOND element "Z". Thus, the sequence "A-B-Y" remains in our hands. "Y" is seen on row 2 before row 6. So who's here before? "X". Now let's write the template: (X-Y) + (A-B-Y). When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column D on row 6.
- Now, let's define the THIRD template we will search. Again we'll create for row 6: Which of the three consecutive elements before "y"? "A-Z-B". Here we ignore the THIRD element "B". Thus, the sequence "A-Z-Y" remains in our hands. "Y" is seen on row 2 before row 6. So who's here before? "X". Now let's write the template: (X-Y) + (A-Z-Y). When this combination was last seen, we need to find it. The date we are looking for is in column B. This date will be written to column E on row 6.
I want the procedure described above for all rows. I tried to explain it as simple as I could. I hope you understand. If you can create a formula for this, I will be grateful. Thank you, happy days for everyone.
