• 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.

Copy hours from a timesheet and paste them to a second sheet.

GN0001

Member
Hello team,

I have to paste lots of rows to another workbook, the first workbook is a time sheet. it has columns as: project name, sub project, days of the week and date (which is the first week of April). If a project Manager has worked on a project on Monday, Tuesday and Wednesday (days are across the row in cell F2 to L2), then in the second sheet: I have to repeat the Project name three times and put the hours in front of that project in an adjacent column vertically and then dates are from 04/01/02014 to 04/06/2014 (which the first week of April), I have put a drop down list in a column that I can select the date for the that pertaining hour.

The project hours are across column but when I want to paste it to second workbook, I have to paste it vertically (which I can paste it transpose).

We need to move data to a system so we need data to be transposed, I need to paste data from rows to columns that I know we can do that in excel.

Is there an easy way to take care of this task?


I always appreciate your help.

GN0001
 
Hi GN001

Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary.

the new forum gives members something they have been asking for, the ability to upload files. If you look to the bottom of the box in which you post your question you will see the button to upload a file from your computer to Chandoo ; this is going to save members a lot of time and trouble.

Thank You..
 
Please look at the Time sheet worksheet. I need the data to be entered and restructured as in destination work sheet.
Please remember each sheet has different number of projects, some have two projects and some have 10 projects.
By the way, do you know what is that being shown on the top of every message box? I mean this tip:
Tip:
Use
Code:
 &
tags to embed your VBA Macros
This is very confusing.
I appreciate your help and look forward to hearing from you.
GN0001
 

Attachments

  • My Work Project.xlsx
    10.4 KB · Views: 15
Hi GN0001, The tip above is for when you want to post a bit of code or macro. You put your code between the the code tags and it will come out formatted as code. You will not see [ ] The code here is posted between them. Hope that helps. ps the code is just an example and nothing to do with your worksheet.
Code:
Sub CopyRedRowsAsIs()
  Dim wks As Worksheet
  Dim wNew As Worksheet
  Dim lRow As Long
  Dim x As Long
 
  Set wks = ActiveSheet
  lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
  Set wNew = Worksheets.Add
  For x = 1 To lRow
  If wks.Cells(x, 1).Interior.Color = vbRed Then
  wks.Cells(x, 1).EntireRow.Copy wNew.Cells(x, 1)
  End If
  Next
End Sub
 
Hello,
I've made a simple solution for you that restructures sample data the way you want. Just enter sheet name and you have your data (extend formulas if needed).
It copies zero hour values as well, you can either filter those manually or add another column that will only show those where hours>0.
 

Attachments

  • My Work Project.xlsx
    14.9 KB · Views: 5
Ok, I further improved it, so it final result doesn't take zeroes
 

Attachments

  • My Work Project.xlsx
    24.8 KB · Views: 14
Ok, I further improved it, so it final result doesn't take zeroes
Xen,
It is amazing.
I understand it partially,
I did evaluate formula to understand what it does, however some of them is not clear form me:

1-COUNT(INDIRECT(B1&"!"&"1:1"))
-What is this part (B1&"!"&"1:1"))? I know it counts the number of days
--------------------------------------------------------------------------------------------------
2-(B2)*(COUNTA(INDIRECT("Timesheet!A:A"))-1)+1
-Indirect returns the content of the cell, but here I see that It returns number 8 which is the number of the rows that I have it in the Timesheet? Why? Also why do you deduct 1 from it and then add 1 to it?
------------------------------------------------------------------------------------------------------------------
3-INDIRECT(ADDRESS(1,MOD(ROW()-2,$B$2)+3,4,1,$B$1))
-How do you know that you have to add 3 to this part: ADDRESS(1,MOD(ROW()-2,$B$2)+3
-Why do you deduct 2 from Row()?
-When I evaluate the function, $B$1 gives me the name of sheet1? How could a $B$1 represent a sheet?
-I have around 400 time sheets, do you think should I copy and paste each one and put it in my workbook in timesheet tab?

- Each time I plug the new data into time sheet. Should I copy and paste the content of the previous sheet with value and start over writing the function for the new time sheet?

Look forward to hearing from you. I have to start this project on Monday....Thaaaaannnnkkkkk you.
GN0001
 
1-COUNT(INDIRECT(B1&"!"&"1:1"))
It just counts numbers in first row of selected sheet, that's how we decide how many times we have to repeat each row in our new table (i.e. if we have 6 days, we'll have project 1 repeated 6 times, if you add a new day - 7 times). It's the same as count(Timesheet!1:1), indirect is so you can specify sheet to use.

2-(B2)*(COUNTA(INDIRECT("Timesheet!A:A"))-1)+1
Oh, it's purely for information, not needed for final result, just counts how many records there should be in new table, the number here tells to which row formula should be extended, so you are sure all the entries are there. Number of dates multiplied by number of projects.

3-INDIRECT(ADDRESS(1,MOD(ROW()-2,$B$2)+3,4,1,$B$1))
-How do you know that you have to add 3 to this part: ADDRESS(1,MOD(ROW()-2,$B$2)+3
We have to take data from columns C to H, C = 3, our formula cycles from 0 to 5 (see below), so we have to add 3 to get data from correct position. It's just an offset

-Why do you deduct 2 from Row()?
Row() here acts as a counter, since first formula is in Row 2, we have to substract 2 so we start at zero. Then we divide Row-2 by number of days and, in case of rows, round it down , so we have a row number repeated six times before it switches to next row in original sheet , or in case of columns, we get a remainder, so numbers cycle from 0 to 5. +/- constants are just offsets, so we start picking data from correct positions. In case of dates, you only need row 1, in case of project names, you only need column 1, that's why we only use constants there, in case of hours we need both formulas.

-When I evaluate the function, $B$1 gives me the name of sheet1? How could a $B$1 represent a sheet?
Well, unfortunately, sheet name in B1 is entered manually, you specify where to pull data from. And this leads us to your next question:

-I have around 400 time sheets, do you think should I copy and paste each one and put it in my workbook in timesheet tab?
No, you just enter sheet name in B1 and it will pull data for you from that sheet. But 400 is a lot! This solution is better suited for few sheets with lots of data, rather than hundreds of sheets with little data. I think macro will be better suited for this job.

- Each time I plug the new data into time sheet. Should I copy and paste the content of the previous sheet with value and start over writing the function for the new time sheet?
No, it should pull everything automatically just fine (that's what counter in B2 is for), as long as pattern remains the same, you can add new days and projects, just don't forget to extend formula down so you don't miss anything (number in b3 should tell you how many rows should be filled with formula)
 
Last edited:
it should be much easier to understand if you break it down into smallest parts, see file attached

And I know it can be modified further to pull data from multiple sheets, by telling formula to switch to new sheet when number of records in B3 is reached, but you will still have to supply it with sheet names (and there is VBA way to get list of sheets).
 

Attachments

  • reformat explained.xlsx
    38.7 KB · Views: 5
Last edited:
it should be much easier to understand if you break it down into smallest parts, see file attached

And I know it can be modified further to pull data from multiple sheets, by telling formula to switch to new sheet when number of records in B3 is reached, but you will still have to supply it with sheet names (and there is VBA way to get list of sheets).
------------------------------------------------------------------------------
Hello Xen,
I understood all the questions that I have made, however I need to evaluate the formula more. I need clarification on several points.

Question 2, I understand that you made B2 for test, you have deducted -1 and then add +1, I assume you did this, because you deducted 1 for header row of Time sheet and then you added 1, because of the header of the destination row? is this right?


-If the row on the Time sheet starts from row 10, should I still deduct 2 from Row()

-What is the result of MOD(4, 6)? when I evaluate the formula for row 4, for MOD(4,6), the output is 4.
We know if divide 4 by 6 the remainder won't be 4. Why the Mod in the evaluation formula brings back 4?



-I am not clear on this:

- Each time I plug the new data into time sheet. Should I copy and paste the content of the previous sheet with value and start over writing the function for the new time sheet?
No, it should pull everything automatically just fine (that's what counter in B2 is for), as long as pattern remains the same, you can add new days and projects, just don't forget to extend formula down so you don't miss anything (number in b3 should tell you how many rows should be filled with formula)

I populate the destination sheet from row 1 to Row 43
Then I plug in the new time sheet instead of the old time sheet. Then my functions are in Row 44 on, however the data in the new time sheet starts from Row 2 to 10.

The formulas worked when time sheet and destination had similar rows, I mean for example: we put the function in row 2 and data was in row 2 in time sheet.

Can the functions in row 44 pull the data from time sheet tab from row 1 to row 10?


My time sheet has always have the same format. Please let me know.

I have understood what you have done so far, and I am hoping that I can assimilate the same thing on my own sheet at work on Monday. I have not opened the file you have sent me, I am going to work on it tomorrow.

I don't know VBA and I stick to these functions.

Many, many thanks,
GN0001
 
Question 2, I understand that you made B2 for test, you have deducted -1 and then add +1, I assume you did this, because you deducted 1 for header row of Time sheet and then you added 1, because of the header of the destination row? is this right?
I assume you are talking about B3 here. And yes, you are correct here, it's because of headers in timesheet and destenation.

-If the row on the Time sheet starts from row 10, should I still deduct 2 from Row()
No, Row()-2 is counter for destination sheet, you need your counter to start from zero. If you want to start formula from row 10, only then you have to deduct 10.
If time sheet starts from 10, you will have to modify this part:
INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) +3 is offset for timetable.

-What is the result of MOD(4, 6)? when I evaluate the formula for row 4, for MOD(4,6), the output is 4.
We know if divide 4 by 6 the remainder won't be 4. Why the Mod in the evaluation formula brings back 4?
It will be 4. 6*0 + 4 = 4, there are zero sixes, and what remains is 4. For MOD(6,6) it would be 6*1 + 0 = 6, remainder is zero, for MOD(20;6) it would be 6*3 + 2 = 20, reminder is 2.

I populate the destination sheet from row 1 to Row 43
Then I plug in the new time sheet instead of the old time sheet. Then my functions are in Row 44 on, however the data in the new time sheet starts from Row 2 to 10.
You want to combine data from multiple time sheets into one array, right? I will try modify formula so it can work with multiple sheets. Currently, formula only gets you data from one (whatever specified) sheet, I think I can make it to pull data from multiple specified sheets.
I have to do similar things at work, so this experience will be useful for me as well :)
 
Last edited:
Here we go, now it combines data from all the sheets specified in column B (I'll call it sheet info table).
C is counted number of days in that sheet (we later use it to create cycles), D is total number of entries that should be generated from that sheet (used to calculate running total), E is running total of all the entries from all sheets, which is used to reset row counter for each new sheet.

Now, what we do is count rows (L) and compare it to running total with MATCH (M), if current row falls within running total range of X sheet, we use info from that sheet. We take running total of entries until current sheet and deduct it from current row counter, so we can count entries in current sheet from 0. Then counter, sheet name and day count are taken from sheet info table to use in original formula.

As long as all of your timetables are formatted this way, you will have no problem adding them to function, adding days and projects to any timetable won't (shouldn't) break anything either. Extend sheet info table if you need more sheets, extend formula to be sure (all the way from G to Q) to make sure nothing is left out.
If entries start from different positions, you can further modify sheet info table so it includes desired offset, then add it to calculations in same fashion sheet name is added and modify indirect formula so it takes offset from corresponding column instead of using constant +3. Pretty much all the constant that are left in formula are +3 for starting row, +3 for starting column and -1 in column D to throw out header, modify those to suit your needs and you can reformat any table of this style.

As for the part with not taking entries with zeros, it's kinda heavy for recalculation and if you are going to have lots and lots of sheets, you'd better just filter out zeros manually and take what's yours :)
 

Attachments

  • Reformat and combine timetables.xlsx
    97.1 KB · Views: 4
Last edited:
Here we go, now it combines data from all the sheets specified in column B (I'll call it sheet info table).
C is counted number of days in that sheet (we later use it to create cycles), D is total number of entries that should be generated from that sheet (used to calculate running total), E is running total of all the entries from all sheets, which is used to reset row counter for each new sheet.

Now, what we do is count rows (L) and compare it to running total with MATCH (M), if current row falls within running total range of X sheet, we use info from that sheet. We take running total of entries until current sheet and deduct it from current row counter, so we can count entries in current sheet from 0. Then counter, sheet name and day count are taken from sheet info table to use in original formula.

As long as all of your timetables are formatted this way, you will have no problem adding them to function, adding days and projects to any timetable won't (shouldn't) break anything either. Extend sheet info table if you need more sheets, extend formula to be sure (all the way from G to Q) to make sure nothing is left out.
If entries start from different positions, you can further modify sheet info table so it includes desired offset, then add it to calculations in same fashion sheet name is added and modify indirect formula so it takes offset from corresponding column instead of using constant +3. Pretty much all the constant that are left in formula are +3 for starting row, +3 for starting column and -1 in column D to throw out header, modify those to suit your needs and you can reformat any table of this style.

As for the part with not taking entries with zeros, it's kinda heavy for recalculation and if you are going to have lots and lots of sheets, you'd better just filter out zeros manually and take what's yours :)
 
Hello Xen,
Thank you very much for all.
Last night I was up til 3:30 a.m. to put the piece together to understand.

I am trying to learn your first solution. After I get mastery, I will go ahead and learn more the second solution.

-If time sheet starts from 10, you will have to modify this part:
INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) +3 is offset for timetable.

1-Should I replace 3 with 7?
2- I can't remember exactly where the cells starts in the time sheet (I wish I did), but i think it is in row 10 and column 1,
if so, what would this function: INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) change to?
3- Why did you replace comma , with semi colon ; in the formula on the top?
----------------------------------------------------
-I went to tech on the net to see how the row function is being used:
I saw Row(B1:G9) is equal to 1, can you please explain about this?
-----------------------------------------------------------------------------------
-You want to combine data from multiple time sheets into one array, right? I will try modify formula so it can work with multiple sheets. Currently, formula only gets you data from one (whatever specified) sheet

I want to plug in the data of multiple sheets, but one at a time. I pull in the data with functions from one time sheet to destination sheet and then copy and paste the result with value and then delete the sheet and start a new time sheet. I can't have 400 time sheet in one workbook, if so, Excel becomes hassle.

I can do this because:

Since formula is not changing in destination sheet in all the rows and always references are staying the same, I am going to plug in one sheet and then copy and paste the result with values, however I have to copy formula to all cells down the sheet, so I won't type in the formula over and over. This is my temporary solution. What is your thought on this?


I don't have words to thank you enough.

GN0001

------------------------------------------------------------------------------------------------------
 
r1-Should I replace 3 with 7?
With 10. First row number with data = your constant in ADDRESS row argument (one with INT), first column with data = your constant for ADDRESS column argument (one with MOD). Best way to know is just test - put number and see what the result is, if it starts from wrong position, just correct constants.

2- I can't remember exactly where the cells starts in the time sheet (I wish I did), but i think it is in row 10 and column 1
if so, what would this function: INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) change to?
INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+10;1;4;1;$B$1))

3- Why did you replace comma , with semi colon ; in the formula on the top?
I guess it's just regional differences, arguments are separated with ; for me


-I went to tech on the net to see how the row function is being used:
I saw Row(B1:G9) is equal to 1, can you please explain about this?
Row() returns number of current row if no argument is specified, or row of specified cell like in Row(B56) = 56, or first row in range Row(B1:G9) = 1, which is more useful if you are using dynamic ranges, you can also use it in array formulas, say to find last row number in range {max(Row(B1:G9))} will evaluate to 9, since it's translated to MAX(1;2;3;4;5;7;8;9).



I want to plug in the data of multiple sheets, but one at a time. I pull in the data with functions from one time sheet to destination sheet and then copy and paste the result with value and then delete the sheet and start a new time sheet. I can't have 400 time sheet in one workbook, if so, Excel becomes hassle.

Well, in fact you can have this formula in separate book and pull data from other workbooks. If workbook with timetable is open, just type '[bookname.xlsx]sheet name' as a sheet name in column B (which tell formula where to get data from). This way you can save some time, don't need to put timetables into same workbook, rename sheets etc. By the way, you need to type those ' (apostrophes) if you are using first version of solution and your worksheet name contains spaces, I fixed that in last version so you can just type the name as it is.

Since formula is not changing in destination sheet in all the rows and always references are staying the same, I am going to plug in one sheet and then copy and paste the result with values, however I have to copy formula to all cells down the sheet, so I won't type in the formula over and over. This is my temporary solution. What is your thought on this?

Yes you don't have to type formula every time, just correct constants once to match your needs (starting positions for rows and columns, exclude headers from day and row counters if any present etc, if you don't understand it fully, just experiment with numbers until you get correct results), then copy formula all the way down, after that all you have to do is change Sheetname to pull from and copy/paste the results.
 
r1-Should I replace 3 with 7?
With 10. First row number with data = your constant in ADDRESS row argument (one with INT), first column with data = your constant for ADDRESS column argument (one with MOD). Best way to know is just test - put number and see what the result is, if it starts from wrong position, just correct constants.

2- I can't remember exactly where the cells starts in the time sheet (I wish I did), but i think it is in row 10 and column 1
if so, what would this function: INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) change to?
INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+10;1;4;1;$B$1))

3- Why did you replace comma , with semi colon ; in the formula on the top?
I guess it's just regional differences, arguments are separated with ; for me


-I went to tech on the net to see how the row function is being used:
I saw Row(B1:G9) is equal to 1, can you please explain about this?
Row() returns number of current row if no argument is specified, or row of specified cell like in Row(B56) = 56, or first row in range Row(B1:G9) = 1, which is more useful if you are using dynamic ranges, you can also use it in array formulas, say to find last row number in range {max(Row(B1:G9))} will evaluate to 9, since it's translated to MAX(1;2;3;4;5;7;8;9).



I want to plug in the data of multiple sheets, but one at a time. I pull in the data with functions from one time sheet to destination sheet and then copy and paste the result with value and then delete the sheet and start a new time sheet. I can't have 400 time sheet in one workbook, if so, Excel becomes hassle.

Well, in fact you can have this formula in separate book and pull data from other workbooks. If workbook with timetable is open, just type '[bookname.xlsx]sheet name' as a sheet name in column B (which tell formula where to get data from). This way you can save some time, don't need to put timetables into same workbook, rename sheets etc. By the way, you need to type those ' (apostrophes) if you are using first version of solution and your worksheet name contains spaces, I fixed that in last version so you can just type the name as it is.

Since formula is not changing in destination sheet in all the rows and always references are staying the same, I am going to plug in one sheet and then copy and paste the result with values, however I have to copy formula to all cells down the sheet, so I won't type in the formula over and over. This is my temporary solution. What is your thought on this?

Yes you don't have to type formula every time, just correct constants once to match your needs (starting positions for rows and columns, exclude headers from day and row counters if any present etc, if you don't understand it fully, just experiment with numbers until you get correct results), then copy formula all the way down, after that all you have to do is change Sheetname to pull from and copy/paste the results.
r1-Should I replace 3 with 7?
With 10. First row number with data = your constant in ADDRESS row argument (one with INT), first column with data = your constant for ADDRESS column argument (one with MOD). Best way to know is just test - put number and see what the result is, if it starts from wrong position, just correct constants.

2- I can't remember exactly where the cells starts in the time sheet (I wish I did), but i think it is in row 10 and column 1
if so, what would this function: INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+3;1;4;1;$B$1)) change to?
INDIRECT(ADDRESS(INT((ROW()-2)/$B$2)+10;1;4;1;$B$1))

3- Why did you replace comma , with semi colon ; in the formula on the top?
I guess it's just regional differences, arguments are separated with ; for me


-I went to tech on the net to see how the row function is being used:
I saw Row(B1:G9) is equal to 1, can you please explain about this?
Row() returns number of current row if no argument is specified, or row of specified cell like in Row(B56) = 56, or first row in range Row(B1:G9) = 1, which is more useful if you are using dynamic ranges, you can also use it in array formulas, say to find last row number in range {max(Row(B1:G9))} will evaluate to 9, since it's translated to MAX(1;2;3;4;5;7;8;9).



I want to plug in the data of multiple sheets, but one at a time. I pull in the data with functions from one time sheet to destination sheet and then copy and paste the result with value and then delete the sheet and start a new time sheet. I can't have 400 time sheet in one workbook, if so, Excel becomes hassle.

Well, in fact you can have this formula in separate book and pull data from other workbooks. If workbook with timetable is open, just type '[bookname.xlsx]sheet name' as a sheet name in column B (which tell formula where to get data from). This way you can save some time, don't need to put timetables into same workbook, rename sheets etc. By the way, you need to type those ' (apostrophes) if you are using first version of solution and your worksheet name contains spaces, I fixed that in last version so you can just type the name as it is.

Since formula is not changing in destination sheet in all the rows and always references are staying the same, I am going to plug in one sheet and then copy and paste the result with values, however I have to copy formula to all cells down the sheet, so I won't type in the formula over and over. This is my temporary solution. What is your thought on this?

Yes you don't have to type formula every time, just correct constants once to match your needs (starting positions for rows and columns, exclude headers from day and row counters if any present etc, if you don't understand it fully, just experiment with numbers until you get correct results), then copy formula all the way down, after that all you have to do is change Sheetname to pull from and copy/paste the results.



Hello Xen,

My time sheets have the same name.
I think I am equipped enough to take care of this task.

By the way, you need to type those ' (apostrophes) if you are using first version of solution and your worksheet name contains spaces, I fixed that in last version so you can just type the name as it is.

Do you mean I have to refer time sheet in this way?
(INDIRECT(B1&"!"&"1:1"))

Sorry, I can't understand which part you are referring to?
Thanks a lot,
GN0001
 
Yes, it's better replaced with (INDIRECT("'"&B1&"'!"&"1:1"))
It didn't work at all, since we I go to row 45 and then we deduct 2 of it, then on the first week sheet we don't have that row. I will writer you in detail once I am home. Thanks GN0001
 
You'd better post workbook you made, so I can see where the problem is.
Unfortunately, It didn't work, you can see that when we go to row 100, it returns a row which is out of what I have in April WK_April_1-6. My timesheet has only 13 rows and your formula wants to capture data out of row 13,
moreover, I get a lot of zeros, how can I manage it?

Regards,
GN0001
 
Unfortunately, It didn't work, you can see that when we go to row 100, it returns a row which is out of what I have in April WK_April_1-6. My timesheet has only 13 rows and your formula wants to capture data out of row 13,
moreover, I get a lot of zeros, how can I manage it?

Regards, it doesn't allow me to upload the file
 
I can't upload the file, unfortunately, but when you go to row 100, the function can't capture the time sheet rows.
 
Hello Xen,
I have uploaded the files. Please remember that when your formula doesn't work after some rows. When I evaluate the formula it gives a row number like 25. Row 25 doesn't have anything. Time sheet has only 13 rows.
Regards, GN0001
 

Attachments

  • TimeSheet.xlsx
    20.7 KB · Views: 8
  • Destination.xlsx
    61.7 KB · Views: 7
Back
Top