Merging Multiple Cell Values into 1 Cell

[ajwooden32]

My original question was needing a formula to find each date in B4:O25 in row Q3:IV3, then automatically enter text from B3:O3 into the intersecting cell below specific date. B4 has a sample that I manually entered into the correct locations. This question was answered using this formula:

=IF(AND($B4>=Q$3,$B4<R$3),$B$3,IF(AND($C4>=Q$3,$C4<R$3),$C$3,IF(AND($D4>=Q$3,$D4<R$3),$D$3,IF(AND($E4>=Q$3,$E4<R$3),$E$3,IF(AND($F4>=Q$3,$F4<R$3),$F$3,IF(AND($G4>=Q$3,$G4<R$3),$G$3,IF(AND($H4>=Q$3,$H4<R$3),$H$3,IF(AND($I4>=Q$3,$I4<R$3),$I$3,IF(AND($J4>=Q$3,$J4<R$3),$J$3,IF(AND($K4>=Q$3,$K4<R$3),$K$3,IF(AND($L4>=Q$3,$L4<R$3),$L$3,IF(AND($M4>=Q$3,$M4<R$3),$M$3,IF(AND($N4>=Q$3,$N4<R$3),$N$3,IF(AND($O4>=Q$3,$O4<R$3),$O$3,""))))))))))))))

Now I need to take the above formula (which works perfectly) to combine cells with the same date from B4:O25 into one cell over in Q4:AZ25. Can this be done?

 

[awoohaw]

What is an example of expectations with the last sentence?
Also, you have a lot of data that the forum needs to manually recreate to help you solve your question. Please use the xl2bb add in (link below), or post your data as a table that can be copied. While the image does a good job of explaining your question it takes time for the forum to manually type it in and they can have errors as well.
And, you'll get many more responses from forum members.

Thanks in advance.

 

[dermie_72]

It doesn't look like there are alot of instances were this would happen. Maybe use a conditional formatting in columns B4:O25 (Making sure B4 is the active cell in the range, then using the formula to determine cell format:

=COUNTIF($B4:$O4,B4)>1

and choosing the format Fill (Pick a color)
This will highlight the cell in the color you chose if it's a duplicate. You can either filter by color or scan down the page, depending on how much data you have.

 

[StephenCrump]

Also, you have a lot of data that the forum needs to manually recreate to help you solve your question. Please use the xl2bb add in (link below), or post your data as a table that can be copied. While the image does a good job of explaining your question it takes time for the forum to manually type it in and they can have errors as well.

All good comments!

But (with just a little bit of typing) I think this is what you're after?

	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
1
2																	2021												2022
3		Pre	Con 1	Con 2	F					D	P	PP	O				Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
4		Apr 21								Jul 22	May 23	Nov 22								Pre															D				PP
5		May 21		Jul 22	Jul 22						May 21										Pre,P														Con 2,F
Sheet3

Cell Formulas
Range	Formula
Q3:AN3	Q3	=EDATE(DATE(2021,1,1),SEQUENCE(,24,0))
Q4:AN5	Q4	=TEXTJOIN(",",,FILTER($B$3:$M$3,(MONTH($B4:$M4)=MONTH(Q$3))*(YEAR($B4:$M4)=YEAR(Q$3)),""))
Dynamic array formulas.

 

[ajwooden32]

What is an example of expectations with the last sentence?
Also, you have a lot of data that the forum needs to manually recreate to help you solve your question. Please use the xl2bb add in (link below), or post your data as a table that can be copied. While the image does a good job of explaining your question it takes time for the forum to manually type it in and they can have errors as well.
And, you'll get many more responses from forum members.

Thanks in advance.

I was meaning the formula above works perfectly for placing one cell's value in the correct location. It just doesn't combine values into one cell if multiple cells have the same date.

 

[StephenCrump]

It just doesn't combine values into one cell if multiple cells have the same date.

Did you try Post #4?

 

[ajwooden32]

@StephenCrump This worked great for a month to month timeline. Thank you for this piece!!!

How do I get this same type of information into a quarter by quarter columns & cells combined into an individual cell? Currently the data in P3:AY23 is all just manual entry which takes too long when dates move.

I should also mention, all the Q1, Q2, Q3, Q4 are currently just text in P2:AY2. Not sure how to enter these quarters in each cell & for them to be searchable.

 

[StephenCrump]

One way, perhaps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2																	2021				2022
3		Pre	Con 1	Con 2	F					D	P	PP	O				Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
4		Apr 21								Jul 22	May 23	Nov 22						Pre					D	PP
5		Jul 21		Nov 22	Nov 22						Jul 21								Pre,P					Con 2,F
Sheet1

Cell Formulas
Range	Formula
Q4:X5	Q4	=LET(c,COLUMNS($Q3:Q3),y,INDEX($Q3:$X3,c),TEXTJOIN(",",,FILTER($B$3:$M$3,(CEILING(MONTH($B4:$M4),3)=3+3*MOD(c-1,4))*(YEAR($B4:$M4)=INT($Q$2+(c-1)/4)),"")))

I should also mention, all the Q1, Q2, Q3, Q4 are currently just text in P2:AY2. Not sure how to enter these quarters in each cell & for them to be searchable.

You could enter as numbers, formatted as "Q"0. Then 1,2 .... will display as Q1, Q2 ...