unique sort

[orsm6]

Hi all - is excel clever enough to: Unique range, filtered for greater than zero/blank, sort ascending AND return all the values in ever other row? if so, how would that be done?

e.g:

1
miss a row
2
miss a row
3
miss a row....etc.

many thanks.

 

[orsm6]

Sorry - should have included, just assume column A holds list of values and i want to return the sorted list to C1

 

[Peter_SSs]

Like this?

23 10 17.xlsm
	A	B	C
1	2		1
2	5
3	3		2
4	2
5			3
6	2
7	0		5
8	5
9	9		9
10	1
fsus

Cell Formulas
Range	Formula
C1:C9	C1	=TEXTSPLIT(TEXTJOIN("||",,UNIQUE(SORT(FILTER(A1:A10,A1:A10>0)))),,"|")
Dynamic array formulas.

 

[orsm6]

Like this?

23 10 17.xlsm
	A	B	C
1	2		1
2	5
3	3		2
4	2
5			3
6	2
7	0		5
8	5
9	9		9
10	1
fsus

Cell Formulas
Range	Formula
C1:C9	C1	=TEXTSPLIT(TEXTJOIN("||",,UNIQUE(SORT(FILTER(A1:A10,A1:A10>0)))),,"|")
Dynamic array formulas.

absolutely spot on... thank you very much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[orsm6]

Like this?

23 10 17.xlsm
	A	B	C
1	2		1
2	5
3	3		2
4	2
5			3
6	2
7	0		5
8	5
9	9		9
10	1
fsus

Cell Formulas
Range	Formula
C1:C9	C1	=TEXTSPLIT(TEXTJOIN("||",,UNIQUE(SORT(FILTER(A1:A10,A1:A10>0)))),,"|")
Dynamic array formulas.

Hi Peter - since using this formula when i try to open the workbook it crashes. i do not get any error messages. It kind of feels like it is unable to load the app. I see the sheet, the load symbol then it closes on me.

I am now unable to get back into the book to fix it. Any thoughts?

 

[orsm6]

You're welcome. Thanks for the follow-up.

I think that it was because i have the formula pointed at a list that was a result of a unique formula. since changing the source, your formula continues to work and the file doesn't crash.

 

[Peter_SSs]

It kind of feels like it is unable to load the app.

By 'app' do you mean excel itself?

Any thoughts?

I have not heard of anybody reporting such a problem with using any of the functions in that formula.
I have a workbook with that sheet, data and formulas in it and it still opens fine.

If you can start Excel without that workbook, start a new workbook, type that sample data and formula in and save the workbook. Can you now close out of Excel, open again and open that new workbook?

 

[Peter_SSs]

I think that it was because i have the formula pointed at a list that was a result of a unique formula.

That should make no difference to the crash scenario as far as I can see.
However, it does mean that the formula that I suggested should not need the UNIQUE function since you already have a unique list. It also means the the formula can be pointed at the 'UNIQUE" spill range rather than a fixed range. I have an example below and confirm that I have still been able to close out of the workbook and/or Excel and get back in without any crash.

23 10 17.xlsm
	A	B	C	D	E
1	2		2		1
2	5		5
3	3		3		2
4	2		0
5			0		3
6	2		9
7	0		1		5
8	5
9	9				9
10	1
fsus (2)

Cell Formulas
Range	Formula
C1:C7	C1	=UNIQUE(A1:A10)
E1:E9	E1	=TEXTSPLIT(TEXTJOIN("||",,SORT(FILTER(C1#,C1#>0))),,"|")
Dynamic array formulas.

 

[orsm6]

That should make no difference to the crash scenario as far as I can see.
However, it does mean that the formula that I suggested should not need the UNIQUE function since you already have a unique list. It also means the the formula can be pointed at the 'UNIQUE" spill range rather than a fixed range. I have an example below and confirm that I have still been able to close out of the workbook and/or Excel and get back in without any crash.

23 10 17.xlsm
	A	B	C	D	E
1	2		2		1
2	5		5
3	3		3		2
4	2		0
5			0		3
6	2		9
7	0		1		5
8	5
9	9				9
10	1
fsus (2)

Cell Formulas
Range	Formula
C1:C7	C1	=UNIQUE(A1:A10)
E1:E9	E1	=TEXTSPLIT(TEXTJOIN("||",,SORT(FILTER(C1#,C1#>0))),,"|")
Dynamic array formulas.

thanks Peter - that one works