Hi,
I have the below data for which I need to perform following task:
1. I need to transpose the rows to columns. Here, the first problem is few headers are repeating and may have different values. therefore, if I use unique tool it will make the rows unique with particular combination but how can I figure out which repeating header to come in the beginning section and which one comes in end section as data will keep on changing every month.Also, I need to maintain the order of the headers...Please note actual headers are different from the example.
2. I need to ensure that the totals for both the columns Header 24_Beginning Total (sum of Header 7 to Header 23) & Header 31_end Total (sum of Header25 to 23) should tally with sum of corresponding columns and yes keeping in consideration the repeating columns.
Your help will be greatly appreciated
| Header 1 | JAN_2020 | FEB_2020 | MAR_2020 | APR_2020 | MAY_2020 | JUN_2020 | JUL_2020 | AUG_2020 | SEP_2020 | OCT_2020 | NOV_2020 | DEC_2020 | |
| Header 2 | 3039.60 | 2175.60 | 3599.77 | 3602.57 | 3605.36 | 3608.16 | 3610.95 | 3613.74 | 3616.54 | 3619.33 | 3622.13 | 3623.94 | |
| Header 3 | 425.66 | 436.37 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | 388.69 | |
| Header 4 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | 451.24 | |
| Header 5 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 7 | 23715.04 | 24608.00 | 25024.71 | 22246.11 | 22204.48 | 22162.85 | 22121.23 | 22079.60 | 22037.97 | 21996.35 | 21954.72 | 21913.09 | |
| Header 8 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 9 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | 34.77 | |
| Header 10 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | 5.59 | |
| Header 11 | 17.19 | 47.48 | 17.64 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.93 | |
| Header 12 | -99.37 | -47.92 | -97.19 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -152.99 | |
| Header 13 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 14 | 281.28 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 15 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 16 | 653.50 | 376.80 | -2739.41 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 17 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 18 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 19 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 20 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 21 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 22 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 24_Begining Total | 24608.00 | 25024.71 | 22246.11 | 22204.48 | 22162.85 | 22121.23 | 22079.60 | 22037.97 | 21996.35 | 21954.72 | 21913.09 | 21818.39 | Grand Total - sum(header7 to Header 23 |
| Header 25 | 27320.00 | 27647.60 | 27200.31 | 25845.88 | 25807.05 | 25768.21 | 25729.38 | 25690.55 | 25651.72 | 25612.88 | 25574.05 | 25535.22 | |
| Header 26 | 264.28 | 37.76 | 38.40 | 37.58 | 37.58 | 37.58 | 37.58 | 37.58 | 37.58 | 37.58 | 37.58 | 36.60 | |
| Header 27 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Header 11 | 17.19 | 47.48 | 17.64 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.39 | 17.93 | values for repeated columns can be same or different from the values in the beginning section |
| Header 12 | -99.37 | -47.92 | -97.19 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -99.37 | -152.99 |
| Header 13 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 | -1.00 |
| Header 28 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 | 6.57 |
| Header 29 | 139.92 | -490.17 | -1318.85 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 17 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 18 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 30 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 21 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 22 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Header 31_end Total | 27647.60 | 27200.31 | 25845.88 | 25807.05 | 25768.21 | 25729.38 | 25690.55 | 25651.72 | 25612.88 | 25574.05 | 25535.22 | 25442.33 | Grand Total - sum(header25 to Header 23) |
| Header 32 | 0.06 % | (0.06 %) | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | 0.95 % | |
| Header 33 | 0.26 % | 0.20 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | 1.15 % | |
