Amy Gassett
New Member
- Joined
- Oct 30, 2020
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
I have the below table:
I need to have a table as follows:
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.
I need to have a table as follows:
LEYT | 1500 | 108.24 |
LEYT | 5030 | 5.51 |
LEYT | 5100 | 1.78 |
LEYT | 5172 | 26.99 |
LEYT | 5235 | 196.92 |
LEYT | 5240 | 25.85 |
LEYT | 5245 | 12.32 |
LEYT | 5260 | 812.31 |
MAYF | 5030 | 5.51 |
MAYF | 5100 | 10.69 |
MAYF | 5235 | 5.33 |
MAYF | 5240 | 25.85 |
MAYF | 5260 | 454.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.