I have a complex problem. I hope I can find a solution here.
I have a range of sample data in columns A:G. I want to create a representation of this data in columns I:O like this:
Each cell of every row is compared with the next 5 rows. If the value of the cell or +1/-1 value of the cell is present in any of the rows, then it is represented with one of the letters (a,b,c,d,e). If there is a repetition, then the closest row/cell is counted only.
Starting from each cell of every row, if the value of the cell or +1/-1 value is present in the next row, then it is represented with "a", and the process stops for this cell, and goes to the next cell in the same row.
If not present, then it checks the next row, and if the same or +1/-1 is present in the next row, it is repsented with "b".
it goes on like this for the next 5 rows, and represent with (a,b,c,d,e) in order.
For example, A1: (1.0), and it is not present in second column so it's skipped, and in the next column, it is present (A3). so It will be represented with "b" in J1: (a).
B1: (3.0) is not present in the next 5 rows, but +1/-1 value of 3.0 is present, in the next column, so it is represent with "a" in K1: (a)
and if the value or +1/-1 value of the cell is not present in the next 5 rows, then it is represented with "x".
Sample data:https://1drv.ms/x/s!AoGkZUHlKui9gRean_XzUEkUo7_c
I have a range of sample data in columns A:G. I want to create a representation of this data in columns I:O like this:
Each cell of every row is compared with the next 5 rows. If the value of the cell or +1/-1 value of the cell is present in any of the rows, then it is represented with one of the letters (a,b,c,d,e). If there is a repetition, then the closest row/cell is counted only.
Starting from each cell of every row, if the value of the cell or +1/-1 value is present in the next row, then it is represented with "a", and the process stops for this cell, and goes to the next cell in the same row.
If not present, then it checks the next row, and if the same or +1/-1 is present in the next row, it is repsented with "b".
it goes on like this for the next 5 rows, and represent with (a,b,c,d,e) in order.
For example, A1: (1.0), and it is not present in second column so it's skipped, and in the next column, it is present (A3). so It will be represented with "b" in J1: (a).
B1: (3.0) is not present in the next 5 rows, but +1/-1 value of 3.0 is present, in the next column, so it is represent with "a" in K1: (a)
and if the value or +1/-1 value of the cell is not present in the next 5 rows, then it is represented with "x".
Sample data:https://1drv.ms/x/s!AoGkZUHlKui9gRean_XzUEkUo7_c
