Please Help!! VLOOKUP AND COMBINATION FUNCTION

[Yossy]

Possible two-person teams that can be formed from 8 candidates (28) i.e
=COMBIN(8,2).

However, my question is Lets assume the list of all 8 candidates names' are
John, Dustin, Mary, David, Peter, Rossy, Liz and Mike. How do I get the list
of all the two-person team that will total 28. All help will be appreciated.

I was thinking of using the vlookup together with the COMBIN function but
can't figure it out. Let me know if there is a better way to acheive this.
Thanks

 

[Mike H]

Hi,

try this

On your worksheet create a named range called "MyMates" No quotes and enter
your names in the range
Right click the sheet tab - view code and paste this in

Sub stantial()
Count = 0
For i = 1 To Application.WorksheetFunction.CountA(Range("myMates"))
For j = i + 1 To Application.WorksheetFunction.CountA(Range("MyMates"))
Name1 = Range("MyMates").Cells(i, 1)
Name2 = Range("MyMates").Cells(j, 1)
Count = Count + 1
If Name2 <> "" Then
Cells(2 + Count, 3) = Name1
Cells(2 + Count, 4) = Name2
End If
Next j
Next i
End Sub


Mike

 

[Mike H]

The if bit isn't necessary

Sub stantial()
Count = 1
For i = 1 To Application.WorksheetFunction.CountA(Range("myMates"))
For j = i + 1 To Application.WorksheetFunction.CountA(Range("MyMates"))
Name1 = Range("MyMates").Cells(i, 1)
Name2 = Range("MyMates").Cells(j, 1)
Cells(0 + Count, 1) = Name1
Cells(0 + Count, 2) = Name2
Count = Count + 1
Next j
Next i
End Sub

Mike

 

[Yossy]

Thanks Mike
I set the range and copied the code right then pressed F5 but it gave me
this error "Range" of Object "- worksheet" failed. I tried both codes. Please
help me

 

[Susan]

did you change the name of your spreadsheet as mike indicated????? if
you didn't, that would cause the error you describe.
susan

 

[Susan]

my apologies - misread range to sheet.........
the error you describe comes from the macro looking for something
specific that it can't find. maybe check your range name again.
susan

 

[Mike H]

Yossy,

It will works as worksheet code or in a general module but in a general
module it will write the list to the active worksheet. I suggest you right
click the sheet tab, view code and paste it in there.

The only way I can replicate the error is if the named range has been given
an incorrect name. Ensure you have you list of name in the named range call -
MyRange -
and it should work. Use the second version, it's tidier.

Mike

 

[Yossy]

Ooh sorry mike, the first code worked i.e(the IF code). I didn't set my range
properly at first that is why I got errors.

Another question. I also have 4 vertical bars each bar with High and Low
points. I will like to draw out the 2 possible combination of comparison of
the High and Low points of the bars. I used the COMBIN function =COMBIN(16,2)
= 120possibilities (Not sure if right)

E.g Bar49 Bar50 Bar51 Bar52
H9 H7 H12 H9
L3 L1 L7 L1

I want the code to compare all bars high and low points and display all the
possible comparison of High to High, High to Low, Low to Low and Low to High.
The result will be displayed like the following:
Bar49 High > Bar50 High (i.e H9 is higher than H7)
Bar49 Low > Bar50 Low
Bar49 High < Bar51 high
Bar49 High = Bar52 high
Bar50 High = Bar51 Low

etc. Please help me set this function. I might be wrong with the combination
however, I have oulined how the result should be. Many Thanks

 

[Harlan Grove]

Possible two-person teams that can be formed from 8 candidates (28) i.e
=COMBIN(8,2).

However, my question is Lets assume the list of all 8 candidates names'
are John, Dustin, Mary, David, Peter, Rossy, Liz and Mike. How do I get
the list of all the two-person team that will total 28. All help will be
appreciated.

I was thinking of using the vlookup together with the COMBIN function but
can't figure it out. Let me know if there is a better way to acheive this.

You don't need VBA for this. Anyone suggesting you do or that it's easier
simply doesn't know how to use formulas effectively.

If your list of names were in a range named Persons, let the first team
would display in cells A3 and B3 with the other teams below it using the
following formulas.

A3:
=T(Persons)

B3:
=INDEX(Persons,2)

A4:
=IF(B3<>INDEX(Persons,COUNTA(Persons)),A3,
INDEX(Persons,MATCH(A3,Persons,0)+1))

B4:
=INDEX(Persons,MATCH(IF(A4=A3,B3,A4),Persons,0)+1)

Select A4:B4 and fill down into A5:B30.