Concatenate with index match?

[shadedlight]

Hello,

I have a workbook that has three columns (A, B, C). I think I'm looking to use the Index Match as well as the Concatenate function. What I would like to accomplish is located in column D & E.

Any help would be appreciated. Thank you.

 

[herofox]

hi,you can to see the file
elso yo can watch this video
How to Combine (Concatenate) Data from Multiple Rows into One Cell in Excel
or ,you can try bt this
How to concatenate rows into one cell based on group in Excel?

 

[herofox]

hi,you can to see the file
elso yo can watch this video
How to Combine (Concatenate) Data from Multiple Rows into One Cell in Excel
or ,you can try bt this
How to concatenate rows into one cell based on group in Excel?

 

[shadedlight]

Thank you herofox,

On the file you uploaded in column E 'one way' is only accounting for two of the entries in column C. For Acute there should be 4 entries total.

Also, on the third way is it possible to give each entry a new line in the cell so they appear like this:

ou shi jing
ji tou shi
lian shi
Middle notes

Thanks again

 

[GraH - Guido]

Hi shadedlight, What you want to accomplish is very easily done with Power Query (Get & Transform).

1. Load range into PQ (mouse operation)
2. Group By Column2; using advanced and select "All Rows" (mouse operation)
3. Get the first Column out of the created SubTables to generate a list (a formula you need to type)
4. Extract the values with a custom separator "Line Feed" (mouse operation)
5. Load to excel (mouse operation)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"All", each _, type table [Column1=text, Column2=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "List", each Table.Column([All],"Column1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All"})
in
    #"Removed Columns"

 

[shadedlight]

Thank you G.,

That's exactly what I was looking to do!

I'm attempting to update column 1 on sheet one and then refresh the sheet but there is an error.

Data --> Refresh all

The data connection can't be refreshed, as this type of data connection is not supported in this version of Excel.

Following data range failed to refresh:
• ExternalData_1
Continue to refresh all?

Security warning: Data connectoins have been disabled.

 

[GraH - Guido]

Hi again, it simply works with your file on my pc.
Normally you can enable data connections


It should then work on refresh.

 

[shadedlight]

I did enable the content but received these messages.

 

[GraH - Guido]

By any chance, are you using Excel for MAC?

 

[shadedlight]

Yes, excel for MAC

 

[GraH - Guido]

Then I'm afraid my solution isn't for you...

 

[shadedlight]

Thank you for all your suggestions anyway. Is there any chance you can upload the file that has the updated sheet?

 

[GraH - Guido]

That's no hassle. Here it is.