I have a big excel list, and I want to merge, the content of multiple cells, comma separated, in a single cell.
But ...
-the number of these content-cells are unknown.
-these content-cells also hold duplicate values, which also need to be sorted out.
Unfortunatly Iam no Excel-Expert, and also dont know VBA.
I guess something like this should be possible using VBA.
If its possible at all with formulars, I still cant answer that.
I tried to find some answers about this over the last days, all over the net. But I cant seem to find any information which could help me here with this special case. Maybe I use the wrong searchterm here.
Let me try to explain it in more detail:
A "dataset" in this list has multiple rows.
Between each "dataset" there is an empty row.
The number of rows can vary. (see example)
Iam trying to get a comma separated list, of all values, up to the next empty row, without any duplicates.
| What I have | What I want to achieve |
|---|---|
| < blank > | 15x15cm, 20x20cm, 30x30cm |
| 15x15cm | 15x15cm |
| 15x15cm | 15x15cm |
| 15x15cm | 15x15cm |
| 20x20cm | 20x20cm |
| 20x20cm | 20x20cm |
| 30x30cm | 30x30cm |
| < blank > | 10x10cm, 15x15cm, 20x20cm, 30x30cm |
| 10x10cm | 10x10cm |
| 15x15cm | 15x15cm |
| 20x20cm | 20x20cm |
| 30x30cm | 30x30cm |
| 30x30cm | 30x30cm |
| ... | ... |
CodePudding user response：
=IF(ISBLANK(A1),TEXTJOIN(", ",TRUE,UNIQUE(INDIRECT(ADDRESS(MATCH(TRUE,ISBLANK(A1:$A$14),0) ROW(A1)-1,1)):INDIRECT(ADDRESS(MATCH(TRUE,ISBLANK(A2:$A$14),0) ROW(A1)-1,1)))),A1)
Formula to be pasted in B1 and dragged down. Keep in mind that this formula is written for dataset in A1:A13.
If used in other "place" need to change not only A1, A1:$A$14, ROW(A1), A2:$A$14, but also -1 in ROW(A1)-1
CodePudding user response：
The following approach doesn't require to use a 
The use of user LAMBDA function A() is to be able to use a RACONs functions or ranges like this: INDEX:INDEX inside an array function such as MAP. Check the @DavidLeal's answer from this question: MAXIFS doesn't work as expected invoked inside MAP using names from LET.
The main idea is to identify the index position of the blank cells in column A. The name b has such positions plus the n 1 for the last one. Then on each iteration of MAP function, we check for blank cell index position (s) and if so we identify start and end index positions for doing the concatenation, otherwise we return the corresponding value of column A (aa).