You are here:

Excel/Pulling out names from an array and reorganizing them (the hard way)


Thank you for your time in reading this.  I don't have a problem it’s more of a puzzle to figure out because I know there is an easy solution already.
I have a sheet called Members, and in ColumnA there are 200 names; in ColumnB next to each name, I will be placing either the number 1, 2, 3, 4, 5, 6, 7, 8, 9 or 10.  Each number corresponds to an activity the person has selected. It is formatted like this because I am going to reference a physical sheet of paper with numbers next to names, and it is easy and quick to go down with my arrow keys and hit numbers as I see them on the paper.  In the end, only 20 people will have a 1, 20 will have a 2, 20 will have a 3, and so on.. .  I have created 10 more sheets, each one representing an activity.

The "puzzle" is: to get all the names with a 1 next to them (which is scattered about in ColumnA), onto the sheet for Activity1 in the first twenty rows of that sheet.  All the names with a 2 next to them should go to the first 20 rows in the Activity2 sheet, and so on...

Of course I could just sort ColumnB in the Members sheet numerically and then copy and paste the names over to the right Activity sheets… but that’s no fun.  I want to figure out a way to have the names sent to the sheets right when I enter each number in ColumnB on the Members sheet.

I have an idea of how to do it, but it is very sloppy, and probably way off base but here it goes:  I can get the first name next to a 1, next to a 2, next to a 3, and so on; into the first cells of each of the activity sheets using the below formula (this is for just ten names, but you’ll get the pattern)


(I'm locking the cells for copy and paste purposes later).  For 200 names I know I would have to have 200 “IF” arguments, which is a lot but copy and paste helps with that.  

For the next name (Row2 of a given activity sheet), my idea was to use the MATCH function to get the row number where the first name came from, and then use the same above formula, but inserting the MATCH argument for the numbers next to the $A$ and $B$ in the first IF argument.  The rest of the arguments would somehow have to have, for example, $A$(MATCH(($A$1,$A$1:$A$200,1)+1)), but of course this is already starting to breakdown because the cell won’t recognize MATCH($A$1,$A$1:$A$200,1) as equaling anything.

For every cell on the activity sheets (except in Row1), referencing the row where the name-above came from is important so that formula that finds the names starts off where it left off on a particular sheet.

I hope I have made this "puzzle" clear enough, and I hope it is worth your time to give a response, I would greatly appreciate it.  if you have any ideas on how to correct what I've started, or perhaps you know a much nicer/simpler/more sophisticated way to do what I'm trying to do, that would be awesome.

Thanks again,
-Jari \m/


in the sheet for the activity 1's, in A1 put this formula

A1: =INDEX(Members!$A:$A,SMALL(IF(Members!$B$1:$B$200=1,ROW(Members!$B$1:$B$200)),ROW($A1)),1)
Enter this with Ctrl+Shift+Enter rather than just Enter since this is an Array formula.

Now you can select A1 and drag fill it down to the 20th row.

Use the same formula in the 2 activity sheet, but change the
IF(Members!$B$1:$B$200=1   part of the formula to


again, enter with Ctrl+Shift+enter
then drag fill down.

Repeat for the other sheets.

The formula will return errors if no match to the activity number is found.  So if the Members sheet only has 10 people with 1 in column B, then the formulas in the 1 activity sheet in rows 11 to 20 will show error values.  

Tom Ogilvy

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.