Combine Excel cells into 1 cell based on date in next column

I've exported some log data into a spreadsheet. It's 2 columns:

Entry 1 |  29/5/2021
Entry 2 |  29/5/2021
Entry 3 |  28/5/2021
Entry 4 |  28/5/2021
Entry 5 |  28/5/2021

i.e. each entry occupies a single cell, with multiple entries over multiple rows per each date.

How do I combine all entries into a single cell on 1 row for each date?

I know this isn't an easy task, so help would be appreciated.

Solution 1:

If you have Office 365, you can use TEXTJOIN, UNIQUE and FILTER functions.

Note that I used a Table with structured references in the formulas as this makes the references auto-adjust. You can use regular addressing if you prefer.

eg:

J2: =UNIQUE(Table6[Column2])
K2: =TEXTJOIN(";",TRUE,FILTER(Table6[Column1],J2=Table6[Column2]))

Select K2 and fill down as far as needed

Edit:

If you don't have Office 365, you can obtain your results in Power Query which is available in Windows Excel 2010+

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Read in the data
//change Table Name in next line to reflect actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry", type text}, {"Date", type date}}),

//Group by date and concatenate the Entries
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {
        {"Dates", (t)=> List.Accumulate(t[Entry],"",(state, current)=>
            if state = "" then current else state & ", " & current), type text}
        })
in
    #"Grouped Rows"

Solution 2:

all entries into a single cell on 1 row for each date Assuming the cells of your shared table is A1:B5

In C1 put : =A1 In C2 put : =IF(B2=B1,C1&"|"&A2,A2) and drag downwards.

In D1 put : =IF(B1=B2,"",B1) and drag downwards.

In E1 put : =IF(B1=B2,"",C1) and drag downwards.

In F1 put : =ROW()-COUNTBLANK($E$1:E1) and drag downwards.

In G1 put : =IFERROR(INDEX(D:D,MATCH(ROW(),F:F,0)),"") and drag downwards.

In H1 put : =IFERROR(INDEX(E:E,MATCH(ROW(),F:F,0)),"") and drag downwards.

That shall give you the combined cells per different date.

Please share if it works/not. ( :

Solution 3:

Copy Column B to the New Column and go to Data- Remove Duplicates:

Then run this array formula end with Ctrl+Shift+Enter:

=TEXTJOIN(",",1,IF(B1:B5=E1,A1:A5,""))