WRAPCOLS formatting and numbering

[tigerzen]

Would like to be able to conditionally format the wrapped columns as per the spreadsheet below, anyone with priority 1 should be highlighted, also the individual cells should be numbered. Of the 2 requests I've made, the first one is more urgent. Thank you.

Football stats functions.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Dept	Priority						Desired solution
2	Tom	HR	1		1 Tom	1 Vic	1 Jake		1 Tom	4 Vic	7 Jake
3	Jack	Sales	3		1 Jack	1 Mary	1 Ted		2 Jack	5 Mary	8 Ted
4	Sam	Admin	2		1 Sam	1 Noel	1 Ed		3 Sam	6 Noel	9 Ed
5	Vic	HR	2
6	Mary	Sales	1
7	Noel	Admin	2
8	Jake	HR	3
9	Ted	Sales	1
10	Ed	Admin	2
Sheet25

Cell Formulas
Range	Formula
E2:G4	E2	=ROW(A1)&" "&WRAPCOLS(A2:A10,3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:G4	Expression	=C1=1	text	NO

 

[Armstrong_N14]

please disregard.

 

[tigerzen]

please disregard.

???????

 

[Armstrong_N14]

???????

Book6
	A	B	C	D	E	F	G	H
1	No.	Name	Dept	Priority
2	1	Tom	HR	1		1 Tom	4 Vic	7 Jake
3	2	Jack	Sales	3		2 Jack	5 Mary	8 Ted
4	3	Sam	Admin	2		3 Sam	6 Noel	9 Ed
5	4	Vic	HR	2
6	5	Mary	Sales	1
7	6	Noel	Admin	2
8	7	Jake	HR	3
9	8	Ted	Sales	1
10	9	Ed	Admin	2
Sheet1

Cell Formulas
Range	Formula
F2:H4	F2	=WRAPCOLS(LET(s,SEQUENCE(9),CONCATENATE(INDEX(A2:A10,s)," ",INDEX(B2:B10,s))),3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:H4	Expression	=VLOOKUP(TEXTAFTER(F2," ",1,0),$B$2:$D$10,3,0)=1	text	NO

 

[Peter_SSs]

A variation

23 09 11.xlsm
	A	B	C	D	E	F	G	H
1	Name	Dept	Priority			Desired solution
2	Tom	HR	1			1 Tom	4 Vic	7 Jake
3	Jack	Sales	3			2 Jack	5 Mary	8 Ted
4	Sam	Admin	2			3 Sam	6 Noel	9 Ed
5	Vic	HR	2
6	Mary	Sales	1
7	Noel	Admin	2
8	Jake	HR	3
9	Ted	Sales	1
10	Ed	Admin	2
WRAPCOLS

Cell Formulas
Range	Formula
F2:H4	F2	=WRAPCOLS(ROW(A2:A10)-ROW(A2)+1&" "&A2:A10,3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:H4	Expression	=VLOOKUP(TEXTAFTER(F2," "),$A:$C,3,0)=1	text	NO

 

[Armstrong_N14]

this is

A variation

23 09 11.xlsm
	A	B	C	D	E	F	G	H
1	Name	Dept	Priority			Desired solution
2	Tom	HR	1			1 Tom	4 Vic	7 Jake
3	Jack	Sales	3			2 Jack	5 Mary	8 Ted
4	Sam	Admin	2			3 Sam	6 Noel	9 Ed
5	Vic	HR	2
6	Mary	Sales	1
7	Noel	Admin	2
8	Jake	HR	3
9	Ted	Sales	1
10	Ed	Admin	2
Sheet2 (2)

Cell Formulas
Range	Formula
F2:H4	F2	=WRAPCOLS(ROW(A2:A10)-ROW(A2)+1&" "&A2:A10,3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:H4	Expression	=VLOOKUP(TEXTAFTER(F2," "),$A:$C,3,0)=1	text	NO

this is better.

 

[Rick Rothstein]

Another variation...

=WRAPCOLS(SEQUENCE(ROWS(A2:A10))&" "&A2:A10,3)

 

[Peter_SSs]

Another variation...

=WRAPCOLS(SEQUENCE(ROWS(A2:A10))&" "&A2:A10,3)

I was originally going to post that, but then discovered the other one was 1 character shorter.

 

[tigerzen]

Many thanks to you all!

 

[Rick Rothstein]

I was originally going to post that, but then discovered the other one was 1 character shorter.

Funny! I figured you must have seen this construction, so I wondered why you went with the one you did. Personally, I think the SEQUENCE approach is "cleaner", but now I understand reason behind your choice.