Combine 5 lists into one big list

W would need to know where your other lists are located (columns), so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

I am sorry .I post from an old phone that upload cannot be accomplished.
I edited post 1

You still haven't told us which columns you are using for your five lists.

Pete

every list consists of 3 columns like I posted before.
all 5 lists are next each other.

up......up.......up
Is there something that needs explaining?

I could finally uploaded the file:
www.datafilehost.com/d/0b74c5ce
please your support

merge-three-named-ranges-into-one-list.png

Excel 2007 array formula in the cell where you want to have the fill list(e.g D2):

=IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)), IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)), ""))) + CTRL + SHIFT + ENTER

This is an array formula, here is how to enter it. Type the formula in cell D2, press and hold CTRL + SHIFT simultaneously. Press Enter once. Release all keys. If you did it correctly, you now have curly brackets before and after the formula.
Copy cell D2 and paste it to D3:D12.
This example merges three columns into one column using an array formula. (See the image)

I'm not sure how much work you want to do yourself, and how much you want this to be automated. For example, you could fill the numbers 1 to 72 down column A manually, but then if you add some more names to any of the lists you would need to remember to extend the sequence. Consequently, I've tried to put my solution together so that it requires minimal effort on your part, and is as generic as possible. The only input required on the Report sheet is the spacing between columns (i.e. 3) in cell J1 and the column letter of the starting column in J2 (i.e. A), and then everything else is derived from that input.

I've used a small lookup table in columns K to N where the column letters for each block are derived (A, D, G etc.), along with the column number (1, 4, 7 etc.). These could easily be set manually, but I have generated them automatically from the two inputs. Column M generates the number of names in each list (MAX of the appropriate column), and column N gives a cumulative sum of those numbers. It is important that cell N1 contains zero.

Then the numbers in column A can be generated with this formula in A2:

=IF(ROWS($1:1)>MAX(N:N),"",ROWS($1:1))

which can be copied down until you start to get blanks - I've copied to A80 in the attached file. The names can be picked up using this formula in B2:

=IF($A2="","",INDEX(Data!$A:$O,$A2-VLOOKUP($A2-1,$N$1:$N$6,1)+2,(MATCH($A2-1,$N$1:$N$6)-1)*$J$1+1+COLUMNS($B:B)))

and this can be copied into C2 for the Jobs. The range A to O is specific to your layout, but this could be made larger if you had larger spacing between your lists and would make the solution more generic. Again, these formulae can be copied down to complete the lists, and by copying to row 80 this will cope with (a few) extra names being added.

Next time you should attach your file to one of your posts, rather than use an external file-hosting site.

Hope this helps.

Pete

Mr. Pete_UK thank you for your solution but you used many helping columns.
Mr. Regality you made a great solution, but please apply it to my file
I love independent formulas!!

Originally Posted by AliGW

Have you tried to do that for yourself? You will learn more by doing so.

Thank you sir for your help
I did it