Show cells from pivot table without double clicking

I'm using a pivot table to sort/consolidate data. Each row has a unique identifier (in this case an opening label) that i'd like to show up in the pivot table without double clicking. If I include the opening label in the pivot table field then it will not consolidate the data because each label is unique.

Excel Example.PNG

Excel Example 2.PNG

Next to each of the sums, i'd like to see the opening label WITHOUT double clicking, copying the opening labels, transposing them next to the row, and then consolidating into one cell as a comma separated list.

What I'd like to see:
Excel Example 3.PNG

Can anyone help?

Hi EggsCell,

See attached for example.

The solution consists of a helper column and a User Defined Function (UDF).

Your data file needs a helper column. In the example it is column A.

Formula in A2: copy downward

Please Login or Register  to view this content.

Copy downward.

Put this in a module:

Please Login or Register  to view this content.

Formula in F4 to the right of Pivot Table: copy downward

Please Login or Register  to view this content.

Hope this is helpful

Thank you southward! It works perfectly on your example but i'm having difficulty implementing your method. I've attached my copy, can you tell what I'm doing wrong? It says "#VALUE!"

Hi EggsCell,

The problem is with the [Data] in your formula. That part of the formula is telling it to look for an outside source.

Replace this:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

and copy it downward.

Cheers

AWESOME!!! Thank you so much!

southward, thanks so much for your help. I'd like to take this one step further and I'm hoping you can help with the lookup function. I have added a cost column to the wood door pivot table tab. I'd like to show the associated cost of each opening on the door schedule tab. Please see attached and let me know if you can help.

Wonderful! I like the iferror() Function, hadn't used that before. Thanks again, you've been a tremendous help.

You are very welcome. Glad I could help