Creating a vertical list of people from two pivot tables

Copper Contributor


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



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


That could be

    f, UNIQUE(VSTACK(TOCOL(A:A, 1), TOCOL(J:J, 1))),
        (ISNUMBER(SEARCH("Label", f)) - 1) *
        (ISNUMBER(SEARCH("Total", f)) - 1)
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


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