Dear all,
Requesting any help on Summing values of visible cells (row-wise)
PFA for the excel sheet.
Input sheet : Data which is the input to the macro. (will always be dynamic)
Unique Names sheet : Has list of unique names.
Output sheet : Presently what am able to achieve.
Final sheet : Expected Output
Appreciate if you could also help with writing this entire code efficiently.
Please Login or Register to view this content.
Thanks,
Vinod Krishna
Please Login or Register to view this content.
Dear Bob,
Your solution is efficient but gives partially correct results
Correction in Output:
A -> FW03 -> 0
B -> FW01 -> 0
B -> FW02-> 2
B-> FW03-> 2
Just to inform, the data at the year end will have ~52/53 FWs (columns) and 1000 rows.
Appreciate all your help
Thanks
This is one of those classic "you should do it different" moments. Have you considered entering data in a 3 column format?
Like:
NAME - FW# - Value
A 01 4
B 01 1
A 02 3
B 02 2
Excel is very good at expanding vertically - this format will be very easy to summarize. Your "Final" sheet is an example of a pivot table.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My input sheet is not being input manually but output from an script. This will eventually have 53 columns and close to 1000 rows. There will be blanks in between.
This needs to be converted to
Name -- FW -- Hours
No manual intervention.
Once I get it in this format, yes it can summarized like a Pivot table.
Last edited by Vinod Krishna.C; 08-05-2014 at 02:43 PM.
I don't think so, I think your predicted results are wrong. There are two A/FW03 entries, one with a value of 0, one with a value of 1. That sums to 1 in my book.Originally Posted by Vinod Krishna.C
Dear Bob,
Your solution is efficient but gives partially correct results
Correction in Output:
A -> FW03 -> 0
B -> FW01 -> 0
B -> FW02-> 2
B-> FW03-> 2
Input:
Name FW01 FW02 FW03
A 1 2 -
B - 2 2
A - 2 1
Expected Output:
Name FW Hours
A FW01 1
A FW02 4
A FW03 1
B FW01 0
B FW02 2
B FW03 2
Bob's Output:
Name FW Hours
A FW01 1
A FW02 4
A FW03 1
B FW01 1
B FW02 4
B FW03 1
PFA for reference.
Please Login or Register to view this content.
You could also pull Input data into Power Query and unpivot it. Save the results to the workbook, and a simple pivot will get your rewsults.
Bob,
Your code works like a charm. Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks