Count and Lookup?

Darts101

New Member
Joined
Sep 9, 2009
Messages
29
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
This isn't the most elegant solution, but if you can put in 2 helper columns you could do something like this:
Excel Workbook
ABCDEF
1NameDrawHelper ColumnsJoined Names
2RICK112RICK & MIKE
3DAN434BRANDON & DAN
4TONY556TONY & CARL
5BRANDON3
6MIKE2
7CARL6
Sheet1
Excel 2010
Cell Formulas
RangeFormula
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.
 
Upvote 0
Excel Workbook
ABCD
1NameDrawCombo
2RICK1RICK & MIKE
3DAN4BRANDON & DAN
4TONY5TONY & CARL
5BRANDON3
6MIKE2
7CARL6
...
Cell Formulas
RangeFormula
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)))
 
Upvote 0
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.
 
Upvote 0
Just copy the formula down as many cells as you need. If can handle more than six names.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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