CODEWITHSUNDEEP
excel
Andrew Martin
Andrew Martin

Reputation: 151

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

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.

Upvotes: 0

Views: 7217

Answers (1)

nutsch
nutsch

Reputation: 5962

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.

Upvotes: 3

Related Questions