1

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
... ...
LWS-Mo
  • 180
  • 2
  • 9

2 Answers2

1

The following approach doesn't require to use a Volatile functions. In cell C2, put the following formula:

=LET(A, LAMBDA(A2:A14), n, ROWS(A()), s, SEQUENCE(n), f, IF(A()="", s,-1), 
  b, VSTACK(FILTER(f, f>0), n+1), MAP(A(), s, LAMBDA(aa, ss, LET(start, 
  XLOOKUP(ss, b, b, -1), IF(start<0, aa, LET(end, MIN(FILTER(b, b>start)),
  TEXTJOIN(", ",,UNIQUE(INDEX(A(),start+1):INDEX(A(),end-1)))))))))

Here is the output:

excel output

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).

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Thank you David for your Input. Unfortunatly I could not get your formular to work in my document. I even tried multiple times to rebuild your formular manually, but I always got some invalid errors and couldnt figure out what was wrong. Iam sure that this was entirely my fault though! – LWS-Mo Dec 07 '22 at 13:29
  • Thanks @LWS-Mo probably some excel version conflict, what is your excel version? share an editable link to your file, so I can take a look at it. I you can obviously. – David Leal Dec 07 '22 at 13:55
  • Iam using the german, Office365 version of Excel. So I had to "translate" some formulars. I did this manually and also tried out an Excel-Translator for this. I think the translation itself was not the problem, because I got no errors regarding the used formulars. Iam sorry I cant link the document directly, cause there maybe some sensible data. And to strip it down it would require some work which I cant do in the moment. – LWS-Mo Dec 08 '22 at 12:55
  • I see, I meant to share a mockup or sample file. Here you have a [link](https://1drv.ms/x/s!AlZxw2GG3C7Ih2Qc1fMi98qLj_uC?e=Iel0cK) to my sample file, so you can check it works. We are using the same version, so probably there is another reason. Would you share also the error you are getting? Thanks – David Leal Dec 08 '22 at 13:24
0

enter image description here

=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

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • Thank you, I was able to get the data I need based on your formular, and accepted your answer! I guess that Davids idea would be more flexible and would require less adjustment than this approach. But for my usecase your answer works great! – LWS-Mo Dec 07 '22 at 13:36