Forum Discussion

Vesperwind's avatar
Vesperwind
Copper Contributor
Oct 10, 2023

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?

excel
Formulas and Functions

3 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Oct 10, 2023

    Vesperwind 

    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)
    )
)
    • Vesperwind's avatar
      Vesperwind
      Copper Contributor
      Oct 11, 2023
      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

Resources