Forum Discussion
Transform several rows into one muli-line cell
I have to work on a selection of about 4,000 song titles from about 250 albums.
How do I automatically transform this:
into this:
I'm in my 70s, and never dared to mess with VBA -- but if that's the only way, I guess there always has to be a first time...
10 Replies
JanPlett , there are elegant, dynamic (auto-scalable) and robust ways by using newer techniques / advanced formulas already described. Or else, one can go step by step using helper columns. This technique uses smaller formulas one at a time. An example file is attached.
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 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!
