Hello,
I am trying to take the matching data from multiple rows and put the unique information onto separate columns. I am working with timecard punches. Each time punch in a day is listed on a separate row. I'd like 1 row for the entire day for that employee. Below is a small example of how it's currently formatted versus how I want it to be formatted
EMP # | NAME | DATE | PUNCH TYPE | TIME |
123 | JOHN SMITH | 11/1/23 | ON | 7:05:00 |
123 | JOHN SMITH | 11/1/23 | OFF | 12:00:00 |
123 | JOHN SMITH | 11/1/23 | ON | 12:30:00 |
123 | JOHN SMITH | 11/1/23 | OFF | 16:00:00 |
123 | JOHN SMITH | 11/2/23 | ON | 8:00:00 |
123 | JOHN SMITH | 11/2/23 | OFF | 11:30:00 |
123 | JOHN SMITH | 11/2/23 | ON | 12:00:00 |
123 | JOHN SMITH | 11/2/23 | OFF | 16:00:00 |
124 | JANE DOE | 11/1/23 | ON | 7:00:00 |
124 | JANE DOE | 11/1/23 | OFF | 11:00:00 |
124 | JANE DOE | 11/1/23 | ON | 11:30:00 |
124 | JANE DOE | 11/1/23 | OFF | 3:30:00 |
124 | JANE DOE | 11/2/23 | ON | 9:00:00 |
124 | JANE DOE | 11/2/23 | OFF | 13:00:00 |
124 | JANE DOE | 11/2/23 | ON | 13:30:00 |
124 | JANE DOE | 11/2/23 | OFF | 18:00:00 |
How I want it to be formatted:
EMP # | NAME | DATE | PUNCH 1 | PUNCH 2 | PUNCH 3 | PUNCH 4 |
123 | JOHN SMITH | 11/1/23 | 7:05:00 | 12:00:00 | 12:30:00 | 16:00:00 |
123 | JOHN SMITH | 11/2/23 | 8:00:00 | 11:30:00 | 12:00:00 | 16:00:00 |
124 | JANE DOE | 11/1/23 | 7:00:00 | 11:00:00 | 11:30:00 | 3:30:00 |
124 | JANE DOE | 11/2/23 | 9:00:00 | 13:00:00 | 13:30:00 | 18:00:00 |
Any help would be greatly appreciated!
Solved! Go to Solution.
Hi @tkaufman you can use a Multi-Row tool to create a rank column that you can pivot into a horizontal format. I mocked up approach which showcases one way to achieve this.
This worked like a charm! Thank you SO much for the easy to understand steps!
Tamara