ialwayscapital
New Member
- Joined
- Aug 18, 2009
- Messages
- 18
Okay guys, I did ask this question in many forums but did not get any reply.
I have a salary sheet, where new data inserted every month. This is not a one time job, it needs to be done every month.
Salary Sheet looks like this. (image 1)
I want to print Salary sheet for each UNIT, with total below the table. Something like this (image 2)
With power query i can group table by UNIT and selecting ALL ROWS for all other data, This way i get a table for each unit. But i dont know how to load multiple tables from here.
I want multiple tables (one under another) in a same worksheet so that i can manipulate data & column widths etc to fit it in print area.
- As you can see (image 2), Unit name as Heading and whoever worked in that unit should be in that list then another heading of another Unit and whoever worked there.
- Header Row (Sr,Name, Bank & Acc No, etc etc) is not important. I could put this row at top and repeat it with print tiles.
- Units are DYNAMIC, may varies every other month.
- Space of 2 blank rows between two unit tables should be there. (Like shown in image 2)
Somehow I managed to achieve expected results by making helper sheet (see helper1 hidden sheet, where i assumed that maximum names in that unit would be 55) and then another worksheet with auto-filter remove blanks VBA. Its complicated see Sample Sheet.
I want some kind of formula based OR power query based solution for this. Its regular updating sheet, Units can increase or decrease, Number of names can increase or decrease every other month.
I am using office 365 (monthly targeted) fully updated version so all the dynamic array formulas available with me.
Feel free to play with sample sheet. Download Sample Sheet From Here
I think POWER QUERY can do this, but i dont know how.
Here is how i achieved the desired result.
1. Helper Sheet created. In a column i used formula =Unique(SR[Unit]) to get all the unique entries of UNIT column.
2. Created a table like design (Not excel official table, because dynamic array formulas are not supported in excel table) and used formula =FILTER(SR[[Unit]:[Status]],SR[Unit]=F2) Here F2 is the first row of Unique list mentioned in step 1.
3. I assumed that maximum NAMES in a table (mentioned in step 2) would be 55. (it can be more in future, thats why i am looking for some other solutions)
4. This way i created all the tables (about 50) and added TOTAL row manually.
5. Created another sheet (PRINT) and adjusted required data and column widths etc. Please UNHIDE HELPER1 SHEET in sample sheet for a better idea
6. Then used filter to remove blanks. Done
7. I used VBA code (to unprotect, refresh, filter blanks and then protect again) in PRINT sheet. PASSWORD is 911
Used tons of formulas and tricks to achieve desired result, but this makes my sheet bulky and slow. I am looking for an easy way to achieve the same. Now am trying hands in power query. Lets see. Any help would be great.
Thanks
I have a salary sheet, where new data inserted every month. This is not a one time job, it needs to be done every month.
Salary Sheet looks like this. (image 1)
I want to print Salary sheet for each UNIT, with total below the table. Something like this (image 2)
With power query i can group table by UNIT and selecting ALL ROWS for all other data, This way i get a table for each unit. But i dont know how to load multiple tables from here.
I want multiple tables (one under another) in a same worksheet so that i can manipulate data & column widths etc to fit it in print area.
- As you can see (image 2), Unit name as Heading and whoever worked in that unit should be in that list then another heading of another Unit and whoever worked there.
- Header Row (Sr,Name, Bank & Acc No, etc etc) is not important. I could put this row at top and repeat it with print tiles.
- Units are DYNAMIC, may varies every other month.
- Space of 2 blank rows between two unit tables should be there. (Like shown in image 2)
Somehow I managed to achieve expected results by making helper sheet (see helper1 hidden sheet, where i assumed that maximum names in that unit would be 55) and then another worksheet with auto-filter remove blanks VBA. Its complicated see Sample Sheet.
I want some kind of formula based OR power query based solution for this. Its regular updating sheet, Units can increase or decrease, Number of names can increase or decrease every other month.
I am using office 365 (monthly targeted) fully updated version so all the dynamic array formulas available with me.
Feel free to play with sample sheet. Download Sample Sheet From Here
I think POWER QUERY can do this, but i dont know how.
Here is how i achieved the desired result.
1. Helper Sheet created. In a column i used formula =Unique(SR[Unit]) to get all the unique entries of UNIT column.
2. Created a table like design (Not excel official table, because dynamic array formulas are not supported in excel table) and used formula =FILTER(SR[[Unit]:[Status]],SR[Unit]=F2) Here F2 is the first row of Unique list mentioned in step 1.
3. I assumed that maximum NAMES in a table (mentioned in step 2) would be 55. (it can be more in future, thats why i am looking for some other solutions)
4. This way i created all the tables (about 50) and added TOTAL row manually.
5. Created another sheet (PRINT) and adjusted required data and column widths etc. Please UNHIDE HELPER1 SHEET in sample sheet for a better idea
6. Then used filter to remove blanks. Done
7. I used VBA code (to unprotect, refresh, filter blanks and then protect again) in PRINT sheet. PASSWORD is 911
Used tons of formulas and tricks to achieve desired result, but this makes my sheet bulky and slow. I am looking for an easy way to achieve the same. Now am trying hands in power query. Lets see. Any help would be great.
Thanks
