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

Complex data dump

R

Raja Kamarthi

New Member
Hello Friends,


Need help in extracting the required numbers out from the data dump which is in a complex and disorganised format. Please refer to the attached file which has 2 sheets

1) Dump & 2) Report.

The data in the dump sheet is basically the login and logout details of the employees which is odd and uneven. I tried all the possible ways but am finding it hard to populate the desired values.

Now based on the data dump, I need the values to get updated in the 2nd sheet i e Report sheet


I know that I'm asking for more, may be something beyond excel limits but am sharing this on this platform with a hope of some resolution


Please refer to the file for more details.


https://www.dropbox.com/s/igi0tuu6jkrywgz/Login%20Dump.xlsx?m


Thanks,

Raja
 
Hi,


Can you please check the below file.


https://www.dropbox.com/s/sxlg4rhr9l0go5y/Login%20Dump%281%29.xlsx


Thanks,

Suresh Kumar S
 
Hi Raja ,


I think you need to start with the data dump , which might be in CSV format , or in some other text format.


There are some entries which span more than one row , which may not be present in that format in the dump ; I think in the process of importing the data into Excel , unwanted carriage returns may be introduced.


Secondly , in the entries which have multiple swipe in and swipe out times , the total hours values does not seem to tally with the swipe times , unless you take in all the breaks in between each swipe out and swipe in. Or is it that in the report sheet , you want the total hours to be directly copied through a formula ?


Lastly , where the swipe out time spills over to another day , the swipe out time will be numerically less than the swipe in time , unless you add 24 hours to the swipe out time.


Doing all this using only formulae is a cumbersome job ; is VBA acceptable ?


Narayan
 
Hello Suresh,


Many Thanks for sparing your time and working on my file.Just had a glance of your file and I must say you succeeded in resolving my query but only to some extent. The last swipe out value is mismatching for the employees. Nonetheless I really appreciate your effort


Regards,

Raja
 
@NARAYANK991:


Much Thanks for your response. You got those points absolutely right.

1)There are few enteries which are spanning more than a row but this is how the tool is exporting the data and added to this we dont have have an option to customize the exporting procedure. This is something out of my hands


2) The total hours mentioned in the "dump sheet" is the pure login time which doesnt include the break time. This can be direclty reflected to the next sheet "Report"


3) There are many swipe outs which spill over to the next day and we have to indeed add 24 hours to get the desired output


And lastly, I'm fine with VBA, I am fine with anything which gives me a solution


Regards,

Raja
 
Hi Raja ,


Thanks for the clarifications ; one small confirmation required viz. that the format of the input data ( the fields and the number of columns ) will be the same , since the macro will have to be tailored for the format.


Narayan
 
Hello Narayan,


Happy to see that you are heading towards the solution.


You can go ahead with the scripting as the format would remain the same.


And I'm hoping that the values which would reflect in the "Report" sheet would be in time format.


Regards,

Raja
 
Hello Narayan:


Thank you so much for getting this done. This is phenomenal. Appreciate your work & support. I'm very pleased with your resolution.


You almost got the report perfect however missing out only on few points as mentioned below:


1) First Swipe In & Last Swipe Out are accurate.


2) I have randomly checked the break time for few employees and found out that the numbers are correct for most of the employees and for a set of employees the break time is incorrect. I have highlighted the same in the shared file for you.


3)Lastly, in shared file, row # 69, GOVARDHAN KARNATI is a part of FIDELITY SYSTEMS but the report shows as PMO. Also in the dump sheet we have Pavan Chavali, PMO which is not reflecting in the VBA Report.


4)Column I & J [Total hours & Total hours (no breaks)] can be derived with a formula based on 1st swipe in and last swipe out and break time so the only critical thing to be worked is the "BREAK TIME". Please refer to the shared file which talks more on this.


Apart from this, I want to know if this VBA script would be good to run if there are new additions in the dump file.


Also, on a given day if there are couple of employees who have multiple login and logouts i e more than 20 logins and 20 logouts, Can I still use this template?


https://www.dropbox.com/s/f7n95fa77hby1nh/Login%20Dump.xlsm?m


Please have a look on the above pointers and share your inputs.


Once again, much thanks for your help.


Do let me know if you need any information


Regards,

Raja.
 
Hi Raja ,


Thanks for the detailed feedback ; I'll work on the problems and get back to you.


Some of the problems should be easily resolved ; as for your questions :


1. The same macro should handle any number of employees , as long as you take care to change the data range , which is actually a named range ; in your worksheet , go into the Name Manager , and change the range address of the named range Data_Range.


2. The number of logins and logouts can be any number , provided the dump does not intrude into the column for the serial number. As long as this column is reserved solely for the serial number , the number of logins and logouts is immaterial.


Narayan
 
Hi Narayan,


You make me smile each time you revert to my post :)


A big big thanks for your commendable support.


Your inputs are noted and would be implemented as and when required


Awaiting your revised template.


Regards,

Raja
 
Hello Narayan,


This is absolutely perfect.


Just dumped the yesterday's raw data in the file and the script worked like a magic displaying the final output values with "ZERO ERRORS".


Thanking you is the least way of showing my gratitude for your help.


You made my day, Narayan. This template will drastically cut down my effort and save much time for me.


For me this task was something near to impossible but YOU nailed it.


Hats off to YOU, bro


Thank you so much,

Raja
 
You must log in or register to reply here.
Back
Top