why doesn't excel want me to combine a range into one cell?

**glupaka** · 02-16-2019, 06:08 AM

hello,
i want to combine a range with a dynamic address and eventually filter out unique values into a list all in one cell.

for example:
a1:a5 contain: john, bob, john, lucy, bob
i want a formula in b1 to extract the values from the range a1:a5, list them with commas and eventually to omit the duplicates - i know this last part is available as an excel function in end results but i want this in an interactive formula since i will be using this for a large number of ranges, so no - combining multiple fields one by one with & will not help

it is flabbergasting that this is so difficult to achieve and find information about since i doubt this isn't a common necessary function among others as well

thanks

**AliGW** · 02-16-2019, 06:10 AM

Welcome to the forum! You may be flabbergasted, but you are working with an old tool! This would be easy with Power Query in newer versions of Excel.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**glupaka** · 02-16-2019, 09:17 AM

thanks for the reply, i attatched a file portraying the example and desired results

**Bo_Ry** · 02-16-2019, 10:47 AM

You need Excel 365 with Textjoin or UDF Textjoint

Please try with Ctrl+Shift+Enter
=TEXTJOIN(",",,IF(FREQUENCY(MATCH(A3:A10&"",A3:A10&"",),ROW(A1:A10)),A3:A10&"",""))

**glupaka** · 02-18-2019, 08:55 AM

i am still very baffled, but since there is apparently no simple way to achieve this result i've resorted to the & method with dynamic ranges.

**AliGW** · 02-18-2019, 09:35 AM

Perhaps if you express your bafflement to Microsoft ...

why doesn't excel want me to combine a range into one cell?

LinkBack

Thread Tools

Rate This Thread

Display

why doesn't excel want me to combine a range into one cell?

Re: why doesn't excel want me to combine a range into one cell?

Re: why doesn't excel want me to combine a range into one cell?

Re: why doesn't excel want me to combine a range into one cell?

Re: why doesn't excel want me to combine a range into one cell?

Re: why doesn't excel want me to combine a range into one cell?

Thread Information

Users Browsing this Thread

Similar Threads

Count cell range that doesn't contain specific strings or is empty

[SOLVED] For Each cell In Range.SpecialCells(xlCellTypeBlanks) doesn't process every cell.

VBA Find Dynamic Range based on Cell Borders then combine to one cell

Pull Keywords from cell range and combine in single cell

[SOLVED] Combine Cell Contents with a Named Range

Reference Cell as range().value doesn't work

Use a function in a chart range so it doesn't include a cell if blank?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions