Forum Discussion

mfseingim's avatar
mfseingim
Copper Contributor
Sep 10, 2023

How to convert more colums into a single one separated by ";"

Hi to everyone, here's an example of what I want to do:   I have three columns witch each three rows:   a1         b1          c1 a2         b2          c2 a3         b3          c3   I want...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 10, 2023

mfseingim 

I take the solution a bit further than Patrick2788

Rather than creating a fill-down formula with relative referencing, I aim to create a single function that returns the entire list.  Naming your data, 'dataArray', the processing may be done row by row using

= BYROW(dataArray, Joinλ)

where Joinλ is a slightly more restrictive Lambda version of TEXTJOIN

= LAMBDA(x, TEXTJOIN(";", , x))

I would upload a picture but, apparently I can no longer be allowed to upload pictures.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 10, 2023

    PeterBartholomew1 

    To play with Python for Excel

    import numpy
df = xl("dataArray", headers = False )
rows = numpy.matrix(df).tolist()
list(map(lambda x : '; '.join(x), rows) )

    Result is the same.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Sep 10, 2023

      SergeiBaklan 

      I have still to come to terms with this (I need to put some time aside).  Does the access to Python libraries augment the world of spreadsheet formulas or replace it?  For a hard-core Python programmer, I guess it is the latter, but do they need Excel and the worksheet environment.

      I can see some places where rolling one's own formulas is verging on being too difficult (extracting eigenvalues for example) and a well-written library routine would inspire greater confidence.  At the moment, though, I have so much more from the Excel formula language, particularly as it includes Lambda functions, than I might ever have dreamt of having to hand.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 11, 2023

        PeterBartholomew1 

        I think Python on Excel will have quite narrow niche. Libraries is definitely a plus. The rest - I'm not sure. Depends also on pricing model. Python for Power BI was introduced at least an year ago, but I don't see it becomes very popular in that environment.

        But why don't to play with new tool.

Resources