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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
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.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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)))
 

Darts101

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384

ADVERTISEMENT

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

Darts101

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top