Convert columns to rows, keeping first column identifier

[lynnsharkey]

Hi all

I have a table with employee identifier data in the first two columns, running down in rows and strings of data (date and times) in the remaining columns. What I need to do is to have the date and time in and date and time out in rows for each employee, but keeping the identifier in the first column. For example:

Current view:

Name ID Date/Time1(in) Date/Time1(out) Date/Time2(in) Date/Time2(out) Date/Time3(in) Date/Time3(out)

What I need is:

Name ID Date/Time1(in) Date/Time(out)
Name ID Date/Time2(in) Date/Time(out)
Name ID Date/Time3(in) Date/Time(out)
Etc

i've attached a sample workbook. Any help in this is greatly appreciated

Thanks

[bluesky63]

Hi,

Easier way is to use power query editor, bring in your cross table (with all your multiple ins and outs), build a query (one off) by unpivotting into a flat table,
standardize all the in and out so that can fit into the pivot column In and Out, Values will be the date and time

See Attached tab 2 "Output (Power Query)" for the results

The M code (mainly using the UI of Power Query Editor

Please Login or Register  to view this content.

[azumi]

Option using formula and using helper table:
1. Put this on P2 and copied down and copied cross until comn Q =IFERROR(INDEX(A$2:A$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-MIN(ROW($A$2:$A$4))+1)/($A$2:$A$4<>""),ROW(A1))),"")
2. Put this on R2 and copied down =SUMPRODUCT(($A$2:$A$4=P2)*(ISNUMBER(SEARCH("In",$C$1:$N$1)*($C$2:$N$4<>""))))

And fo results table,
1. Put this on A10 and copied down and cross until column B =LOOKUP(ROW(A1),SUMIF(OFFSET($R$1,,,ROW($1:$99),),"<>")+1,P$2:P$99)
2. Put this on C10 and press all together CTRL+SHIFT+ENTER button because this an array formula then copied down and cross to column D
=IFERROR(INDEX($C$2:$N$4,SMALL(IF(($A$2:$A$4=$A10)*(ISNUMBER(SEARCH(C$9,$C$1:$N$1))),MATCH(ROW($C$2:$N$4),ROW($C$2:$N$4)),""),COUNTIF($A$10:$A10,$A10)),1*(SMALL(IF(($A$2:$A$4=$A10)*(ISNUMBER(SEARCH(C$9,$C$1:$N$1))),MATCH(ROW($C$2:$N$4),ROW($C$2:$N$4))+1*MATCH(COLUMN($C$2:$N$4),COLUMN($C$2:$N$4)),""),COUNTIF($A$10:$A10,$A10))-SMALL(IF(($A$2:$A$4=$A10)*(ISNUMBER(SEARCH(C$9,$C$1:$N$1))),MATCH(ROW($C$2:$N$4),ROW($C$2:$N$4)),""),COUNTIF($A$10:$A10,$A10)))),"")

[lynnsharkey]

thank you both for your responses. I'll give them a shot!

[Fluff13]

As you have xl365, another option

Please Login or Register  to view this content.

[Czeslaw]

Formulas
Name and ID

Please Login or Register  to view this content.

In

Please Login or Register  to view this content.

Out

Please Login or Register  to view this content.