Dynamic Range Expansion (No VBA)

[sooshil]

Hi

I want to dynamically expand my data as follows without using VBA.

I have text data in a single column. There is no empty cells in between the data but the number of rows having data can vary.
[TABLE="class: cms_table_grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]Names[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want my result as follows.
[TABLE="class: cms_table_grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]Matches[/TD]
[/TR]
[TR]
[TD]John Vs Sam[/TD]
[/TR]
[TR]
[TD]John Vs Jessica[/TD]
[/TR]
[TR]
[TD]Sam Vs Jessica[/TD]
[/TR]
</tbody>[/TABLE]


If I add one more data:

[TABLE="class: cms_table_grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]Names[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want my result updated automatically as follows.
[TABLE="class: cms_table_grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]Matches[/TD]
[/TR]
[TR]
[TD]John Vs Sam[/TD]
[/TR]
[TR]
[TD]John Vs Jessica[/TD]
[/TR]
[TR]
[TD]John Vs Peter[/TD]
[/TR]
[TR]
[TD]Sam Vs Jessica[/TD]
[/TR]
[TR]
[TD]Sam Vs Peter[/TD]
[/TR]
[TR]
[TD]Jessica Vs Peter[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be highly appreciated. But, again, only Formulas, NO VBA.
Thank you.

 

[Fazza]

And they're not as old as me‼

I must find the time/interest to start using Power Query on day.

Keep promoting it, Sandy. Hope you have more success than I have with MS Query (promoting, that is).
Both awesome tools and too little known.

As I like to say, a little SQL can do a lot of heavy lifting.

 

[sandy666]

SQL? 70's
Honestly, Power Query is the successor of MS Query but it is known that MS is not too fast in implementing anything so PQ took a long time so I will not write anything about promotion, it's a bad for health

 

[sooshil]

Thank you so much all of you for this nice conversation.
Yeah, the second query of Fazza solves my problem exactly as I wanted to. Thank you.

God bless you.