Need help getting some data in a usable format, what i have =
What i wantPlease Login or Register to view this content.
Any assistance will be greatly appreciated.Please Login or Register to view this content.
Need help getting some data in a usable format, what i have =
What i wantPlease Login or Register to view this content.
Any assistance will be greatly appreciated.Please Login or Register to view this content.
Last edited by 6StringJazzer; 12-20-2018 at 11:25 AM. Reason: Moderator updated title, added code tags for spacing
Welcome to the Forum Needhelpdfec!
First, please take the time to review our rules. There aren't many, and they are all important.
Rule #1 says you should have a good title. I am going to update it since it's your first post, but we need your title to tell us enough about your question to be able to tell if we should open the thread. A one-word title won't do that.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
I would suggest a pivot table.
Then under the values change the Sum of time and change the formatting to TIME.
We need more info. The last entry is also for Vincent. It is also a check in. Why is the first one the expected answer and the second one not the expected answer? Will these guys be checking in/out again tomorrow? How do you want that handled? So more data, please AND on an Excel sheet.
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Sorry for not clarifying earlier, I am using google sheets to create a time clock / quota check for employees as well as some other information for me to use.
But i cant get the information in the format i want, i attached the entire file along with some screen shots of what id like to do, if anyone has any suggestions or can help me out that would be amazing!
The guys will be clocking in/out tomorrow , i attached the full document.
Ideally i want to keep track on each employees time in/out and quotas also the info they left feedback on, but i cant get the information in the correct format.
Halp and thanks again.
As i said earlier a pivot table may work. I included one on your sheet4. It breaks it down by person by Date and gives a check in/check out for each person on that day.
The table works the only issue with it is that it wont auto fill it self everyday.
Im looking for something that would automatically fill the time in/out, quota etc in the correct field once the information is entered via google forms.
Thread moved to the appropriate forum section.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
All you have to do is right click on the table and hit refresh. All data entered in your sheet 1 will automatically appear once refreshed.
Hmm i having trouble adding other fields with the table , leaving to go home from work soon too, id love to get it in any variation of this format if possible, and thanks again!
ill be back in a couple hours
Where, in your sample sheet, is your expected answer? If it's not there, please amend and repost.
Here it is the sample sheet and the expected is at the bottom
In your sample each person's check out is immediately after their check in. Let's hope that that is actually what you have.
Try this. ASll formuale are variants of this one:
=IFERROR(INDEX(Sheet1!$B$2:$B$20,1+2*(ROWS($1:1)-1))&"","")
Each shading = slightly different formula. Only the one in E2 can be dragged across and down.
Will this work if people clock in/out in random order? Because that will surely happen, I only input the data one after the other for testing.
Well, isnt that exactly what I suggested might be the case. Pity you didn't give us a representative sample to begin with....
Any formula will now be much more complicated.
I'll take a look in the morning.
this is way tooo hard for me
What is way too hard for you? I didnt come back with an alternative yet!!
been trying to get it to work on my own, il upload what i have done so far for your umm amusement, but yea its hard.
It does take a long time to give you the wrong answer, doesn't it...
A helper, in raw, I2, copied down:
=INT(A2)&" "&B2
Then sort the columns on the basis of row I, the helper. I also included a column to reverse it bacl to the original order in J for testing. You can delete it.
Then variants of the formula that I gave you a mile or so further back up this thread:
=IFERROR(INDEX(Raw!B$2:B$50,1+2*(ROWS($1:1)-1))&"","")
See attached. Each shading = different formula.
My savior! This is exactly what i wanted.
Thank you so much, ill go through the formulas and try to understand them. Thanks again!!!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks