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!

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.

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.

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.

Originally Posted by 6StringJazzer

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!

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.