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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top