Combining cells in one column based on values in cells of another column

[angel34]

Seasons greetings.

I have a table in excel where column A consists of dates in unsorted manner. Same dates may appear several times in the column. Column B comprises of text values. I would like to combine (concatenate), the values in Column B with a comma separator, where date in Column A is identical. As for example, for all date entries of 1/10/2018, if there are 4 entries altogether (say) in A1, A2, A3 and A4, I am looking to combine the corresponding texts in B1, B2, B3 and B4 in Column E.

I have already extracted the unique date values in Column D and looking for a formula in Column E which will combine the text values in B1:B4 based on the unique date values extracted in Column D.

I was trying to use the formula CONCATENATEIF(A:A,D1,B:B,",") but soon realized that this is not a standalone excel formula and should be used with a UDF to make it work.

I am neither able to use a UDF or a helper column and soI was wondering whether there could be a solution to this by using a standard excel formula which can be copied down Column E to accomplish the task.

I shall be most thankful for any insight. Thanks and Regards

 

[angel34]

Thank you for your continued support. As I have already written, I cannot test this code in my workplace PC. I shall try this out in my home PC and shall revert back soon. As written in my original post, since there are identical dates in several cells of Column A, I had to extract the unique values of dates in Column D. I am looking to reference Column D in lieu of Column A, as suggested in your formula. Does this get redundant once I use this formula along with the StringConcat function. By the way, from which version of Excel does the TEXTJOIN function has been introduced?

 

[angel34]

I copied the function in a new module iand data in Column A and B. Now, with the formula in C1, the code returns a blank cell.

 

[Mike Szczesny]

Can you use this? For the purposes of brevity, I did not create a formula to select unique dates. So, I selected one date. Assume C1 contains 1/2/17. In C2 I have the formula =concatenate(transpose(a1:a8=c1,b1:b8&" , ")). Before you hit enter, you need to highlight the transpose(...... all the way to the last 2nd last ) in formula. Hit F9 and remove the the { and } in the formula. Hit enter. You will see some FALSE results. Then add on a =substitute formula. =Substitute(formula,"FALSE","") will remove the FALSE answers.

[TABLE="width: 201"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Text[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD]a[/TD]
[TD]a , c , d , f , [/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD]e[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[angel34]

I regret very much the delay in replying since I thought that the thread is closed and did not check back.. Unfortunately, it is not possible to implement this solution in my project since the file gets transmitted to not so tech savvy users. Thanks any way for the reply. Regards