Forum Discussion
Transform several rows into one muli-line cell
It all depends on the version of Excel you are using. For example, with the latest versions of Excel 365 the following gives the bare bones of a formula solution that sits in one cell and creates the entire output array in one
= LET(
distinctAlbum, UNIQUE(Album),
details, BYROW(distinctAlbum,
LAMBDA(a,
LET(
t, FILTER(Track&", "&Artist, Album=a),
TEXTJOIN(CHAR(10),,t)
)
)
),
CHOOSE({1,2}, distinctAlbum, details)
)Without Lambda functions, you would work on one album at a time and fill down. Without LET, you nest the formula, without FILTER, the going gets tough. Perhaps FILTERXML.
By the time you get to that point, switching to Power Query would probably be better.
PeterBartholomew1 Very many thanks for your quick reply. I'm using 365, so your solution should have the best chance.
I always feel uncomfortable pasting code of which I have absolutely no understanding, so I'll first search for some Idiot's basic VBA guide to gain a bit more confidence. But I know the sooner I do this, the better (and I'll keep you posted....)
Meanwhile -- just a thought -- I have some basic understanding of regular expressions, which don't seem to be handled directly in Excel, so I was wondering whether, if I open the sheet in LibreOffice (whose search/replace includes fussy but useable RegX) there might be a way to achieve my desired result within a framework I can more easily get my head around.In addition to clearly being a VBA Master, are you also a RegX Wizard?
First, I had better address a misconception! Believe it or not, the formula I used was not VBA; it was a regular Excel formula, entered directly to a worksheet cell!
Riny_van_Eekelen implemented the formula as well as his Power Query solution, so you might like to take a look at his workbook.
I have some experience of RegEx either from VBA scripting library (accessed as a UDF) or through the use of Charles Williams's paid FastExcel add-in. Despite that I would describe it as somewhat outside my comfort zone!
- PeterBartholomew1, Riny_van_Eekelen, SergeiBaklan, Willy Lau, amit_bhola:
If you'll excuse the Harry Potter reference, I feel like a new student at Hogwarts College suddenly finding himself overawed in the presence of powerful Wizard Masters, and I'm amazed and grateful for the time and trouble they are prepared to take to help.
I use often use spreadsheets (I started with Supercalc on CP/M), but only in the most superficial way, with very little formula use, so unfortunately I'm totally out of my depth with the solutions presented.
I had never heard of Power Query, UDF etc., and didn't even know that to view an Excel formula properly indented, all I had to do was drag the bottom of the formula bar down.)
It will take me quite a while to begin to assimilate what lies behind the unfamiliar spells the Wizards are suggesting, so I'm just taking this opportunity to thank you all.
PeterBartholomew1: I'm not sure where to paste your formula -- if I paste it into the next free column to the right in my sheet, I just get #NAME?. Right now, the formula is just a mystery black box to me, but I don't understand why it doesn't directly reference any column letter or heading.
PeterBartholomew1 Impressive as ever, though funny that you mention PowerQuery as a last resort, where I would instinctively go for PQ to begin with and choose complex formulae only if I have to. Attaching a file with both PQ and your formula (slightly altered to work with the structured table in my example).
It's probably something that is influenced as much by the comfort zone of the programmer as the technical demands of the problem. I do like PQ for ETL tasks but not so much when the data is already loaded in Excel.
You mention complicated formulae but once I have to switch to the Advanced Editor in PQ, it doesn't feel very 'low code'. I did take a quick look at creating a PQ solution but made a rookie error of Grouping first, which left me the task of trying to merge the rows within each embedded table.
I approve of your conversion to structured references, though occasionally I add a further layer of indirection by introducing a concise defined name to refer to a long structured reference. What I don't use is A1-style referencing. Having described it as "an abomination born of idleness that has no place in any computational environment", I can't help feeling that there would be an element of hypocrisy for me to then proceed to employ it!
That's never ended discussion what to use: formulas vs PQ vs DAX & data model. Or formulas & PQ & DAX with data model. From my point of view that's the only what makes the decision - how critical is the using of Refresh All.
The rest is solvable and mostly personal preferences. PQ has rich library could be used from UI. On the other hand DAX has lot of patterns and I believe it'll be lambda-based libraries and/or patterns.
