Forum Discussion

Ian Tobin's avatar
Ian Tobin
Copper Contributor
Jun 20, 2017

Create one list of names combining three lists

Hi I am sure I have done this before but I can't find it now.  I have a spreadsheet with three lists.  I want to combine the lists into one.  On the sheet attached I have three columns of names A, B...
Formulas & Functions
tips and tricks
Ian Tobin's avatar
Ian Tobin
Copper Contributor
Jun 21, 2017

Thank you for the reply Sergei, however I think I have tried to be too clever.  I thought I would be able to change the formula to the exact specifications I was wanting but I dont seem to be able to do this.  The list I need to make is on a different sheet and there are gaps between the columns on the first sheet.  I have atached a workbook that looks more like what I really need. 

The three columns that have data in on the first sheet need combining in a column on the second sheet if that is possible please?

Sorry for not being specific enough in the origional post.

Ian

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 21, 2017

Hi Ian,

 

Very fast modification of the formula is

 

=IF(
 ROW()<=COUNTA(Sheet1!A:A),
  INDEX(Sheet1!A:A,ROW()),
  IF(ROW()>COUNTA(Sheet1!A:D)-(4-1),
   IF(ROW()<= COUNTA(Sheet1!A:H)-(8-1),INDEX(Sheet1!H:H,ROW()-COUNTA(Sheet1!A:D)+4),"" ),
   INDEX(Sheet1!D:D,ROW()-COUNTA(Sheet1!A:A)+1)
  )
)

but above is very unflexible. Another approach could be using of Power Query.

I'll modify a bit some later to make the formula more human friendly, so far the result is in attached file.

 

  • Patrick_210's avatar
    Patrick_210
    Copper Contributor
    Feb 18, 2019

    Sergei I saw your script in the post above and was wondering if you could help derive a script that has been perplexing me for days now. I am trying to create teams for an upcoming function in my school district and need to set teams by two criteria a random number that has been assigned as a student ID and then make sure that a team doesn't have more than two team members from the same school. I need a script because the day of the event we will have students missing due to absence and will need to quickly rearrange the teams. I have excel 2010 and can access 2013. 

    • Patrick_210's avatar
      Patrick_210
      Copper Contributor
      Feb 18, 2019

      =IF

      (

      H4="Bernal ms",Neff!C6,

      IF(H4="Neff MS",Connally!C6,

      IF(H4="Connally Ms",Jefferson!C6,

      IF(H4="Jefferson Ms",Jordan!C6,0)

      )))

       

      The function above allows me to pull students from lists in the tabs across the bottom of the spreadsheet ensuring the function changes schools each time, but the problems is making sure I pull form the top of the directed lists each time the formula returns a hit for a school. I am not familiar enough with how to write a script using the "index feature. Making the index function pull from a list until the list is exhausted. 

       

       

Resources