Combine Ranges into an Array

[mperata]

I could not find an answer to my question by search.

Example:

I have four unsorted data ranges in Column B in the attached file, "Project1", "Project2", "Project3", "Project4"

I want to create an unsorted Array in Column D, ignoring spaces and #N/A in Column B

[tigeravatar]

mperata,

Attached is a modified version of your example workbook.

Column C is being used as a helper column to eliminate the error cells. Then column E creates the desired array.

In cells C5 and copied down is this formula:

Please Login or Register  to view this content.

In cell E5 and copied down is this array formula (Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter):

Please Login or Register  to view this content.

You can hide column C if you want. Does that work for you?

[tigeravatar]

Here's the formula without a helper column (still an array formula):

Please Login or Register  to view this content.

[mperata]

Thanks tigeravatar

I do not understand this part of your formula "ROW()-4)"

[tigeravatar]

Row() gets the current row. The formula starts in row 5, so that returns 5. Then it subtracts 4 so that the row the formula starts in will return a 1:

row 5 -> Row()-4 = 5-4 = 1
row 6 -> Row()-4 = 6-4 = 2
row 7 -> Row()-4 = 7-4 = 3
etc

The reason we want that is so the Small(array,#) will return the correct #. It needs to be:
Small(array,1)
Small(array,2)
Small(array,3)
etc

So basically, because the formula starts in row 5, it is Row()-4
If the formula started in row 2, it would be Row()-1
If the formula started in row 3, it would be Row()-2

[mperata]

Got it, thanks!!