Returning all non-zero cells in a pivot table to one vertical list

Amy Gassett

New Member
Joined
Oct 30, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I have the below table:

1604086867085.png


I need to have a table as follows:
LEYT1500108.24
LEYT50305.51
LEYT51001.78
LEYT517226.99
LEYT5235196.92
LEYT524025.85
LEYT524512.32
LEYT5260812.31
MAYF50305.51
MAYF510010.69
MAYF52355.33
MAYF524025.85
MAYF5260454.88

I've tried various INDEX formulas, with SMALL, and I can only get a return on one column at a time.

I need to summarize the whole table into one column. I can do a unique formula in each of the columns of the new table; that is not problem.

So, I need a forumla for new column A that will return for every non-zero cell, the value in row 1.

For new column B, I need a formula that will return the value in pivot table column A for every non-zero value. This needs to correspond to the previous column's value from row 1.

For new column C, I need the non-zero values in the table, that match the values in column A and column B.

I know this is super complicated.

I know some VBA.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Instead of picture which we cannot manipulate, please upload your source information using XL2BB function which is available as part of this forum. Once that data is available, I believe that the transformation you are looking for can be easily achieved with Power Query which is called Get and Transform on your version of XL and found on the Data Tab of the ribbon.
 
Upvote 0
Instead of picture which we cannot manipulate, please upload your source information using XL2BB function which is available as part of this forum. Once that data is available, I believe that the transformation you are looking for can be easily achieved with Power Query which is called Get and Transform on your version of XL and found on the Data Tab of the ribbon.
Okay, I am heading out for voting right now, but I will upload Monday.
 
Upvote 0
Glad to hear you are voting. Get a sandwich as the line may be long.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top