I have the sample dataset below and the Name column contains the name and the employee number. Each time there is a name the numbers below it corresponds to the same person until a new name appears, so 123 pertains to BOB, 567 pertains to Rachel.
What I am having issues figuring out is how to apply the row with the name in it to all the rows below it for the same employee as shown in the desired result, the column names do not matter.
Thanks in advance!
Sample data:
Name | Date | PAY | TYPE | RATE |
BOB | 08/04/21 | 1111 | STANDARD | 11.1 |
123 | 07/06/21 | 2222 | A | 22.2 |
123 | 07/06/21 | 2222 | B | 22.2 |
RACHEL | 15/04/21 | 3333 | STANDARD | 33.3 |
567 | 22/04/21 | 4444 | C | 44.4 |
567 | 22/04/21 | 4444 | D | 44.4 |
567 | 22/04/21 | 4444 | E | 44.4 |
SMITH | 07/05/21 | 5555 | STANDARD | 55.5 |
889 | 06/05/21 | 6666 | A | 66.6 |
Desired results:
Name | Date | PAY | TYPE | RATE | A | B | C | D | E |
123 | 07/06/21 | 2222 | A | 22.2 | BOB | 08/04/21 | 1111 | STANDARD | 11.1 |
123 | 07/06/21 | 2222 | B | 22.2 | BOB | 08/04/21 | 1111 | STANDARD | 11.1 |
567 | 22/04/21 | 4444 | C | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | 33.3 |
567 | 22/04/21 | 4444 | D | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | 33.3 |
567 | 22/04/21 | 4444 | E | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | 33.3 |
889 | 06/05/21 | 6666 | A | 66.6 | SMITH | 07/05/21 | 5555 | STANDARD | 55.5 |
Solved! Go to Solution.
a multirow tool will work. Question. Is type reliably "standard" on a name row? Because that would be easier to use than the Name for determining when to transition to a new group.
This workflow uses a different multi row tool for each attribute column you want to roll down. However, you could use a single multi row tool to build a "row number" for each data group, then use some kind of cross tab/transpose magic to do it if you have more than the five columns you want to roll down or you want it to be more dynamic.
Edit: Added a Multi Row example v2 that does it dynamically, by using a single multi row tool to create a "groupid" then splitting the standard rows off and joining them to the sub group rows.
Yes standard is on name row.
Thank you another question I have what if I don't want values to repeat and only appear once? See column E:
Desired results:
Name | Date | PAY | TYPE | RATE | A | B | C | D | E |
123 | 07/06/21 | 2222 | A | 22.2 | BOB | 08/04/21 | 1111 | STANDARD | 11.1 |
123 | 07/06/21 | 2222 | B | 22.2 | BOB | 08/04/21 | 1111 | STANDARD | |
567 | 22/04/21 | 4444 | C | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | 33.3 |
567 | 22/04/21 | 4444 | D | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | |
567 | 22/04/21 | 4444 | E | 44.4 | RACHEL | 15/04/21 | 3333 | STANDARD | |
889 | 06/05/21 | 6666 | A | 66.6 | SMITH | 07/05/21 | 5555 | STANDARD | 55.5 |