Reorganize table to combine each pair of records into one record

[Needhelpdfec]

Need help getting some data in a usable format, what i have =

Please Login or Register  to view this content.

What i want

Please Login or Register  to view this content.

Any assistance will be greatly appreciated.

[6StringJazzer]

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.

[dosydos]

I would suggest a pivot table.

Then under the values change the Sum of time and change the formatting to TIME.

[Glenn Kennedy]

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.

[Needhelpdfec]

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!

[Needhelpdfec]

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.

[dosydos]

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.

[Needhelpdfec]

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.

[AliGW]

Thread moved to the appropriate forum section.

[dosydos]

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.

[Needhelpdfec]

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!

[Needhelpdfec]

ill be back in a couple hours

[Glenn Kennedy]

Where, in your sample sheet, is your expected answer? If it's not there, please amend and repost.

[Needhelpdfec]

Here it is the sample sheet and the expected is at the bottom

[Glenn Kennedy]

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.

[Needhelpdfec]

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.

[Glenn Kennedy]

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.

[Needhelpdfec]

this is way tooo hard for me

[Glenn Kennedy]

What is way too hard for you? I didnt come back with an alternative yet!!

[Needhelpdfec]

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.

[Glenn Kennedy]

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.

[Needhelpdfec]

My savior! This is exactly what i wanted.
Thank you so much, ill go through the formulas and try to understand them. Thanks again!!!!

[Glenn Kennedy]

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.