Hello All! I need to write a formula to pull out the Top 5 programs from a large set of of data. Example set of data is provided below. What I am trying to do is extract the Top 5 Groupings in column C below by ID number in column A along with their percentage of the total in the Amount column for Direct Project Account Codes only in column B in order of greatest to smallest. If the ID number has more than 5 Direct Groupings like for example ID 3 below then I want the total of the other Groupings to be summed under an Other column. The desired results in the desired format is below the sample data set.
Below is the answer I am looking to get in this format from the data set below using a formula. Lists the ID in the first column and then 2 columns per Top 5 Grouping and then 2 columns for other where the first column under each Grouping is for the Project Name and the second is for the % of the amount - these are in order from greatest to smallest. Where a particular ID had less than 5 Direct Projects those are blank. Again, only focused on Direct Project Account Group Codes even though the data set above has multiple codes.
Any help much appreciated! Thanks!
Shana
ID | Project Account Code | Grouping | Amount |
1 | Direct | Project A | 250 |
1 | Direct | Project A | 100 |
1 | Direct | Project B | 500 |
1 | Direct | Project C | 50 |
1 | B&P | INV A | 150 |
2 | Direct | Project C | 25 |
2 | Direct | Project C | 600 |
2 | Pre B&P | INV B | 800 |
2 | Suspense | SUSP A | 100 |
2 | Unallowable | UNALLOW D | 25 |
3 | Direct | Project A | 1000 |
3 | Direct | Project B | 900 |
3 | Direct | Project C | 50 |
3 | Direct | Project D | 250 |
3 | Direct | Project E | 400 |
3 | Direct | Project F | 10 |
3 | Direct | Project G | 25 |
Below is the answer I am looking to get in this format from the data set below using a formula. Lists the ID in the first column and then 2 columns per Top 5 Grouping and then 2 columns for other where the first column under each Grouping is for the Project Name and the second is for the % of the amount - these are in order from greatest to smallest. Where a particular ID had less than 5 Direct Projects those are blank. Again, only focused on Direct Project Account Group Codes even though the data set above has multiple codes.
ID | Grouping 1 | | Grouping 2 | | Grouping 3 | | Grouping 4 | Grouping 5 | Other | |||
1 | Project B | 56% | Project A | 39% | Project C | 6% | ||||||
2 | Project C | 100% | ||||||||||
3 | Project A | 38% | Project B | 34% | Project E | 15% | Project D | 9% | Project E | 2% | Other | 1% |
Any help much appreciated! Thanks!
Shana