Dynamic Range Expansion (No VBA)

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
Names
John
Sam
Jessica

<tbody>
</tbody>


I want my result as follows.
Matches
John Vs Sam
John Vs Jessica
Sam Vs Jessica

<tbody>
</tbody>


If I add one more data:

Names
John
Sam
Jessica
Peter

<tbody>
</tbody>


I want my result updated automatically as follows.
Matches
John Vs Sam
John Vs Jessica
John Vs Peter
Sam Vs Jessica
Sam Vs Peter
Jessica Vs Peter

<tbody>
</tbody>


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

 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Without VBA, I think it'll be easiest without formulas. Using a query table.

If your data is on Sheet1, query defined by
Code:
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

Well-known Member
Joined
Jun 24, 2010
Messages
804
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,812

ADVERTISEMENT

is that what you want?

NamesCustom
JohnJessica vs John
SamJessica vs Mark
JessicaJessica vs Peter
MarkJessica vs Sam
PeterJohn vs Mark
John vs Peter
John vs Sam
Mark vs Peter
Mark vs Sam
Peter vs Sam

if yes you'll need PowerQuery to do this my way ;)
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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?

Code:
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.
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,812
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top