Sample file attached.
I am trying to extract from a much larger database, all employees (up to 20) with hours against various project numbers.
and have this listed by employee. Do not show employees with no assigned hours.
Don't want pivot tables, but rather VBA or macros.
Any help appreciated.
RobN
Last edited by Robn02; 03-07-2018 at 10:26 PM.
the database is designed wrong, so you cannot pivot.
the macro would have to dismantle the database , then rebuilt it in data terms, THEN you can pivot.
Thanks Ranman... I am needing formula or VBA, not pivots.
RobN
To get a formula solution to work I had to convert the data into record form (credit inspiration to ranman's post) as seen on sheet 2 of the attached file, which was accomplished using three formulas.
1) For Project (array entered*):Formula:
Please Login or Register to view this content.
2) For EMP:Formula:Please Login or Register to view this content.
3) For HRS:Formula:Please Login or Register to view this content.
The array entered formula* which populates the result table on sheet 2 is:Formula:Please Login or Register to view this content.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks so much, JeteMc and Ranman256.
Converting the data to record form was key! I would never have figured that out.
Appreciate the fixed up spreadsheet as well. That was an education!
Robn
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks