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

Big issue with date format

The Log Values are already in Rows 3,5,7,9,11 etc eg: D5, G11 etc


The changes I made don't use any VBA code it is all with the formulas in the Cells
 
Thanks hui for that it will save me lot of time every day don't know how to thanks you for this.But then I need to have a extra row in the that int he rows 2,3,4 I need info of Emp id 1111, and in 5,6,7 th emp id of 2222 I need to insert a row for each employee id that is the emp id should be merged with the third row and the next column should have shift in the first row, actual time int he second roe,log in the third row. Please help me on this.


I will show like how it should look

Emp Id Team Name Emp Name Details 1-Nov 2-Nov 3-Nov 4-Nov

11111 To Sad Shift Second

time 7:30

Log 0

2222 To Kri Shift First

time 8:30

Log 435


etc for many records
 
Have a look at

http://rapidshare.com/files/431145383/pradeepthota_Hui_V2.xlsx
 
Hi Hui,


What can i say wow!!!!!!!:) for your solution


But can u please explain me how does this work for 10000 or more records.


And also please explain the formula.
 
The solution I have presented will scale to 10,000 or more records on R2

If you start to have more than 1000'ish rows on R1 you may start to see the spreadsheet slow down due to the large number of Sumproducts used.


To expand:

Page R1: Just select Rows 23:25 and drag down. all the formulas will adjust automatically. because your copying 3 rows make sure you drag down a multiple of 3 rows eg : 123 not 124 rows etc


Page R2: Add records to your hearts content. R2 uses named ranges to automatically extend and add new records.


I have made a minor change which will enable the above to happen properly so use this as a base, not V2 from earlier today

http://rapidshare.com/files/431192508/Pradeepthota_Hui_V3.xlsx


Have a look at the named formulas on R2 and read about Offset at: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

Sheet R1 uses Sumproduct to select the correct data from Sheet R2 and return it.

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


Please note that I have removed most of your merged cells, these can make more trouble than there worth.
 
Hi Hui,


The earlier one didnot work for rhis sheet.


http://rapidshare.com/files/433209561/Empdetails1.xls


can you please look at this and let me know why is not working
 
Did you use the Post just above with v3


As I found an error in an earlier version and changed the forula to fix it up
 
in the columns now it is working but the problem is I am not getting the data for the formula

=IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!E$1)*(Employee_ID='r1'!$A3),ROW(Employee_ID))-1,0),0) where I need to get 610 the actual duration but its not showing
 
Whoops


D3 should be

=IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!E$1)*(Employee_ID='r1'!$A2),ROW(Employee_ID))-1,0),0)


Is everyhthing else ok?
 
No still D3 I am not getting a value it is showing as 0 it should show me the actual duration but no I have tried but it failed
 
F3: =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!F$1)*(Employee_ID='r1'!$A2),ROW(Employee_ID))-1,0),0)

and copy across


and


F6: =IFERROR(OFFSET('r2'!$G$1,SUMPRODUCT((Date='r1'!F$1)*(Employee_ID='r1'!$A5),ROW(Employee_ID))-1,0),0)


Copy across

Then copy Row 6 to Rows 9, 12,15 etc
 
Try this XLS version

http://rapidshare.com/files/434046074/Pradeepthota123Hui.xls
 
You must log in or register to reply here.
Back
Top