IF help

[markdoc5]

Hi All,

Not sure if what I'm looking for is possible with a formula or needs a script involved.

I have a simple enough sheet, with lets say 8 people and 15 questions. if they get a questions wrong it shows as trend as per the sample provided, is it possible to show bellow, who got the question wrong ? for example Person 1&5 have a trend on Q12 etc ?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
4	Person1	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
5	Person2	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
6	Person3	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
7	Person4	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend
8	Person5	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
9	Person6	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend
10	Person7	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
11	Person8	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:Q11	Cell Value	beginning with "Trend"	text	NO
C4:Q11	Cell Value	beginning with "No"	text	NO

 

[StephenCrump]

A bit of a guess, as the question isn't clear (and also assuming you have the new BYROW and BYCOL functions?)

Summary by person: =LET(w,BYROW(IF(C4:Q11="Trend",C3:Q3,""),LAMBDA(r,TEXTJOIN(", ",,r))),FILTER(B4:B11&": "&w,LEN(w)))

Summary by Q'n: =LET(w,BYCOL(IF(C4:Q11="Trend",B4:B11,""),LAMBDA(r,TEXTJOIN(", ",,r))),TRANSPOSE(FILTER(C3:Q3&": "&w,LEN(w))))

 

[markdoc5]

A bit of a guess, as the question isn't clear (and also assuming you have the new BYROW and BYCOL functions?)

Summary by person: =LET(w,BYROW(IF(C4:Q11="Trend",C3:Q3,""),LAMBDA(r,TEXTJOIN(", ",,r))),FILTER(B4:B11&": "&w,LEN(w)))

Summary by Q'n: =LET(w,BYCOL(IF(C4:Q11="Trend",B4:B11,""),LAMBDA(r,TEXTJOIN(", ",,r))),TRANSPOSE(FILTER(C3:Q3&": "&w,LEN(w))))

Thanks Stephen, i think your on to a winner, I do have the BYROW functions however the formula give me a error

 

[StephenCrump]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3			Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
4		Person1	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
5		Person2	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
6		Person3	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
7		Person4	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend
8		Person5	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
9		Person6	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend
10		Person7	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
11		Person8	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
12
13
14		Wrong
15		Person4: Q4, Q11
16		Person6: Q7, Q12
17		Person7: Q4
18
19
20		Wrong
21		Q4: Person4, Person7
22		Q7: Person6
23		Q11: Person4
24		Q12: Person6
Sheet1

Cell Formulas
Range	Formula
B15:B17	B15	=LET(w,BYROW(IF(C4:Q11="Trend",C3:Q3,""),LAMBDA(r,TEXTJOIN(", ",,r))),FILTER(B4:B11&": "&w,LEN(w)))
B21:B24	B21	=LET(w,BYCOL(IF(C4:Q11="Trend",B4:B11,""),LAMBDA(r,TEXTJOIN(", ",,r))),TRANSPOSE(FILTER(C3:Q3&": "&w,LEN(w))))
Dynamic array formulas.

 

[markdoc5]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3			Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15
4		Person1	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
5		Person2	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
6		Person3	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
7		Person4	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend
8		Person5	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
9		Person6	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend
10		Person7	No Trend	No Trend	No Trend	Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
11		Person8	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend	No Trend
12
13
14		Wrong
15		Person4: Q4, Q11
16		Person6: Q7, Q12
17		Person7: Q4
18
19
20		Wrong
21		Q4: Person4, Person7
22		Q7: Person6
23		Q11: Person4
24		Q12: Person6
Sheet1

Cell Formulas
Range	Formula
B15:B17	B15	=LET(w,BYROW(IF(C4:Q11="Trend",C3:Q3,""),LAMBDA(r,TEXTJOIN(", ",,r))),FILTER(B4:B11&": "&w,LEN(w)))
B21:B24	B21	=LET(w,BYCOL(IF(C4:Q11="Trend",B4:B11,""),LAMBDA(r,TEXTJOIN(", ",,r))),TRANSPOSE(FILTER(C3:Q3&": "&w,LEN(w))))
Dynamic array formulas.

Stephen, you are a gentleman of the highest order sir !!
thank you so much!

 

[StephenCrump]

You're welcome. I'm glad we could help.