Order multiple texts in "row.cells" in columns

[vbadumb]

Order multiple texts in "row.cells" in columns as in the attached file.
Rows and columns can be upto 1000s of any cell length.

An example file with input and output
VBA3.xlsx



https://www.youtube.com/@alanelston2330/featured

[DocAElstein]

Hello vbadumb
excelfox is a place to further the knowledge of excel: it is for people to get excel help from other people.
So tell us a bit more about what you are trying to do and why. That might help us to give the best help
_..Alternatively, if you just want to post anonymous question, best try one of the larger forums in future , like
mrexcel.com
or
excelforum.com

---

Here is what you gave as sample data, and wanted results, and macro results: https://excelfox.com/forum/showthrea...ll=1#post15293
https://excelfox.com/forum/showthrea...ll=1#post15293
Here is a macro to get you started https://excelfox.com/forum/showthrea...ll=1#post15294 .

Here is a brief summary of what is going on in the macro that I have done for you
I capture your data from the first worksheet into an array, arrIn() = WsIn.Range("B1:F5").Value2
I loop through the header columns, For Clm = 1 To 5 Step 1 , ( and do nothing if any column headers are empty
If I have a header, then I note that in variable Itms
I then go down the data rows, For RwDta = 2 To 5 Step 1
If I don’t have any data in a row , I do nothing. Otherwise I try to fill a string, strOutB , with as much data as is in a cell. I continually add to that string. I separate that data with a line feed ( vbCr & vbLf ) . I could use any character , ( provided its one that doesn’t appear in any data ) , but the line feed is convenient because I could also view my data in a message box or in the immediate window.
In each loop, I can determine the number of times I need to have the header by the number of elements I get from an array made from strOutB split by the vbCr & vbLf
The header data wanted in column A of the output goes into a similar string, strOutA


Please let us know how you get on with it.


Alan

[p45cal]

You can also do this with Power Query (Get and Transform Data). See attached, table at cell D2 of the OutputB sheet. Right-click and choose Refresh to update.
It doesn't show anything from entirely blank columns.

[sandy666]

shorter version with Power Query
(I assumed your example was representative)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Find word"}, "Attribute", "Value"),
    Split = Table.ExpandListColumn(Table.TransformColumns(UOC, {{"Value", Splitter.SplitTextByDelimiter("| ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Sort = Table.Sort(Split,{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Find word"})
in
    RC

Find word	1Abc	2wer#	3smar	Column1	4chris		Attribute	Value
AP1k		AP1k, 6-9| AP1k, 10-13					2wer#	AP1k, 10-13
ForCome			ForCome, 13-19				2wer#	AP1k, 6-9
							2wer#	Double, 14-16
Double		Double, 14-16 | Double, 14-16| Double, 14-16	Double, 14-16| Double, 14-16		Double, 14-16| Double, 14-16		2wer#	Double, 14-16
							2wer#	Double, 14-16
							3smar	Double, 14-16
							3smar	Double, 14-16
							3smar	ForCome, 13-19
							4chris	Double, 14-16
							4chris	Double, 14-16

Blank columns are removed, if you want use headers of blank columns you'll need to add any single nullable data (eg. 0 or space) to this column in source table then replace it with null in the result table
btw. to use Power Query you will need XL2016 or higher or XL2010/2013 with PQ add-in