Sorting column data which is based on formulae including Blank cells

[syp1677]

Hello Gurus,
I am using Office 2016.
I want to have an array function to sort the data column which also has blank cells.
Example file attached.
It contains a Source column(based on the function of the source), using which I need data to be arranged as per Target.
Thanks in advance for your help.
Best Regards,
S

 

[p45cal]

Tenuous possible solution using Power Query (built-in to Excel 2016) in the attached at cell I1.
If you update the data in the table in column D, you'll need to right-click the result table and choose Refresh.

 

[syp1677]

Thank you p45cal,

Can this be possible with Array function?

Best Regards,
S

 

[p45cal]

Can this be possible with Array function?

Hard work, but I mentioned my solution was tenuous because I don't know the rules; I guessed.
What my solution did was:

• Take the source column,
• remove blank rows,
• determine which were headers and which were not by saying that if a hyphen was present it was not a header, and anything without a hyphen was a header
• matching non headers' text before the first hyphen, with headers' text before the first full stop
• put each header before its list of members and made a single list of all of them.

It could be done using different rules; for example, looking at the source column, we could assume the first cluster is the headers (it doesn't matter what's in them), then for the clusters below that, working from the top down, put each cluster inbetween each of the headers. With this system, it's only the position of the data and the presence of blank rows which determines what goes where. What's actually in each text is irrelevant.

I'm not prepared even to contemplate a formula solution without a very clear set of rules for how the final list should be put together, and even then I imagine it would be hard work and convoluted, so I might not be prepared to put the time in.

 

[bosco_yip]

Thank you p45cal,

Can this be possible with Array function?

Best Regards,
S

Or, try this simple formula solution with helper columns.

1] In Helper1, F2 formula copied down until blank:

=IF(D2="","",MID(D2,3,1))

2] In Helper2, G2 formula copied down until blank:

=IF(F2="","",0+(F2&COUNTIF(F$2:F2,F2)))

3] In Result, H2 formula copied down until blank:

=IFERROR(INDEX($D$2:$D$200,MATCH(SMALL(G$2:G$200,ROW(A1)),G$2:G$200,0)),"")

Remark: You can hide the helper columns as per your requirement.

 

[bosco_yip]

Or if you want a single formula solution, you may try this:

In F2 array (CSE) formula in F2 and copied down:

=IFERROR(INDEX($D$2:$D$200,MATCH(SMALL(IFERROR(MID(D$2:D$200,3,1)+(ROW(A$1:A$200)/1000),""),ROW(A1)),IFERROR(MID(D$2:D$200,3,1)+(ROW(A$1:A$200)/1000),""),0)),"")