Auto sequential output in Ascending or Descending order

[bramacharya]

Hi All,

Let say if got 4 cells A, B, C and D.

when there is input like any numbers or alphabets, i should able to get out based on below conditions in Ascending or Descending order.

A EQUAL OR LESS THAN B
B EQUAL OR GREATHER THAN A BUT EQUAL OR LESS THAN C
C EQUAL OR GREATHER THAN B BUT EQUAL OR LESS THAN D
D EQUAL OR GREATHER THAN C


I dont know whether must use vbd code or formula to get this outputs and no dont know the right keywords to search in google.

I've attched the excel image for your better understanding.

Please help me. Thank You.

 

[srehman]

you can try if you have office 365 apply as per your logic.


=SORTBY(G9:G13,H9:H13,1)

Name	Qty		Result
a	5​		e
b	10​		a
c	7​		c
d	25​		b
e	3​		d

 

[Habtest]

Is this what you are looking for?

Book2
	A	B	C	D	E	F	G	H	I	J
1
2		A	B	C	D		A	B	C	D
3		2	5	8	9		A	B	C	E
4
5		0	2	5	8		A	A	B	C
6		1	3	6	9		B	B	C	D
7		2	4	7				C	D	E
8		3	5	8					E	F
9		4	6	9						G
10		5	7							H
11			8							I
12										J
13										K
14										L
15										M
16										N
17										O
18										P
19										Q
20										R
21										S
22										T
23										U
24										V
25										W
26										X
27										Y
28										Z
Sheet1

Cell Formulas
Range	Formula
B5:B10,E5:E6,D5:D9,C5:C11	B5	=SEQUENCE(IF(ISBLANK(C3),9,C3)-A3+1,,A3)
G5:G6,J5:J28,I5:I8,H5:H7	G5	=CHAR(SEQUENCE(IF(ISBLANK(H3),90,CODE(H3))-IF(ISBLANK(F3),65,CODE(F3))+1,,IF(ISBLANK(F3),65,CODE(F3))))
Dynamic array formulas.

 

[bramacharya]

You Are Great.

This is what I'm looking for.. ?????

Marvelous.

Thank You So Much. ????

 

[Habtest]

Glad it worked. Thanks for the feedback

 

[bramacharya]

=SEQUENCE(IF(ISBLANK(C3),9,C3)-A3+1,,A3)

Hi Habtest,

I've try above formula in Office 365 and its working fine.

But not in Ms Office 2019.May I know why ?

 

[Habtest]

Dynamic array or SEQUENCE() function is not available before O365. Here is a solution for O2019, a lot clunkier and you need to manually drag the formula down to a certain range.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		A	B	C	D		A	B	C	D
3		2	5	7	8		A	B	C	E
4
5		0	2	5	7		A	A	B	C
6		1	3	6	8		B	B	C	D
7		2	4	7	9			C	D	E
8		3	5	8					E	F
9		4	6							G
10		5	7							H
11										I
12										J
13										K
14										L
15										M
16										N
17										O
18										P
19										Q
20										R
21										S
22										T
23										U
24										V
25										W
26										X
27										Y
28										Z
Sheet1

Cell Formulas
Range	Formula
B5:E15	B5	=IF(B4<IF(ISBLANK(C$3),9,C$3),A$3+COUNT(B$4:B4),"")
G5:J28	G5	=IF(OR(ISNUMBER(MATCH(H$3,G$4:G4,0)),ISNUMBER(MATCH("Z",G$4:G4,0))),"",CHAR(IFERROR(CODE(F$3),CODE(G$3))+COUNTA(G$4:G4)))

 

[bramacharya]

Thank You

 

[bramacharya]

Dear Habtest,

Good Day To You.

I need your help again.

How to get output like below example?

Thank You.