Excel transpose some rows into columns depending on values in another column

Question

I have an Excel file I'm getting that looks something like this:

Time  012345  2345678   5647890 7891234
6:00    1        0         0       1
6:30    1        1         0       1
7:00    1        1         0       0
7:30    0        0         0       0
8:00    0        0         1       0
8:30    0        0         1       0
9:00    0        0         1       0
9:30    0        0         1       0
10:00   0        0         0       0
10:30   1        0         0       1
11:00   1        0         0       1
11:30   1        0         0       1
12:00   1        0         0       1
12:30   0        0         0       0
13:00   0        0         0       0
13:30   0        0         1       0
14:00   0        0         1       0
14:30   0        0         1       0
15:00   0        0         1       0
15:30   1        1         0       0

etc.

The goal is to get these transposed but to take the time values instead of the actual 1s and 0s in the columns. The ideal output would look like this:

userID    startTime1   stopTime1   startTime2   stopTime2   startTime3  stopTime3
012345      6:00         7:30         10:00       12:30        15:00
2345678     6:00         7:30         15:00
5647890     7:30         10:00        13:00       15:30        
7891234     6:00         7:00         10:00       12:30

I've tried setting up a pivot table and filtering, and that hasn't gotten me anywhere. And I'm not sure what else to try. V and HLOOKUPS haven't done the trick for me either.

Part of the problem is that the start time needs to be the timestamp one row above the 1 and the stop time needs to be the timestamp 1 row below the final 1 in a group. Doing simple things like if statements hasn't helped me because I need the first and last time in each group from the column. So I'm pretty stumped.

Any help on this would be appreciated.

First thing first, I recommend taking your source table and Paste Transpose to turn the columns into rows and vice versa. That way, the userID will be the leftmost column. — PowerUser
– PowerUser, Commented Oct 18, 2012 at 21:02
Well, if I transpose the rows and columns, I end up with thousands and thousands of columns that I also have no idea what to do with. I did try a pivottable, but I'm either not setting it up correctly or it's not getting me where I need to go. — Andrew Martin
– Andrew Martin, Commented Oct 18, 2012 at 21:31

nutsch · Accepted Answer · 2012-10-19 00:19:50Z

3

Based on your ideal format, I have formulas that will give you the right results. Assuming your data is in range A1:E21, and that you have copied / transposed your userIDs in range G2:G5.

First start time formula is: =IF(HLOOKUP($G2,$B$1:$E$2,2,1)=1,$A$2,INDEX($A$1:$A$21,MATCH(1,OFFSET($A$1:$A$21,0,MATCH($G2,$1:$1,0)-1,,),0)-1))

First stop time formula (and this can be copied to all stop time columns) is: =IFERROR(INDEX(OFFSET($A$1:$A$21,MATCH(H2,$A$1:$A$21,0),,,),1+MATCH(0,OFFSET($A$1:$A$21,MATCH(H2,$A$1:$A$21,0),MATCH($G2,$1:$1,0)-1,,),0)-1),"")

Second start time formula (which can be copied to all subsequent start time columns: =IFERROR(INDEX(OFFSET($A$1:$A$21,MATCH(I2,$A$1:$A$21,0),,,),MATCH(1,OFFSET($A$1:$A$21,MATCH(I2,$A$1:$A$21,0),MATCH($G2,$1:$1,0)-1,,),0)-1),"")

For a working file sample, check here.

edited Oct 19, 2012 at 0:19

answered Oct 18, 2012 at 22:44

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

nutsch Over a year ago

Thoughts: UserIDs should be in G2:G5, First formula in H2:H5, second in I2:I5, etc. Is that how you have it set up?

Andrew Martin Over a year ago

Thanks so much! That's a big help. I've got a couple of problems. The first Start Time formula works great. When I copy it down to rows H3:H5, I get "Value not available" errors that produce #N/A. Neither the second start time nor the stop time formulas produce an error, but the cells just sit there blank. You can see the formula in the formula bar, but nothing is in the cell. Any thoughts about those things?

Andrew Martin Over a year ago

Yeah, I was just editing to correct that. That is how I set it up.

Andrew Martin Over a year ago

Never mind. When I pasted, the values were converted to text. Changing them back to numbers fixed everything and works like a charm. Thanks again. This was driving me crazy all day.

nutsch Over a year ago

Congratulations on all your new badges. You now have gained the right to upvote answers.

|

Collectives™ on Stack Overflow

Excel transpose some rows into columns depending on values in another column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest