# Creating a vertical list of people from two pivot tables

Copper Contributor

# Creating a vertical list of people from two pivot tables

Hi,

I have 2 pivot tables on the same sheet.

Under the "Row Labels" of the pivots I have two lists of people (someone is in the other someone is not).

I need a "dynamic" formula to create a vertical list of people with all the unique names in the 2 pivots.

I'm saying dynamic because the pivots could possibly change over time, so the list should too.

I know I should use the vstack and unique functions but I don't know how.

The names are under the A:A and J:J columns...

If I use a formula implying in it A:A and J:J I will have on the list also "row label" and "Total" which I don't want. To exclude such words I did a VLOOKUP connecting to a different page where I can find a complete list of the names. But the VLOOKUP works only for a single column

ex.:=SORT(UNIQUE(STACK.VERT(VLOOKUP(A:A,Listofpeople!P:P,1,0))))

I need a formula like this, where I can put also the J:J column/list, in order to create a single vertical list for all the names in the 2 pivots.

Any idea?

3 Replies

# Re: Creating a vertical list of people from two pivot tables

That could be

``````=LET(
f, UNIQUE(VSTACK(TOCOL(A:A, 1), TOCOL(J:J, 1))),
FILTER(
f,
(ISNUMBER(SEARCH("Label", f)) - 1) *
(ISNUMBER(SEARCH("Total", f)) - 1)
)
)``````

# Re: Creating a vertical list of people from two pivot tables

Hi Sergei, thanks for the reply.
Insn't there in your opinion a way to express the list by using a formula instead of coding?

Thank you very much

# Re: Creating a vertical list of people from two pivot tables

Above is the formula which you could insert into the formula bar.