Merge 3 spilled data columns together

[CV12]

Hello everybody,

For my project I will be having a couple of columns where spilled data is presented. Each column is a separate spilled column.
Is it somehow possible to merge the 3 columns in col C, D and E together? This way I can sort one of the columns, and have the other two columns change with the sorted column.
Please note that the amount of rows are variable.

Sandbox.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1			1	11	500				1	11	500
2			2	22	200				2	22	200
3			3	33	300				3	33	300
4			4	44	600				4	44	600
5			5	34	800				5	34	800
6			6	31	400				6	31	400
7			7	20	900				7	20	900
8			8	25	850				8	25	850
9			9	42	100				9	42	100
Sandbox2

Cell Formulas
Range	Formula
C1:E9	C1	=UNIQUE(I1:I9)
Dynamic array formulas.

 

[Peter_SSs]

Perhaps I am not fully understanding your intention but unless you are going to copy/paste values, thereby removing the spilling formulas, you won't be able to sort anyway.
In your sample, try sorting just C1:C9 in descending order.

 

[CV12]

Peter,

What I would like to do is the following.

Value 100 in E9, belongs to D9 and C9. Now if I sort col E, col C and D do not change, and thus making the columns of data incorrect.

When the spilled data is merged together, so lets say that we use a unique function and col I, J and K together, IT is possible tot sport col E and have col C and D also change.

In my project however, I cannot have a merged together columns spill data, but the columns are separate like in this example.

Now I would like to merge col C D E, so that if I sort a single column, the two other columns also change with the sorter column.

Does this clarify what I would like to do?

 

[Peter_SSs]

Now if I sort col E,

My point was that with the spill formula in E1 you cannot sort column E anyway. Did you try to do that?

 

[CV12]

If I am not mistaken, if the spilled data is merged, I can sort the 3 columns by sort(E1#) in another cell. So I think the current 3 columns will be helper columns. Is that correct?

 

[Peter_SSs]

sort(E1#)

Ah, you are going to sort by formula - I had thought that you were going to physically sort with, say ..

What about something like this?

22 01 29.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	11	500				1	11	500			9	42	100
2	2	22	200				2	22	200			2	22	200
3	3	33	300				3	33	300			3	33	300
4	4	44	600				4	44	600			6	31	400
5	5	34	800				5	34	800			1	11	500
6	6	31	400				6	31	400			4	44	600
7	7	20	900				7	20	900			5	34	800
8	8	25	850				8	25	850			8	25	850
9	9	42	100				9	42	100			7	20	900
10
Sort spill

Cell Formulas
Range	Formula
C1:E9	C1	=UNIQUE(I1:I9)
N1:P9	N1	=INDEX(SORTBY(C1:E20,E1:E20),SEQUENCE(COUNT(E1#)),{1,2,3})
Dynamic array formulas.

 

[CV12]

That's it Peter, thanks!!

 

[Peter_SSs]

That's it Peter, thanks!!

You're welcome. I think I went the long way about it though.

22 01 29.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	11	500				1	11	500			9	42	100
2	2	22	200				2	22	200			2	22	200
3	3	33	300				3	33	300			3	33	300
4	4	44	600				4	44	600			6	31	400
5	5	34	800				5	34	800			1	11	500
6	6	31	400				6	31	400			4	44	600
7	7	20	900				7	20	900			5	34	800
8	8	25	850				8	25	850			8	25	850
9	9	42	100				9	42	100			7	20	900
10
Sort spill

Cell Formulas
Range	Formula
C1:E9	C1	=UNIQUE(I1:I9)
N1:P9	N1	=SORT(FILTER(C1:E20,E1:E20<>""),3,1)
Dynamic array formulas.