Count and Lookup?

[Darts101]

Name Draw
RICK 1
DAN 4
TONY 5
BRANDON 3
MIKE 2
CARL 6

I have this list of names (Column A) with a random number (Column B) assigned to each person. The quantity of names could be different and the row the Draw numbers are located could be different. I need to count how many names are present and then bring back two names with the 1 & 2 combined, 3 & 4 combined and so forth.

Rick & Mike
Brandon & Dan
Tony & Carl

Any help will be greatly appreciated.

 

[Claymationator]

This isn't the most elegant solution, but if you can put in 2 helper columns you could do something like this:

Excel Workbook
	A	B	C	D	E	F
1	Name	Draw		Helper Columns		Joined Names
2	RICK	1		1	2	RICK & MIKE
3	DAN	4		3	4	BRANDON & DAN
4	TONY	5		5	6	TONY & CARL
5	BRANDON	3
6	MIKE	2
7	CARL	6
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F2	=INDEX($A$2:$B$7,MATCH($D2,$B$2:$B$7,0),1)&" & "&INDEX($A$2:$B$7,MATCH($E2,$B$2:$B$7,0),1)
F3	=INDEX($A$2:$B$7,MATCH($D3,$B$2:$B$7,0),1)&" & "&INDEX($A$2:$B$7,MATCH($E3,$B$2:$B$7,0),1)
F4	=INDEX($A$2:$B$7,MATCH($D4,$B$2:$B$7,0),1)&" & "&INDEX($A$2:$B$7,MATCH($E4,$B$2:$B$7,0),1)
D3	=E2+1
D4	=E3+1
E3	=D3+1
E4	=D4+1

Hope that helps.

 

[AlphaFrog]

Excel Workbook
	A	B	C	D
1	Name	Draw		Combo
2	RICK	1		RICK & MIKE
3	DAN	4		BRANDON & DAN
4	TONY	5		TONY & CARL
5	BRANDON	3
6	MIKE	2
7	CARL	6
...

Cell Formulas
Range	Formula
D2	=IF((COUNTA(A:A)-1)/2A1), "", INDEX(A:A,MATCH(ROW(A1)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A1)*2,B:B,0)))
D3	=IF((COUNTA(A:A)-1)/2A2), "", INDEX(A:A,MATCH(ROW(A2)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A2)*2,B:B,0)))
D4	=IF((COUNTA(A:A)-1)/2A3), "", INDEX(A:A,MATCH(ROW(A3)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A3)*2,B:B,0)))
D5	=IF((COUNTA(A:A)-1)/2A4), "", INDEX(A:A,MATCH(ROW(A4)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A4)*2,B:B,0)))
D6	=IF((COUNTA(A:A)-1)/2A5), "", INDEX(A:A,MATCH(ROW(A5)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A5)*2,B:B,0)))
D7	=IF((COUNTA(A:A)-1)/2A6), "", INDEX(A:A,MATCH(ROW(A6)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A6)*2,B:B,0)))

 

[Darts101]

Claymationator
Looks like it would work but would rather not use helpers unless absolutely needed. Thanks for your reply.

AlphaFrog
Your solution works well. I need to add another factor into it though. In the event that there are more than 6 names in column A it would leave it blank. I am basically entering this to a different cell if there are not 6. Would COUNTIF work instead of the COUNTA.

 

[AlphaFrog]

Just copy the formula down as many cells as you need. If can handle more than six names.

 

[Darts101]

It brings back the values no matter how many names are entered. I need it to bring the names back only if there are 6, if there are more or less than 6 names then I need them to be blank.

To clarify a little of what I am trying to do. The names will be on a different sheet than where entered and the number of entries decides which sheet they will be entered on.

 

[AlphaFrog]

Cell D2
=IF(OR(COUNTA(A:A)-1<>6, ROW(A1)*2>6), "", INDEX(A:A,MATCH(ROW(A1)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A1)*2,B:B,0)))

Change the six to the number of entries you want to test for.

 

[Darts101]

Perfect, thanks for the help with this. I was trying to use a countif statement and could not get it right. I can never get the index/match to work right either unless I have a starting point.

Thanks again for the help.