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: NameDatePAYTYPERATEBOB08/04/211111STANDARD11.112307/06/212222A22.212307/06/212222B22.2RACHEL15/04/213333STANDARD33.356722/04/214444C44.456722/04/214444D44.456722/04/214444E44.4SMITH07/05/215555STANDARD55.588906/05/216666A66.6 Desired results: NameDatePAYTYPERATEABCDE12307/06/212222A22.2BOB08/04/211111STANDARD11.112307/06/212222B22.2BOB08/04/211111STANDARD11.156722/04/214444C44.4RACHEL15/04/213333STANDARD33.356722/04/214444D44.4RACHEL15/04/213333STANDARD33.356722/04/214444E44.4RACHEL15/04/213333STANDARD33.388906/05/216666A66.6SMITH07/05/215555STANDARD55.5

Transform data from long to wide by applying top rows to each below

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

Workflow

Transformation

Preparation

Accepted answers

binu_acs

@overhead_press A multirow tool can do the trick

Transpose_27_05.yxmd

All comments

rfoster7

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.

multi row example.yxmd

multi row example v2.yxmd

binu_acs

@overhead_press one way of doing this

Transpose_27_05.yxmd

overhead_press

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

binu_acs

@overhead_press A multirow tool can do the trick

Transpose_27_05.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703