Data representation

[Usercode]

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

 

[Marcelo Branco]

Not clear for me. Don't understand what you mean by

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).

Shouldn't it be? not present in the second row
Another issue: looking at the picture i see an "a" in J1, not "b"
Could you clarify?

M.

 

[Usercode]

I am sorry, I made a mistake there. I prepared the table in a rush. J1: (a) this is true. because even though there is no "1" in the next row, there is +1 of A1 which is (2.0). so it should be "a".
For every cell/value, in the next five columns there are four possibilities. Either same value (repetition), +1 of the value, -1 of the value, or none of these. if it is in the next first row, it should be represented with "a", if second row, it should be "b", if third row, it should be "c", it goes on like this till fifth row (e). after that, it is represented with "x". Please let me know if anything is unclear.

 

[Marcelo Branco]

Considering the data sample above (picture) what are the expected results in J1, K1....?

M.

 

[Usercode]

J1 = (a) because A1:1.0 and there is a 2.0 in the next row.
K1 = (a) because B1:3.0 and there is a 2.0 (and a 4.0) in the next row
L1 = (a) because C1:2.0 and there is a 2.0 in the next row.
M1 = (a) because D1:5.0 and there is a a 5.0 in the next row.
N1 = (b) because E1:7.0 and there is no repetition or +1/-1 in the next row, but there is 8.0, and also 6.0 in the next second row
O1 = (x) because F1:12.0 and there is no same or +1/-1 value in the next 5 rows.
P1= (a) because G1:6.0 and there is 5.0 in the next row.

 

[Peter_SSs]

Like this? This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1.0	3.0	2.0	5.0	7.0	12.0	6.0			a	a	a	a	b	x	a
2	2.0	10.0	4.0	5.0	5.0	4.0	5.0			a	b	a	a	a	a	a
3	1.0	2.0	8.0	4.0	6.0	2.0	4.0			a	a	a	a	a	a	a
4	4.0	5.0	2.0	4.0	5.0	7.0	9.0			a	a	a	a	a	a	a
5	1.0	2.0	5.0	7.0	8.0	9.0	10.0			a	a	a	x	x	x	x
6	2.0	4.0	4.0	1.0	2.0	4.0	5.0			x	x	x	x	x	x	x
Which Row

 

[Usercode]

This is beautiful. Even better with a formula solution. Thanks a lot, Peter!

 

[Peter_SSs]

This is beautiful. Even better with a formula solution. Thanks a lot, Peter!

You are welcome.

The very specific cell references, expected results and clear explanation in post 5 was much easier to understand than the first post.

 

[Usercode]

Peter, if it is not too much my asking, could you give me two separate versions of your formula? one version to show only +1/-1 values. second version is to show only the same values.


I mean instead of showing the +1/same/-1 of values of the data, I need two formulas to group them separately. one for +1/-1 and the other for same values. if it is not clear, please let me know.

 

[Peter_SSs]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	3	2	5	7	12	6			a	a	b	a	b	x	a
2	2	10	4	5	5	4	5			a	b	b	a	a	b	a
3	1	2	8	4	6	2	4			a	b	a	a	a	b	a
4	4	5	2	4	5	7	9			a	b	a	a	b	a	a
5	1	2	5	7	8	9	10			a	a	a	x	x	x	x
6	2	4	4	1	2	4	5			x	x	x	x	x	x	x
+-1

Cell Formulas
Range	Formula
J1	{=MID("abcdex",SMALL(IFERROR((ROW(A2:G6)-ROW())/((ABS($A2:$G6-A1)=1)*($A2:$G6<>"")),6),1),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	3	2	5	7	12	6			b	x	a	a	c	x	b
2	2	10	4	5	5	4	5			a	c	a	b	b	a	b
3	1	2	8	4	6	2	4			b	a	b	a	x	a	a
4	4	5	2	4	5	7	9			b	a	a	b	a	a	a
5	1	2	5	7	8	9	10			a	a	a	x	x	x	x
6	2	4	4	1	2	4	5			x	x	x	x	x	x	x
=

Cell Formulas
Range	Formula
J1	{=MID("abcdex",SMALL(IFERROR((ROW(A2:G6)-ROW())/((ABS($A2:$G6-A1)=0)*($A2:$G6<>"")),6),1),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.