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]

Without VBA, I think it'll be easiest without formulas. Using a query table.

If your data is on Sheet1, query defined by

SELECT A.Names & ' Vs ' & B.Names AS [Matches]
FROM [Sheet1$] A, [Sheet1$] B
WHERE A.Names <> B.Names

Save the file, then you can create the query via ALT-D-D-N and follow wizard. There are other ways in new versions of Excel.
If you get a message about no visible tables, take the option to set system tables & then see worksheet names.
At last step of wizard take option to edit in MS Query & then change SQL as above.
When the data changes, refresh the query. Like a pivot table.

 

[YasserKhalil]

@Fazza
Can you give more details about the steps of how to apply this query?

 

[Fazza]

Sure. Which steps have you done? Where are you at?

 

[Fazza]

Google will find lots of help

https://analystcave.com/create-microsoft-query-excel-excel-query/

MS Query has its own help too.

 

[YasserKhalil]

Thanks a lot for the link. I have applied the SQL statement you provided but the results are different from the requested from the OP (12 result for 4 names) while he expects 6 results I think

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Names[/td][td][/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John[/td][td][/td][td=bgcolor:#E2EFDA]Jessica vs John[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sam[/td][td][/td][td]Jessica vs Mark[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jessica[/td][td][/td][td=bgcolor:#E2EFDA]Jessica vs Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mark[/td][td][/td][td]Jessica vs Sam[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Peter[/td][td][/td][td=bgcolor:#E2EFDA]John vs Mark[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]John vs Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]John vs Sam[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Mark vs Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Mark vs Sam[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Peter vs Sam[/td][/tr]
[/table]

if yes you'll need PowerQuery to do this my way

 

[Fazza]

Thanks, YasserKhalil

I was wrong - failed to exclude the half of the data that was the B-A pair of the A-B pair.

How is this revised version?

SELECT C.First & ' Vs ' & C.Second AS [Matches]
FROM (SELECT A.Names AS [First], B.Names AS [Second]
FROM [Sheet1$] A, [Sheet1$] B
WHERE A.Names < B.Names
ORDER BY 1, 2) C

There could even be instances of duplicates in the initial data, so you could add a DISTINCT to the inner-most SELECT to handle that. Untested - that is the second line would become
FROM (SELECT DISTINCT A.Names, ...

FWIW, this doesn't require power query. Works in all recent (20+ years) Excel versions.

 

[Fazza]

The site I linked to above in post #5 - and I didn't know the site til yesterday - has lots of excellent information on VBA.

The specific page on using MS Query comments on comparing MS Query & Power Query,

MS Query vs Power Query Conclusions

MS Query Pros: Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:

• Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
• You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query

MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:

• Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
• Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations

In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs…

 

[sandy666]

Ha ha ha
I will add one more thing: from version 2016 Power Query is built-in and it is not as old-fashioned as MS Query or VBA

Regards