0

Let's say I am creating a master list of various students and classes they will be taking at a given time, and I want to automatically create a class roster in a separate sheet. Each class gets its own sheet, and as of now I am trying to use drop down list for the classes, then populate the rosters depending on what option is selected from the dropdown.

I have first name and last name in two separate cells, along with other information in other cells, and I want this to be copied over. I used if statements in the rosters, but the issue is it leaves blank rows, and this is less than ideal.

Here is an image of the master list I have created.

This is a sample formula I am using to populate the rosters. I have named each column in the master list to be its heading.

=IF(Book1!Science="Earth Science",First_Name,"")

When it populates the data, this is what happens.

This happens when two people in the same class are not adjacent in the master list.

How can I achieve my desired results?

EDIT

PivotTables are turning out in an undesirable format, as I would like each person to have a row and each data point its own column, even after following the instructions left in the comments.

Here is an image of the changes to the master list.

Here is an image of the PivotTable that resulted.

  • Can you add a pic with some sample data, showing the 'before' and 'after' views, so we understand what the desired result is? Also, what version of Excel do you have? – jeffreyweir Jul 19 '18 at 15:11
  • I am using Excel 2016, and the desired result is simply that I do not want that blank row to be there, but I want it done automatically. Thanks! –  Jul 19 '18 at 16:09
  • Ok. Any particular reason why you wouldn't use a PivotTable to do this? They are perfect for this type of thing, and much easier than formulas. See https://stackoverflow.com/questions/51211178/vba-to-copy-data-if-multiple-criteria-are-met/51214172#51214172 – jeffreyweir Jul 19 '18 at 16:21
  • PivotTables are not providing a desirable format, even after getting it somewhat tabular. Is there a way I can use a formula to move rows upwards if the one above it is empty? –  Jul 19 '18 at 17:52
  • Given you've got Excel 2016, you could use the new PowerQuery functionality to do this, under the Get & Transform tab. That lets you do incredible manipulations fairly easily, although you'll have to do some googling on PowerQuery to get started. Formulas aren't great at this kind of thing, although anything is possible. I don't have time right now to look further. – jeffreyweir Jul 19 '18 at 21:02

1 Answers1

0

Let me try.. I do it with 3 extra columns..

My sample data is : 

Name    Data
Test1   Sc
Test2   Bc
Test3   Sc
Test4   Bc
Test5   Bc
Test6   Bc
Test7   Bc
Test8   Bc
Test9   Bc
Test10  Bc
Test11  Sc
Test12  Bc
Test13  Sc
Test14  Bc

for other cells : F1 = "Sc" , E1 = , G1 = 1 , G2 = 2 , G3 = 3 , G4 = 4 , G5 = 5 , G6 = 6 , G7 = 7 , G8 = 8 , G9 = 9 , G10 = 10 , G11 = 11 , G12 = 12 .

also, in E2 type =IF(F2="",E1,E1+1) then drag it until E15,

lastly in H2 enter =IFERROR(INDEX($F$2:$F$15,MATCH(G2,$E$2:$E$15,0)),"") then drag it until H14.

This will be your results.

Change "Sc" in F1 cell to "Bc" and you'll see the list are updated.

Hope that helps. (:

Note: column G is just a running number, you can generate it by using row() function so we only need to extra columns instead of 3.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17