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.

 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top