Extract and return a rows values and cells list which contains alphabetical texts

[mmr1]

Hi,

Required a formula help to Extract and return a rows which contains alphabetical text, and return a list wise cells values which contains alphabetical text as based on data specified in column(B4:E8)

desired results enclosed.

Thanks for the help,

Book4
	A	B	C	D	E	F
2
3
4		23516	36562	698547Q	154545O
5		14526LO	1545KL6	OP69874	52654
6		85964	47501	36524	98745
7		95856	74856LP	PL98564	63524
8		65653	14563	12546	65412
9
10		Desired Results-1
11		23516	36562	698547Q	154545O
12		14526LO	1545KL6	OP69874	52654
13		95856	74856LP	PL98564	63524
14
15	Desired Results-2
16	Sr.No	List
17	1	698547Q
18	2	154545O
19	3	14526LO
20	4	1545KL6
21	5	OP69874
22	6	74856LP
23	7	PL98564
24
25
Sheet1

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E
1
2
3		23516	36562	698547Q	154545O
4		14526LO	1545KL6	OP69874	52654
5		85964	47501	36524	98745
6		95856	74856LP	PL98564	63524
7		65653	14563	12546	65412
8
9		Desired Results-1
10		23516	36562	698547Q	154545O
11		14526LO	1545KL6	OP69874	52654
12		95856	74856LP	PL98564	63524
13
14	Desired Results-2
15	Sr.No	List
16	1	698547Q
17	2	154545O
18	3	14526LO
19	4	1545KL6
20	5	OP69874
21	6	74856LP
22	7	PL98564
Master

Cell Formulas
Range	Formula
B10:E12	B10	=FILTER(B3:E7,(ISTEXT(C3:C7))+(ISTEXT(D3:D7))+(ISTEXT(E3:E7)))
B16:B22	B16	=LET(a,B3:E7,r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,seq/r+1,MOD(seq,r)+1),FILTER(arr,ISTEXT(arr)))
Dynamic array formulas.

2nd formula pinched from here

Unique values for an array of n rows and m columns

Hi, I have a table of n rows and m columns. For simplicity, I use a table of 5 rows and 2 columns from B1:C5. I try to find unique values, filtering for blank value (as in C3). I look for a formula that extracts values from both columns and rows - I googled for the usage of UNIQUE function but...

www.mrexcel.com

 

[mmr1]

Many thanks fluff for your solution.

first formula solution is works to me

Second formula I do not have Let function yet to my version of office 365.


Thanks,

 

[Fluff]

Ok, try

=FILTER(INDEX(B3:E7,SEQUENCE(ROWS(B3:E7)*COLUMNS(B3:E7),,0)/ROWS(B3:E7)+1,MOD(SEQUENCE(ROWS(B3:E7)*COLUMNS(B3:E7),,0),ROWS(B3:E7))+1),ISTEXT(INDEX(B3:E7,SEQUENCE(ROWS(B3:E7)*COLUMNS(B3:E7),,0)/ROWS(B3:E7)+1,MOD(SEQUENCE(ROWS(B3:E7)*COLUMNS(B3:E7),,0),ROWS(B3:E7))+1)))

 

[mmr1]

Thank you so much fluff it works as needed

Appreciate your help.

 

[Fluff]

You're welcome & thanks for the feedback.