• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help for HR Director with challenging rows-to-column conversion

K

Kristie Catlin

New Member
Hello all,
I am new here, and landed on this website after 3 hours of mind-numbing searches and tutorials for help with my report.

Our new payroll company has given us the attached "Payroll Rows" report to show our PTO (paid time off) for each employee, and says its impossible to convert it to a columnar format within their system. So, I'm trying to do so in Excel.

The problem lies in where the text/data within the rows are not in a consistent format, therefore there isn't consistent data for a heading/grouping. Even so, I need to convert to a columnar report to subtotal balances for:
-PTO at the beginning of the year
-amounts used
-amounts accrued
-balance year to date.
It should be a simple task, but I am at a loss.

There are two worksheets within the report. "Payroll Rows" is the original report that needs to be converted. "HR Columns" is my attempt at showing how I'd like the report formatted. I tried to copy the consistent threads of text from the rows into column headings, hoping this will make it easier to understand what I need. I put in a few employee examples pulling from the "Payroll Rows" report.

Additionally, we started with this company in June, but due to a glitch on their part, the actual balance starting date should be 7/1/15, so all data prior to that date should be ignored.

Whatever help you can give will be a huge burden lifted.

Thank you so much,
KC
 

Attachments

vletm said:
Press [ SOLVE ]-button
to Check / Test / Report
Something like that?
Click to expand...

Yes, that's very close, however the sample report isn't pulling the numbers from the actual text. The numbers are embedded in text like "Schedule debits 8.00" or "Rule accrues credit 6.93". I looked at Emp 12 and it seems the sample report pulled the balance column numbers rather than the numbers embedded in the text. Does make sense?
 
Okay ... You want to use only [G]-column values for 'HR Columns', do You?
[Balance In] and [Balance Out] are always 0 ... and so on?
If there are no numbers, then no values to 'HR Columns'.
So, how do get [Balance Out]-values to Your sample-tab?
I need to know differences of rules, before I can make modifications.
 
vletm said:
The numbers are embedded in text.
Click to expand...

Hi... I'm sorry, it was a busy day yesterday. I tried this new sample report and it's almost there! Yes, I want to use column G values for "adjustments, credits, and debits." Column H values show the PTO bank balance after each change in Column G. I used the sample report again and highlighted similar changes in column G. Yellow is for the adjustment as of 7/1. Red is for all debits made to the PTO bank. Green is for all credits made to the PTO bank. Maybe this will make the report a little easier to read?

Thank you so much for spending your valuable time in helping me!!!
 

Attachments

Some Areas of Both Sheets Colours adjusted after press [SOLVE],
'Balance Out' from PTO.
... maybe easier to read - maybe not?
ps. You didn't like my file?
 

Attachments

vletm said:
Some Areas of Both Sheets Colours adjusted after press [SOLVE],
'Balance Out' from PTO.
... maybe easier to read - maybe not?
ps. You didn't like my file?
Click to expand...

Yes! The latest file you posted worked beautifully!!! You are amazing and I thank you so much!!!

So... how would I be able to use this on my actual file each month? The worksheet "payroll rows" will change data each month, but the columns will remain the same. Will the solve button work if I delete the data each month and past new data in?
 
Of course ... of course works!
I did one new button for You, [Do Empty].
It will empty 'safe way' this sheet.
... and You WON'T copy over the 1st row!

ps. ... everyone has always backups
 

Attachments

vletm, a suggestion; change:
If type_chk = "Balance Out" Then
to:
If type_chk = "Balance Out" Or type_chk = "Balance In" Then
or similar.
 
Last edited:
@p45cal, yes, that's possible too,
but some cases those have to be written in separate sentences.
There were many 'changes' with those "..Out" and "..In" and
I haven't always written the most shortest code...
and those could still change as two part sentences.
There are still few things that maybe should do,
but I have to wait until @Kristie Catlin has checked the latest version.
 
In the attached, on the Payroll sheet there's a button ('Start here…') which when clicked brings up a combobox with dates from the Date column in order with the earliest date selected by default. Either choose a new date to start from or just click the Go button. This will add a new sheet with the re-arranged data.

Optional reading:
How it works:
1. Creates a copy of the Payroll sheet
2. Changes it into a table for use as a Pivot Table data source.
3. Adds a new sheet and creates a pivot table
4. Copies the resultant pivot table 'as values' to yet another new sheet.
5. Deletes the other two newly created sheets.

Notes:
1. The Types headers are created by removing the number from the text in the Type column; in this case it resulted n 8 different headers. If at some time in the future new ones appear, they will also appear in the results
2. The values in the new table arise not from the numbers embedded in the text but variously from the actual value in the original PTO column and the difference between a given value and the value directly above it in that column. This results in values appearing in the final table as negative if they were subtracted and positive if added/reset.
3. If you want to play with the pivot table to see how you might want to see the data differently or with less data, you can prevent the sheets with the pivot and the source data being deleted by deleting or commenting-out (by putting an apostrophe at the beginning of it) a single line near the end of Macro5:
Application.DisplayAlerts = False: NewPivotSheet.Delete: PTDataSceSht.Delete: Application.DisplayAlerts = True
 

Attachments

@p45cal, I haven't ask, but codes would work with Mac too.
Anyway, I couldn't test and I couldn't compare, could I say, 'Your style coding's result'.
reply #10 ... 117 lines code, without 'cleaning'
Okay.
 
Yes, of course.
1) I know and see 'my style coding's result'.
It's possible to see on 'HR Columns' after press [SOLVE].
Your style of coding is different and I couldn't see the result.
2) You gave a suggestion with Result#10, that is always Okay.
I had (and still have) some extra code and someone would like to see comments too. Comments could come later, if needed.
Your code has more, many things. I just compared our codes difference.
There are many ways to find the result. I try to learn different ways to do it.
Okay, now?
 
You must log in or register to reply here.
Back
Top