8 Columns with empty cells -> 1 Column without empty cells -> Problem: 1st value ignored

Hi. I can't see your image. Please attach an excel sheet instead...

excel22.xlsx

here you go!

edit: i forgot to put the formula down in column I, but you can see already that the first value is ignored.

Hi again. That's better !!! I can see it. So, is this what you had in mind? I used a named range (use CTRL F3 to see it - it's there just to keep the formula "simpler"!!) to cover all the cells that have a possible entry and a bit of a monster formula to return a lst of values (in alphabetical order). It's an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Thank you very much, it works Is there just a possibility to shift the first value to the first position in the List? Now it's always in the last position.

my bad, its just alphabetic. thank you!

I'll check tomorrow... Most folk want these lists arranged alphabetically. It's wine o'clock here in rural France: so no point in offering any suggestions now.

Haha sure, enjoy it!

--

I tried it now with real values and the first value in the Final List is always empty. I attached the document. Maybe you have any idea why? excel22 (4).xlsx

edit: I found the origin of the problem, but don't have a solution yet. The empty cells from my formulas are not really empty. Its merely the result of an IF-formula -> Those cells contain ""
It's easy to check with the function ISBLANK()

Just add this to your table in a random cell:

Please Login or Register  to view this content.

... you will now see what i mean!

Is there a way to make cells really blank if its due to a result from a formula?

OK. Another day, another array formula monster. This is simply a series of nested formulae, going across - one column at a time. It returns values int he order that it finds them and ignores formula-induced blanks. OK?

There may be a neater way, but I can't bring it to mind...

Formula:

Please Login or Register  to view this content.

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.