 • 582K Members
• 8,781 Online
• 704K Conversations

## Create one list of names combining three lists

Highlighted
Occasional Contributor

# 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 and C.  In D, I would like to list all the same names in one long list.

Thank you

23 Replies
Highlighted

# Re: Create one list of names combining three lists

Hi Ian,

Try this formula starting from D1 and copy it down

```=IF(
ROW()<=COUNTA(A:A),
INDEX(A:A,ROW()),
IF(ROW()>COUNTA(A:B),
IF(ROW()<= COUNTA(A:C),INDEX(C:C,ROW()-COUNTA(A:B)),"" ),
INDEX(B:B,ROW()-COUNTA(A:A))
)
)

```

File is attached.

Highlighted

# Re: Create one list of names combining three lists

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

Highlighted

# Re: Create one list of names combining three lists

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.

Highlighted

# Re: Create one list of names combining three lists

Hi Ian,

Here is the solution with PowerQuery, that's Excel tool which is most suitable for such tasks. If you are on Excel 2013 is shall be downloaded and installed (fast and stright forward process), in Excel 2016 it's built-in (named as Get & Transform).

I don't know if you are familiar with it, here are step by step instructions, same is in attached file with screenshots.

```1) Select all columns with source lists and name them somehow
2) Ribbon->Data click on From Table/Range
3) In appeared Query Editor remove Change Types step
4) Select (with Ctrl) columns with your names, right click on any and Remove Other Columns:
5) In Ribbon click Use First Row as Header
6) Select all columns and in Ribbon->Transform click Unpivot Columns
7) Select new column "Attributes", Right click and Remove
8) Double click on "Value" column name, enter any name you want, Enter
9) In Ribbon select Close & Load To (it's disabled for the existing query, but available
for new one), after that select Table, Existing Sheet, and any place in your Sheet2```

You will have in Sheet2 the table with combined list of names. If you make any changes in source data in Ribbon->Data click on Refresh All.

If you still prefer formulas let us know, will update then.

Highlighted

# Re: Create one list of names combining three lists

Hi Sergei

I definitly prefer the formula as i dont really understand what is happening with the Query.  Can you tell me what the numbers in the brackets are for on the formula you sent me on the workbook with the two sheets?

Thank you

Ian

Highlighted

# Re: Create one list of names combining three lists

Hi Ian,
Okay, I see. Will answer some later.
Highlighted

# Re: Create one list of names combining three lists

Ian, that formula is better to consider as prototype. It assumes in in Sheet1 you have empty columns between columns with your names; combine column in Sheet2 starts from the same second row as lists in Sheet1, etc. It could be updated using, for example, OFFSET() and/or some helper cells, some time is needed.

In current formula we compare row number in resulting list (taking into account above assumptions) with number of non-empty cells in colums with list of Sheet1.

First,

`=IF(ROW()<=COUNTA(Sheet1!A:A),`

if it is less than row number with first blank cell, we take names from first list. As soon as it is more we compare with total number of non-blank cells in columns from A to D

`IF(ROW()>COUNTA(Sheet1!A:D)-(4-1),`

Here we deduct number of non-blank cells in header row (first one) in above columns, i.e in A1:D1. That's 4. Minus one since we compare as "more than", could be missed with "equal or more than".

After that similar way compare row number in combined list in Sheet2 with total number of non-blank cells in all columns from A to H excluding headers cells

`IF(ROW()<= COUNTA(Sheet1!A:H)-(8-1),`

If less return third list, otherwise blanks. And if previous condition is not met return values from second list.

Highlighted

# Re: Create one list of names combining three lists

By the way, about Power Query - if your are using Excel more or less regular it's worth to check at least basic things. It could be quite powerful and simple, even without any coding. From this point of view Episode 2 of Excel Table Talks on community home page https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat illustrates the approach quite good.

Highlighted

# Re: Create one list of names combining three lists

Hi Ian,

I updated the formula a bit to make it more flexible. If you wand generate combined list starting from cell C3 in second sheet, enter into this cell and when copy down

```=IFERROR(
INDEX(
OFFSET(Sheet1!\$A\$2,0,0,COUNTA(Sheet1!A\$2:\$A\$100)),
ROWS(\$C\$3:\$C3)
),
IFERROR(
INDEX(
OFFSET(Sheet1!\$D\$2,0,0,COUNTA(Sheet1!D\$2:\$D\$100)),
ROWS(\$C\$3:\$C3)-COUNTA(Sheet1!A\$2:\$A\$100)
),
IFERROR(
INDEX(
OFFSET(Sheet1!\$H\$2,0,0,COUNTA(Sheet1!H\$2:\$H\$100)),
ROWS(\$C\$3:\$C3)-COUNTA(Sheet1!A\$2:\$A\$100)-COUNTA(Sheet1!D\$2:\$D\$100)
),
""
)
)
)```

In ROWS() shall be starting cell of your list as above, it returns current position within combined list when copied down.

Each COUNTA() returns number of elements in your source list.

Each OFFSET() returns your source list. It is assumed there are no blank cells in the middle of the list.

Finally INDEX() return element of source list of current row is within the range of this list, otherwise error.

With IFERROR() we are switching from one source list to next one and return empty cell if no more source lists.

Of course you may add some parameters into the cell(s) within your file not to keep all refrences within the formula and use them in formula, but that's another story.

How it works is in attached file.

Highlighted

# Re: Create one list of names combining three lists

Hi Sergei

You have done everything I have asked so far, thank you for that, but I still cannot get it to work in the actual sheet.

I think my problem is that the names in the list are not manually inputted but are pulled through from a formula so no cell in the list is really blank.

The lists are made from a formula that matches two other lists and pulls out the same names. it looks like this

=IFERROR(INDEX(Maths!\$C\$18:\$C\$43,MATCH(0,COUNTIF(\$G\$2:G2,Maths!\$C\$18:\$C\$43)+IF(IF(COUNTIF(Maths!\$M\$6:\$M\$17,Maths!\$C\$18:\$C\$43)>0,1,0)=1,0,1),0)),"")

I then want to use the lists made from the above formulas to make the lists we have been talking about.  I can make it work in a test sheet like the one we have been talking about but not where the formulas are.

I cannot attach the actual sheet as there is lots of information on there.  Do you have any ideas?

Thank you

Ian

Highlighted

# Re: Create one list of names combining three lists

Hi Sergei

Ignore my last message I have found a way that works.  Rather than having COUNTA in the formula I have changed it COUNTIF and used "?*" as the criteria.

Thank you for all your help on this

Ian

Highlighted

# Re: Create one list of names combining three lists

Hi Ian,

Thank you for the update. Yes, your formula is fine to exclude "". Just in case, please take into account it works with texts only (but that's your case), more universal will be

`=SUMPRODUCT(--(LEN(range)>0))`

if your list is the mix of texts and numbers

Highlighted

# Re: Create one list of names combining three lists

Hi Sergei

Is there a mximum amount of arrangements I can fit into this formula?  Should I be able to use the formula for four columns? Or five?

Thank you

Ian

Highlighted

# Re: Create one list of names combining three lists

Hi Ian,

I guess so, will think how to automate on defined number of columns. Do you have any logic in how your lists are placed, e.g. every third column or what?

Highlighted

# Re: Create one list of names combining three lists

I have tried the following formula to do four columns but Im guessing i am missing something as it doesnt work.

=IFERROR(INDEX(OFFSET(\$B\$3,0,0,COUNTA(B\$3:\$B\$100)),ROWS(\$L\$2:\$L2)),IFERROR(INDEX(OFFSET(\$D\$3,0,0,COUNTA(D\$3:\$D\$100)),ROWS(\$L\$2:\$L2)COUNTA(B\$3:\$B\$100)),IFERROR(INDEX(OFFSET(\$G\$3,0,0,COUNTA(G\$3:\$G\$100)),ROWS(\$L\$2:\$L2)COUNTA(B\$3:\$B\$100)COUNTA(D\$3:\$D\$100)),IFERROR(INDEX(OFFSET(\$J\$3,0,0,CONTA(J\$3:\$J\$100)),ROWS(\$L\$2:\$L2)COUNTA(B\$3:\$B\$100)COUNTA(D\$3:\$D\$100)-COUNTA(G\$3:\$G\$100)),""))))

I actually dont need more than four so if i can get this to work ive cracked it i think.

Thanks again

Ian

Highlighted

# Re: Create one list of names combining three lists

Here you initial lists are in B, D, G, J and you put combined list into L, correct?

# Re: Create one list of names combining three lists

Yes but I keep getting 'You've entered too many arguments dfor this function'

Highlighted

# Re: Create one list of names combining three lists

I have attaced an example on sheet 1.  I have taken the = out of the beginning as I couldnt do it otherwise.

Highlighted

# Re: Create one list of names combining three lists

For such complex formulas better to use formatting - there were few misprints in your variant. This one shall work

```=IFERROR(
INDEX(
OFFSET(\$B\$3,0,0,COUNTA(B\$3:\$B\$100)),
ROWS(\$L\$2:\$L2)
),
IFERROR(
INDEX(
OFFSET(\$D\$3,0,0,COUNTA(D\$3:\$D\$100)),
ROWS(\$L\$2:\$L2)-COUNTA(B\$3:\$B\$100)
),
IFERROR(
INDEX(
OFFSET(\$G\$3,0,0,COUNTA(G\$3:\$G\$100)),
ROWS(\$L\$2:\$L2)-COUNTA(B\$3:\$B\$100)-COUNTA(D\$3:\$D\$100)
),
IFERROR(
INDEX(
OFFSET(\$J\$3,0,0,COUNTA(J\$3:\$J\$100)),
ROWS(\$L\$2:\$L2)-COUNTA(B\$3:\$B\$100)-COUNTA(D\$3:\$D\$100)-COUNTA(G\$3:\$G\$100)
),
""
)
)
)
)``` Highlighted

Forgot the file

Highlighted

# Re: Create one list of names combining three lists

Ian, one more modification. If you updated on

```=IFERROR(
INDEX(
OFFSET(\$B\$3,0,0,COUNTA(B\$3:\$B\$100)),
ROWS(\$L\$2:\$L2)
),
IFERROR(
INDEX(
OFFSET(\$D\$3,0,0,COUNTA(D\$3:\$D\$100)),
ROWS(\$L\$2:\$L2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)
),
IFERROR(
INDEX(
OFFSET(\$G\$3,0,0,COUNTA(G\$3:\$G\$100)),
ROWS(\$L\$2:\$L2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)-IFERROR(COUNTIF(D\$3:\$D\$100,"?*"),0)
),
IFERROR(
INDEX(
OFFSET(\$J\$3,0,0,COUNTA(J\$3:\$J\$100)),
ROWS(\$L\$2:\$L2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)-IFERROR(COUNTIF(D\$3:\$D\$100,"?*"),0)-IFERROR(COUNTIF(G\$3:\$G\$100,"?*"),0)
),
""
)
)
)
)```

i.e change COUNTA(range) on IFERROR(COUNTIF(range,"?*")), but not within OFFSET, when you may delete any column(s) except first one. After deleting formula will look like

```=IFERROR(
INDEX(
OFFSET(\$B\$3,0,0,COUNTA(B\$3:\$B\$100)),
ROWS(\$K\$2:\$K2)
),
IFERROR(
INDEX(
OFFSET(\$D\$3,0,0,COUNTA(D\$3:\$D\$100)),
ROWS(\$K\$2:\$K2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)
),
IFERROR(
INDEX(
OFFSET(#REF!,0,0,COUNTA(#REF!)),
ROWS(\$K\$2:\$K2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)-IFERROR(COUNTIF(D\$3:\$D\$100,"?*"),0)
),
IFERROR(
INDEX(
OFFSET(\$I\$3,0,0,COUNTA(I\$3:\$I\$100)),
ROWS(\$K\$2:\$K2)-IFERROR(COUNTIF(B\$3:\$B\$100,"?*"),0)-IFERROR(COUNTIF(D\$3:\$D\$100,"?*"),0)-IFERROR(COUNTIF(#REF!,"?*"),0)
),
""
)
)
)
)```

but IFERROR() in counting of rows numbers return correct result.

Please not COUNTA doesn't work here since COUNTA(#REF!) always returns 1

Highlighted

# Re: Create one list of names combining three lists

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.

Highlighted

# Re: Create one list of names combining three lists

=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.

Related Conversations
How to move a list to another site?
alfie_s in SharePoint on
3 Replies
Format list view => icon if attachment
chhe5 in SharePoint on
2 Replies
Resulting NaN when calculating between two dates
JunHo_KIM in SharePoint on
2 Replies
List column customization
chinmaykulkarni in SharePoint on
1 Replies
How to manage multiple lists
BangorKeith in SharePoint on
2 Replies