+ Reply to Thread
Results 1 to 8 of 8

restructuring table data

  1. #1
    Registered User
    Join Date
    08-24-2016
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    4

    restructuring table data

    Hi there!

    My excel spreadsheet look like this now: first row contains users ID number and time she first time oppened an app. The second row contains user ID for the same person, and time when she clicked on the help button in the app. Assume that some of the users do not open the help button. So the table looks like this, although assume that there are 100.000 cases:
    1 5:50
    1 5:55
    2 16:56
    2 16:57
    3 5:40
    4 17:34
    4 17:50
    .
    .
    .

    The issue I am facing is that I want this data to be ordered that in the first collumn contains users ID, the second column the first time she oppened the app, and the third collumn what time/if she oppened the help button. So like this:
    1 5:50 5:55
    2 16:56 16:57
    3 5:40
    4 17:34 17:50
    ....

    How can I do this in excel? Remmember that there are hundreds of thousands cases so copy-paste is not possible.

    Thank you!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    23,246

    Re: restructuring table data

    In your example, the user numbers start at 1 and increase by 1 each time. Is that real, or just a fake example? If it's just an example, what does the data really look like?

    Depending on your data, this could be done with formulas, but might require a macro.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-24-2016
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    4

    Re: restructuring table data

    Hi, thank for your reply 6StringJazzer.

    Yes, each user has a unique ID #, therefore the first one gets value 1, and it increases by 1 each time a new user uses the app. This is fictional data - the above example is just for illustration.
    I just want to manipulate the data in a way that each user would have its own row so the column headers would be userid, time stamp 1 , time stamp 2. That would make it easier to make analyses.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2310 (Windows 11 22H2 64-bit)
    Posts
    72,798

    Re: restructuring table data

    But the unique user IDs will not necessarily occur sequentially? In other words it could be as follows:

    1
    3
    2
    5
    6
    6
    4
    5
    1
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread.
    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.

  5. #5
    Registered User
    Join Date
    08-24-2016
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    4

    Re: restructuring table data

    Yes, that is correct. What I am trying to do is to find the optimal time for the help button to stay on the screen. Need to cater to the needs of those who want the button there just in case they need help, and those who are annoyed by it.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    23,246

    Re: restructuring table data

    Here is an example of how this could be done with formulas. If this is not what you had in mind then please attach your own file with realistic sample data.

    Column D is the sequence of all possible ID numbers.

    Column E looks up the first time for the corresponding ID number. If that ID number is not found, the result is blank.

    Column F looks up the second time for the corresponding ID number. It does this by setting the lookup area to start in the next row after the first time for that number.

    In the formula in column F change 99 to the maximum number of rows of data you could have.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-24-2016
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    4

    Re: restructuring table data

    Quote Originally Posted by 6StringJazzer View Post
    Here is an example of how this could be done with formulas. If this is not what you had in mind then please attach your own file with realistic sample data.

    Column D is the sequence of all possible ID numbers.

    Column E looks up the first time for the corresponding ID number. If that ID number is not found, the result is blank.

    Column F looks up the second time for the corresponding ID number. It does this by setting the lookup area to start in the next row after the first time for that number.

    In the formula in column F change 99 to the maximum number of rows of data you could have.

    THANK YOU! This is exactly what I was hoping for! If you do not mind, would you explain to me each part of the formula, so that I will understand it? Thank you again for your help!

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    23,246

    Re: restructuring table data

    Column C simply lists all the possible user ID numbers starting with 1.

    Column E

    =IFERROR(VLOOKUP(D1,A:B,2,FALSE),"")

    This uses a very standard VLOOKUP. If you are not familiar with VLOOKUP you should read the Help pages for it. VLOOKUP takes a value, finds it in a range, and then returns the corresponding value that is in the same row as the value found, but some column to the right. It has four arguments:

    D1 is the value you want to look up. In this case, D1 is the user ID number.
    A:B the range where you want to look. It searches in the first column of this range.
    2 is the relative column where the result is found. So if the value in D1 is found in A13, the value in B13 is what is returned.
    FALSE tells whether to find an exact match, or just the nearest one. FALSE means an exact match is required. If an exact match cannot be found, then a VALUE error is returned. TRUE means that it assumes the data is sorted in ascending order; the first time it find a value greater than the desired value, it concludes that the desired value isn't there and uses the next lowest value found. This argument is optional and if omitted, it is assumed to be TRUE.

    IFERROR gives you the option of specifying a result if the first argument results in an error. In this case, if the VLOOKUP fails, then the formula produces a blank cell.

    Column F

    =IFERROR(VLOOKUP(D1,OFFSET($A$1,MATCH(D1,A:A,0),0,99,2),2,FALSE),"")

    This is a little more complicated. There are probably other ways to do this but this is the first one I thought of.

    We are going to use VLOOKUP in the same way as above. Instead of searching A:B, we want to start the search after the first occurrence of the user ID in D1. That's because the VLOOKUP in column E has already found the first one. OFFSET returns a range that is a certain number of rows down and columns over from a reference cell. It also can specify the number of rows and columns to include in the result (the default is just to return a single cell).

    To find where we want this range to start, we first have to find the first occurrence of the user ID. That is what MATCH does. MATCH finds D1 in the range A:A, and returns the relative row number within that range where the number is found. Similar to VLOOKUP, the third argument 0 requires an exact match.

    This returns the next row after the first user ID found. So now we want OFFSET to return the two columns A:B but starting at this point. So OFFSET uses the following arguments:

    $A$1 is the starting reference point for the range
    MATCH is how many rows down to move
    0 is how many rows to the right to move (we still want to start in column A)
    99 is how many rows to include in the returned range. This is arbitrary and you should use the maximum possible number of user IDs that you have. I picked 99 because it looks weird and helps remind you to update it.
    2 is the number of columns to include in the returned range. We want A:B so that is 2 columns.

    IFERROR works the same way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Restructuring data from single row to multiple rows
    By fruitmonkey321 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-21-2016, 09:06 AM
  2. Restructuring data
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-11-2014, 08:41 AM
  3. Restructuring data with a macro?
    By JAMatthews in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2013, 01:41 PM
  4. Restructuring Data
    By SimonNZ in forum Excel General
    Replies: 3
    Last Post: 09-22-2010, 10:24 PM
  5. restructuring data
    By Klipvis in forum Excel General
    Replies: 3
    Last Post: 10-21-2009, 05:30 AM
  6. Replies: 0
    Last Post: 02-15-2006, 03:47 PM

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