+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 23

Reorganize table to combine each pair of records into one record

  1. #1
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Reorganize table to combine each pair of records into one record

    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.
    Last edited by 6StringJazzer; 12-20-2018 at 11:25 AM. Reason: Moderator updated title, added code tags for spacing

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    13,905

    Re: Table

    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

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Springfield, MA
    MS-Off Ver
    2016
    Posts
    547

    Re: Reorganize table to combine each pair of records into one record

    I would suggest a pivot table.

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

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Reorganize table to combine each pair of records into one record

    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



  5. #5
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

    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!
    Attached Images Attached Images
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

    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.

  7. #7
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Springfield, MA
    MS-Off Ver
    2016
    Posts
    547

    Re: Reorganize table to combine each pair of records into one record

    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

    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.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,675

    Re: Reorganize table to combine each pair of records into one record

    Thread moved to the appropriate forum section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Springfield, MA
    MS-Off Ver
    2016
    Posts
    547

    Re: Reorganize table to combine each pair of records into one record

    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.

  11. #11
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

    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!
    Attached Images Attached Images

  12. #12
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

    ill be back in a couple hours

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Reorganize table to combine each pair of records into one record

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

  14. #14
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    Online
    Posts
    12

    Re: Reorganize table to combine each pair of records into one record

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

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Reorganize table to combine each pair of records into one record

    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.
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1