Formula for combining data

willhelm654

New Member
Joined
Jan 11, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have a table that I woul dlike to use to forcast jobs by date and I want to combine all the text from cells in column A when the dates in Column B are the same into Column C. See table below.

I have tried playing around with =IF($B8=$B9,CONCATENATE(Table1[@Description], $A9),"") but it only combines two cells and I cant think of a way to make it do all.

Thanks for your help,

1590586887351.png
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows
If your Excel version is up to date in your profile, then TEXTJOIN is not an option. I'll show two ways to do this...one with TEXTJOIN which requires Excel 2016? or newer, I believe. The other uses a User-Defined Function (UDF) sourced from here (How to easily concatenate text based on criteria in Excel?) and listed here:
VBA Code:
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Update 20150414
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
To install this VBA code, on your worksheet, right click on the worksheet tab (name) and select View Code, then Insert>Module and paste this code listing, then File>Close and Return to Excel. The UDF should then be available to you, and the syntax is CONCATENATEIF(range where some condition is to be satisfied, condition, range corresponding to true condition, delimiter).

In col. C on the sheet below, I've applied TEXTJOIN to the condition where the date in col B (on that row) is found anywhere else in column B, so you'll see a number of repeated entries. Note that this is an array formula, confirmed with Ctrl-Shift-Enter. A cleaner way to do this is to construct a unique list of dates with no duplicates (col. D), and then apply TEXTJOIN (see col E). And if TEXTJOIN is not an option, then the UDF can be used, as shown in col. F:
Book3
ABCDEF
1DescriptionCal Due DateUsing TEXTJOINUnique List of DatesUsing TEXTJOINUsing ConcatenateIF UDF
2alpha5/28/2020alpha5/28/2020alphaalpha
3bravo5/29/2020bravo5/29/2020bravobravo
4charlie5/30/2020charlie5/30/2020charliecharlie
5delta5/31/2020delta5/31/2020deltadelta
6echo6/1/2020echo, foxtrot, hotel, india6/1/2020echo, foxtrot, hotel, indiaecho, foxtrot, hotel, india
7foxtrot6/1/2020echo, foxtrot, hotel, india6/3/2020golf, julietgolf, juliet
8golf6/3/2020golf, juliet6/4/2020kilokilo
9hotel6/1/2020echo, foxtrot, hotel, india  
10india6/1/2020echo, foxtrot, hotel, india  
11juliet6/3/2020golf, juliet  
12kilo6/4/2020kilo  
willhelm654
Cell Formulas
RangeFormula
C2:C12,E2:E8C2=TEXTJOIN(", ",TRUE,IF($B$2:$B$12=B2,$A$2:$A$12,""))
D2:D12D2=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$12),0)),"")
F2:F12F2=CONCATENATEIF($B$2:$B$12, D2, $A$2:$A$12, ", ")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,055
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,893
Messages
5,621,486
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top