concatenate cell of certain color

[mshunt13]

I have a cell range, G4:AK4, that I would like to concatenate only the cells that are a certain color. The value of the cells are number. I would also like to include a coma between each number.

 

[mshunt13]

92003516 Eval Testing v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1		Incomplete Days	17	# Entry Days		374						Compliance Score															'1NC Fast'!																		January
2		Incomplete Days Rate	4.7%	# Errors		17			0% = Full Compliance						Greater than 10% = Non-Compliance with Corrective Action Plan
3		Days Present	360	Error Rate		4.5%			1% - 10% = Substantial Compliance																	Z	AA		AC				AG												Name	Month	Errors	Month
4	1	BRANDON	JALESCYIA	Durham		JAL. B.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31								JAL. B.	January	2, 5, 6, 7, 8, 12	Durham
5	10	Incomplete Days	2	Entry Days	31	NC Fast	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
6		Present Days	30	Emerg Closure Days	0	Sign In/Out	A		P	P	P	P	P	P	P	P	P		P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
7		Inclement Weather Days	0	Absent Days	1	Classroom
8		Provider Closure Days	0	Attendance Errors	2			IN										IN
9						Eval. Attend.	A	0	P	P	P	P	P	P	P	P	P	0	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
10		Total number of Corrections		2		Corrections		A										A
11	2	DEW	YAMANI	Wake		YAM. D.	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31								YAM. D.	January	2, 12	Wake
12	3	Incomplete Days	1	Entry Days	23	NC Fast	A	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
13		Present Days	22	Emerg Closure Days	0	Sign In/Out	A		P	NS	NS	P	P	P	P	P	NS		P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
14		Inclement Weather Days	0	Absent Days	1	Classroom
15		Provider Closure Days	0	Attendance Errors	1			IN
16						Eval. Attend.	A	0	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
17		Total number of Corrections		1		Corrections		A
Jan 2020

Cell Formulas
Range	Formula
AG3,AC3,Z3:AA3	Z3	=LEFT(ADDRESS(1,COLUMN(),2),1+(COLUMN()>26))
B4,B11	B4	=INDIRECT($AA$1&"A"&$A4+1)
D4,D11	D4	=INDIRECT($AA$1&"C"&$A4+1)
F1	F1	= SUMIF(D$4:D$1048576,"Entry Days",E$4:E$1048576)
F2	F2	= SUMIF(D$4:D$1048576,"Attendance Errors",E$4:E$1048576)
F3	F3	=F2/F1
F4,F11	F4	=CONCATENATE(LEFT(C4,3),". ",LEFT(B4,1),".")
AS4,AS11	AS4	=F4
AT4,AT11	AT4	=$AS$1
AU4,AU11	AU4	=CONCATENATEbycolor($AK$1,G4:AK4)
AV4,AV11	AV4	=D4
A4,A11	A4	=(ROW()-4)/7+1
A5,A12	A5	=INDIRECT($AA$1&"D"&$A4+1)
G12:AK12,G5:AK5	H5	=ExtractCap(INDIRECT($AA$1&LEFT(ADDRESS(1,COLUMN(),2),1+(COLUMN()>26))&$A4+1))
I13:Q13,G13,S13:AK13,I6:Q6,G6,S6:AK6	S6	=S5
C1	C1	= SUMIF(B$4:B$1048576,"Incomplete Days",C$4:C$1048576)
C2	C2	=C1/C3
C3	C3	= SUMIF(B$4:B$1048576,"Present Days",C$4:C$1048576)
C4,C11	C4	=INDIRECT($AA$1&"B"&$A4+1)
C5,C12	C5	=COUNTIF(G8:AK8,"IN")
C6,C13	C6	=COUNTIF(F5:AK5,"P")
C7,C14	C7	=COUNTIF(F5:AK5,"IW")
C8,C15	C8	=COUNTIF(F5:AK5,"PC")
E5,E12	E5	=SUM(C6:C8,E6:E7)
E6,E13	E6	=COUNTIF(G5:AK5, "EC")
E7,E14	E7	=COUNTIF(G5:AK5, "A")
E8,E15	E8	=COUNTIF(G10:AK10, "A")
G15:AK15,G8:AK8	G8	=IF(G5="","",IF(G5="NE","",IF(G5="IW","",IF(G5="PC","",IF(G5="EC","",IF(G5="PT","",IF(G5="NS","",IF(G5="NB","",IF(G5="A","",IF(AND(G6=""),"IN",""))))))))))
G16:AK16,G9:AK9	G9	=IF(G5="","",IF(G5="NE","NE",IF(G5="IW","IW",IF(G5="PC","PC",IF(G5="EC","EC",IF(G5="PT","PT",IF(G5="NS","NS",IF(G5="NB","NB",IF(G5="A","A",IF(G6="P","P",IF(G7="P","P",IF(G8="P","P",))))))))))))
G17:AK17,G10:AK10	G10	=IF(G9="NE","",IF(G9="P","",IF(G9="NB","",IF(G9=G5,"","A"))))
D10,D17	D10	=COUNTIF(G10:AK10, "A")+COUNTIF(G10:AK10, "PC")+COUNTIF(G10:AK10, "IW")+COUNTIF(G10:AK10, "EC")+COUNTIF(G10:AK10, "NB")+COUNTIF(G10:AK10, "P")+COUNTIF(G10:AK10, "NS")+COUNTIF(G10:AK10, "NE")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G10:AR10,G17:AR17,G24:AR24,G31:AR31,G38:AR38,G45:AR45,G52:AR52,G59:AR59,G66:AR66,G73:AR73,G80:AR80,G87:AR87,G94:AR94,G101:AR101,G108:AR108,G115:AR115,G122:AR122	Expression	=SUM(COUNTIF(G10,"*"&Mylist&"*"))	text	NO
G10:AR10,G17:AR17,G24:AR24,G31:AR31,G38:AR38,G45:AR45,G52:AR52,G59:AR59,G66:AR66,G73:AR73,G80:AR80,G87:AR87,G94:AR94,G101:AR101,G108:AR108,G115:AR115,G122:AR122	Cell Value	contains "A"	text	NO

 

[Peter_SSs]

Thanks for the XL2BB. It does, however, raise more questions. You originally said that you wanted to concatenate gray cells but then indicated they were conditionally formatted. This XL2BB sample though only has yellow conditionally formatted cells. So which ones do you want concatenated gray or yellow?

In rows 10 and 17 as shown all the cells that are not yellow are empty so the concatenation would be "" in each case.

Have I mis-interpreted what you ant or have your requirements changed.

Further, you have a CF formula that relates to Mylist but you have told us nothing about what that is and it appears there are no example cells coloured by that rule?
Looking at the formulas in G10:AK10 and G17:AK17 those cells will either be "A" or "" so I'm not sure how Mylist could come into it anyway.

All very confusing I must say.

 

[mshunt13]

In the example sent, I had removed the conditional format and was manually coloring fill because that is the only way it was working. Basically I want a formula to concatenate the dates (numbers in the black filled row) for the columns that have an "A" highlighted in yellow.
I have a coworker working on this also. I am not sure how the Mylist is working in this spreadsheet either. I can add the conditional format by in and send it again

 

[Peter_SSs]

In the example sent, I had removed the conditional format and was manually coloring fill ...

Hmm, since it was the Conditional Formatting I was specifically asking about, that wasn't too helpful.

I want a formula to concatenate the dates (numbers in the black filled row) for the columns that have an "A" highlighted in yellow.

I cannot see any "A" values that are not yellow so I assume the requirement is simply to concatenate if the cell contains "A".

mshunt13 2020-04-09 1.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
4	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
5	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
6	A		P	P	P	P	P	P	P	P	P		P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
7
8		IN										IN
9	A	0	P	P	P	P	P	P	P	P	P	0	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
10		A										A																				2,12
11	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
12	A	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
13	A		P	NS	NS	P	P	P	P	P	NS		P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
14
15		IN
16	A	0	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P	NS	NS	P	P	P	P	P
17		A																														2
Jan 2020

Cell Formulas
Range	Formula
AL10,AL17	AL10	=TEXTJOIN(",",1,IF(G10:AK10="A",G4:AK4,""))

 

[mshunt13]

I tried that already. it gives me a #value! error

 

[Peter_SSs]

I tried that already. it gives me a #value! error

There are 3 circumstances that I know of (could be more) that can cause TEXTJOIN to return #VALUE!
- There is already a #VALUE! error in the cells referenced by the formula (eg G4:AK4 and G10:AK10)
- Any of the individual cells trying to be joined (eg G4:AK4) contains more than 255 characters.
- The final result of the TEXTJOIN is longer than 32,767 characters

From what you have posted, none of those things are apparent & as you can see the results with the values you posted work fine since I used your posted values in post #14.

That makes me think that what you have posted is not telling the whole story. Certainly one thing is that your sheet uses a function (ExtractCap) that we don't have and we also obviously do not have the other sheet(s) that your data is extracted from.

Perhaps you can make a small dummy workbook that demonstrates the problem & upload it to DropBox, OneDrive etc and share a link here so that we can take a look to investigate further?

 

[mshunt13]

Test Report SHunt v6.xlsm

Shared with Dropbox

www.dropbox.com

 

[Peter_SSs]

When I enter my formula in that file ('Jan 2020' tab) I get no errors and almost the same result as you.
The only differences between my result and yours are

• You seem to have missed out on checking column AE in your very long formula, and
• I did not include a space after the comma in my TEXTJOIN (but it can easily be added in as shown in AU6) below

The full file is provided here: 13 Test Report SHunt v6.xlsm

13 Test Report SHunt v6.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK								AS	AT	AU
4	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31								New. Y.	January	2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31
5	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P										2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31
6																																									2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31
7
8		IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN	IN
9	A	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10		A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A	A
Jan 2020

Cell Formulas
Range	Formula
AS4	AS4	=F4
AT4	AT4	=$AN$1
G5:AK5	G5	=ExtractCap(INDIRECT($AA$1&LEFT(ADDRESS(1,COLUMN(),2),1+(COLUMN()>26))&$A4+1))
AU4	AU4	=TEXTJOIN(", ",1,IF(G10="A",G4,""),TEXTJOIN(", ",1,IF(H10="A",H4,""),TEXTJOIN(", ",1,IF(I10="A",I4,""),TEXTJOIN(", ",1,IF(J10="A",J4,""),TEXTJOIN(", ",1,IF(K10="A",K4,""),TEXTJOIN(", ",1,IF(L10="A",L4,""),TEXTJOIN(", ",1,IF(M10="A",M4,""),TEXTJOIN(", ",1,IF(N10="A",N4,""),TEXTJOIN(", ",1,IF(O10="A",O4,""),TEXTJOIN(", ",1,IF(P10="A",P4,""),TEXTJOIN(", ",1,IF(Q10="A",Q4,""),TEXTJOIN(", ",1,IF(R10="A",R4,""),TEXTJOIN(", ",1,IF(S10="A",S4,""),TEXTJOIN(", ",1,IF(T10="A",T4,""),TEXTJOIN(", ",1,IF(U10="A",U4,""),TEXTJOIN(", ",1,IF(V10="A",V4,""),TEXTJOIN(", ",1,IF(W10="A",W4,""),TEXTJOIN(", ",1,IF(X10="A",X4,""),TEXTJOIN(", ",1,IF(Y10="A",Y4,""),TEXTJOIN(", ",1,IF(Z10="A",Z4,""),TEXTJOIN(", ",1,IF(AA10="A",AA4,""),TEXTJOIN(", ",1,IF(AB10="A",AB4,""),TEXTJOIN(", ",1,IF(AC10="A",AC4,""),TEXTJOIN(", ",1,IF(AD10="A",AD4,""),TEXTJOIN(", ",1,IF(AF10="A",AF4,""),TEXTJOIN(", ",1,IF(AG10="A",AG4,""),TEXTJOIN(", ",1,IF(AH10="A",AH4,""),TEXTJOIN(", ",1,IF(AI10="A",AI4,""),TEXTJOIN(", ",1,IF(AJ10="A",AJ4,""),TEXTJOIN(", ",1,IF(AK10="A",AK4,"")))))))))))))))))))))))))))))))
AU5	AU5	=TEXTJOIN(",",1,IF(G10:AK10="A",G4:AK4,""))
AU6	AU6	=TEXTJOIN(", ",1,IF(G10:AK10="A",G4:AK4,""))
G8:AK8	G8	=IF(G5="","",IF(G5="NE","",IF(G5="IW","",IF(G5="PC","",IF(G5="EC","",IF(G5="PT","",IF(G5="NS","",IF(G5="NB","",IF(G5="A","",IF(AND(G6=""),"IN",""))))))))))
G9:AK9	G9	=IF(G5="","",IF(G5="NE","NE",IF(G5="IW","IW",IF(G5="PC","PC",IF(G5="EC","EC",IF(G5="PT","PT",IF(G5="NS","NS",IF(G5="NB","NB",IF(G5="A","A",IF(G6="P","P",IF(G7="P","P",IF(G8="P","P",))))))))))))
G10:AK10	G10	=IF(G9="NE","",IF(G9="P","",IF(G9="NB","",IF(G9=G5,"","A"))))

 

[mshunt13]

thank you.

 

[Peter_SSs]

You're welcome.