Excel column A same value and column B value combine in Column D

Question

Please help looking for the excel formula:
The column A have the same value, column B is qty, combine in text with '+' in column D, the column C is a,b,c, sample picture below, the Column D is in text.

Answer 1

This is quite an easy feat using a simple DAX measure.

There should be a build-in add-on called Power Pivot for you in ms Excel 2016. If you haven't already enabled it then have a look here. Power Pivot allows us to:

• Select your data (including headers);
• Click 'Add to Data Model' on the 'Power Pivot' tab. A pop-up should appear, you can close this for now;
• Under the same 'Power Pivot' tab, now click 'Measures'>'New Measure';
• Select the right table and give the measure a name that makes sense, for example 'TEXTJOIN';
• In the formula box apply the following formula: =CONCATENATEX(Table1,[Qty],"+"). Apply the appropriate tablename before clicking 'OK';
• In the same tab, now click 'Manage' and under the 'Home' tab in the pop-up window click 'PivotTable';
• Choose where to output the new table, drag 'Items' to rows and the measure you just created to values.

The output then, should look like:

Note: I removed 'Grand Totals'.

Answer 2

Try using the following formula:

---

=LET(
     _data, A2:B7,
     _items, TAKE(_data,,1),
     _uniqi, UNIQUE(_items),
     HSTACK(_uniqi, BYROW(_uniqi, LAMBDA(x, TEXTJOIN("+",1,IF(x=_items,TAKE(_data,,-1),""))))))

---

Or, Use GROUPBY() function:

=LET(
     x, GROUPBY(A2:A7,B2:B7,ARRAYTOTEXT,,0),
     HSTACK(TAKE(x,,1), SUBSTITUTE(TAKE(x,,-1),", ","+")))

---

Alternative way of writing the above function as mentioned by JvdV Sir. Simple & shorter version.

=GROUPBY(A2:A7,B2:B7,LAMBDA(x,TEXTJOIN("+",,x)),0,0)

---

• Using LET() function it becomes easier to read and define variables which can be used to avoid repeats of formulas.
• _items using TAKE() function get the first range from _data variable.
• Using UNIQUE() function extract the unique values from above.
• Using BYROW() to run a custom LAMBDA() calculations to join using TEXTJOIN() function with a delimiter + after checking whether x is equal to _items else return empty.
• Lastly, using HSTACK() combine both the arrays.

---

As per OP's comments:

Sorry, my ms office excel is ms office 2016 and it do not have the 'let' formula...

---

And this can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

---

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"Qty", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Items"}, {{"TextValue", each Text.Combine([Qty],"+"), type text}})
in
    #"Grouped Rows"

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

---

Also as per MSFT Documentations - Excel 2016 supports CONCAT() function that said then one can use the following:

---

=SUBSTITUTE(SUBSTITUTE(CONCAT("+"&IF(D2=A$2:A$7,B$2:B$7,"-")),"+-",),"+",,1)

---

Answer 3

I admire Mayukh's knowledge about recent Excel features, but I prefer a more "old-fashioned" approach, based on the following formula:

=TEXTJOIN("+";TRUE;IF(A$2:A$8=C2;B$2:B$8;""))

This is what it looks like in a screenshot:

I started working with the formula IF(A$2:A$8=C2;B$2:B$8;""), I saw this created a sequence and I decided to fit it into a TextJoin() function.

Beware: the IF()-clause contains an empty string in the ELSE-case, which is skipped, using the TRUE as the second parameter of the TextJoin() function.

Edit: what about TextJoin()?
As stated by JvdV, Excel 2016 does not support TextJoin() function, but this post contains a VBA UDF (User-Defined Function) TextJoin().