Vstack a variable range of 1D vertical arrays

[gordonpsmith]

Trying to break Excel again, so thanks for any thoughts.

I could do this in VBA, but I'm working with a requirement that disallows that approach, so I'm trying to bend excel syntax to my will.

• I have a series of tabs with structured data for a variety of checkpoints
• I want a dynamically generated, 1D, unique, and sorted list of part numbers from those tabs.

My Current Approach:

1. I create a series of columns on a reference page, whereby I identify:
   1. the tab name (manually entered) and
   2. how many rows of viable data (=COUNTA(B5:B1005)) lie within those tabs.
2. That information is used to create a dynamic definition that can pull the part numbers from the tabs (=SORT(UNIQUE(INDIRECT(B1&"!B2:B500"))))

That gives me a series of 1D vertical arrays, each of which is a sorted, unique list of part numbers from each tab. Now, I want to go one step further and do the same thing against that array. Right now, I have the (godawful)
=SORT(UNIQUE(VSTACK(INDIRECT(D5),INDIRECT(F5),INDIRECT(H5),INDIRECT(J5),INDIRECT(L5),INDIRECT(N5),INDIRECT(P5),INDIRECT(R5),INDIRECT(T5),INDIRECT(V5),INDIRECT(X5),INDIRECT(Z5),INDIRECT(AB5),INDIRECT(AD5),INDIRECT(AF5),INDIRECT(AH5),INDIRECT(AJ5),INDIRECT(AL5))))
This manually looks at the rows I defined for where the data is, stacks them on top of each other in a 1D array, and then pulls the unique values out of that. Problem is, it's unwieldy. Easy to fix in VBA, but as I said, out of bounds for this round.

If I try =UNIQUE(B5:T218), trying to pull the whole thing together, it seems to hit just column by column (not a singular 1D vertical array)
If I try =VSTACK(B5:T218), thinking I could unique and sort later, that also doesn't work

In a perfect world, I'd like to have something like:
=SORT(UNIQUE(VSTACK(StartingRow:EndingRow)))
so this would allow for later growth as needed...

Put another way, how can I get rid of manually doing all the indirects?

 

[gordonpsmith]

 

[gordonpsmith]

As this posts, I realize I forgot to mention the final sorting and such is on a different tab, so things won't get circular...

 

[dreid1011]

If I understand correctly, have you tried something like this:

=UNIQUE(TOCOL(B5:T218))

And then add the outer SORT() of course.