Dynamic Range Expansion

[sooshil]

Hi

I want to dynamically expand my data as follows.

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 a dynamic 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 a dynamic result 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. Thank you.

 

[MickG]

With your data in column "A", Results in column "B".

[COLOR="Navy"]Sub[/COLOR] MG11Feb47
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("B1") = "Matches"
c = 1
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
    [COLOR="Navy"]For[/COLOR] nn = n + 1 To Rng.Count
        c = c + 1
        Cells(c, "B") = Rng(n) & " Vs " & Rng(nn)
    [COLOR="Navy"]Next[/COLOR] nn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sooshil]

Hi Mick,

This works fine to get the result I want. But, this is not as dynamic as I want.
After every time we enter a new data, we need to run this code to the result to be updated.
But, my requirement is as soon as I enter a new data, my result list need to be updated automatically. Formulas are preferred than a VBA.
Any way thanks for your time.

 

[MickG]

Try this "change event"
Enter code in sheet Module
Right click sheet tab>, Select "View Code"> vbwindow appears> Paste code into this window.
Change results by adding/Deleting to column "A".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.EnableEvents = False
    [COLOR="Navy"]If[/COLOR] Target.Column = 1 [COLOR="Navy"]Then[/COLOR]
        Columns("B:B").ClearContents
        Range("B1") = "Matches"
        c = 1
        [COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
            [COLOR="Navy"]For[/COLOR] nn = n + 1 To Rng.Count
                c = c + 1
                Cells(c, "B") = Rng(n) & " Vs " & Rng(nn)
            [COLOR="Navy"]Next[/COLOR] nn
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sooshil]

Thank you so much Mick.
This is what I wanted. This works absolutely perfect.

Since, I don't know even a little about VBA, this is just a solution for me. If it was by using formulas, I would have learnt a lot.
Any way thank you again.
If you have time, please give me a solution using formulas.

 

[MickG]

You're welcome