Excel - Combine multiple columns in a single cell

Vandroy

New Member
Joined
May 2, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,

I'm having an issue with sorting and making a list of data from two columns. In this example let's say we have 4 types of products (AAA,BBB,CCC,DDD) with various quantities like in picture below.
1619951372281.png

Is it possible to filter through each product, sum up the quantities and list it out into a single cell where it would be divided by a ; mark. For example it would look like this.
1619951657169.png

Where the end result is in D22 cell. If there is a thread where a similar issue was resolved, could you please link it, I have been unable to find a solution.

Thank you in advance and kind regards
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Something like this maybe?
Book1 (version 1).xlsb
ABCD
1QuantityProduct
224AAA74*AAA; 12*BBB; 72*CCC; 72*DDD
33BBB
42BBB
514CCC
622AAA
71DDD
844CCC
95DDD
1024AAA
113AAA
122BBB
1314CCC
1422DDD
151AAA
1644DDD
175BBB
Sheet8
Cell Formulas
RangeFormula
D2D2=TEXTJOIN("; ",1,SUMIF(B2:B17,UNIQUE(B2:B17),A2:A17)&"*"&UNIQUE(B2:B17))
 
Upvote 0
Thanks for the quick reply. When I try to use this formula I get the error that there's a problem with the formula. I then tried changing all the , into ; but it gives me back #NAME? error.
Is there a way around it or am I approaching it wrongly?
 
Upvote 0
If you are getting a #NAME? error then it means that your version of excel doesn't have all of the functions used in the formula. As your forum profile shows that you are using office 365, I based the formula on that. Excel 2019 does not have the UNIQUE function which the formula relies on.

Without the UNIQUE function it gets very messy. This uses functions from excel 2019, but that does not mean that it will work correctly.
It must be array confirmed with Ctrl Shift Enter.
Excel Formula:
=TEXTJOIN({"*","; "},1,CHOOSE({1,2},IFERROR(1/(1/SUMIF(B2:B17,IF(MATCH(B2:B17,B2:B17,0)=(ROW(B2:B17)-ROW(B2)+1),B2:B17,""),A2:A17)),""),IF(MATCH(B2:B17,B2:B17,0)=(ROW(B2:B17)-ROW(B2)+1),B2:B17,"")))
For versions of excel older than 2019, this will not be possible with a single formula.
 
Upvote 0
Or try:

=TEXTJOIN(";",1,IF(MATCH(B2:B17,B2:B17,0)=ROW(B2:B17)-1,SUMIF(B2:B17,B2:B17,A2:A17)&"*"&B2:B17,""))

Enter with Ctrl + Shift+ Enter.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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