CODEWITHSUNDEEP
vbaexcelexcel-formula
LN more
LN more

Reputation: 9

Create an event log from an excel file by turning columns into repeated rows

I have an Excel sheet like the following:

ID  Arrival Passed      Berthing Date       UnBerthing Date     Departure Passed 
1   13/05/2017 15:30    13/05/2017 16:00    31/05/2017 20:44    31/05/2017 
2   15/05/2017 16:56    15/05/2017 17:15    16/05/2017 00:00    16/05/2017 
3   20/05/2017 09:54    20/05/2017 10:26    20/05/2017 18:07    20/05/2017 
4   24/05/2017 16:09    24/05/2017 16:35    25/05/2017 01:03    25/05/2017 
5   29/05/2017 10:30    29/05/2017 10:45    29/05/2017 17:33    29/05/2017

I need this in the following format:

ID  Event       Time
1   Arrival     13/05/2017 15:30
1   Berth       13/05/2017 16:00
1   UnBerth     31/05/2017 20:44
1   Departure   31/05/2017 20:58
2   Arrival     15/05/2017 16:56
2   Berth       15/05/2017 17:15
2   UnBerth     16/05/2017 00:00
2   Departure   16/05/2017 00:04

etc

I've searched the web and this site(youtube...), but with no right answer, i've tried the transpose function and pivot table, but i couldn't make it.

Any help would be appreciated.

Thanks you.

Upvotes: 0

Views: 53

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Assuming that your dataset is in range A2:E6.

For getting ID:

=INDEX($A$2:$E$6,CEILING(ROWS($A$1:A1)/4,1),1)

For getting Event:

=CHOOSE(MOD(ROWS($A$1:A1)-1,4)+1,"Arrival","Berth","Unberth","Departure")

For getting Time:

=INDEX($A$2:$E$6,CEILING(ROWS($A$1:A1)/4,1),MOD(ROWS($A$1:A1)-1,4)+2)

and then copy down until you get error.

Upvotes: 1

Related Questions