Splitting Row into multiple rows but keeping the identifier

[robk123]

Hello,
I have tried searching but I'm unable to find a solution to this. Hoping to get help on this.

I have a set of data in one row and I would like to split the row into multiple rows but keep the identifier (First Name, Last Name, and Date).

Ultimately, I would like to put this data in a format that I can do pivot table to look at the Name, ET, and Amount

First Name	Last Name	Date	ET1	Amount	Receipt	ET2	Amount	Receipt	ET3	Amount	Receipt
John	Doe	12/1/2022	Airfare	$100	Test.txt	Hotel	$200	Test2.txt	Meals	$300	Test3.txt
Tony	Smith	12/2/2022	Meals	$50	Test4.txt
Eric	XXX	11/30/2022	Supplies	$20	Test5.txt	Misc	$50	Test6.txt

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	First Name	Last Name	Date	ET1	Amount	Receipt	ET2	Amount	Receipt	ET3	Amount	Receipt
2	John	Doe	01/12/2022	Airfare	$100	Test.txt	Hotel	$200	Test2.txt	Meals	$300	Test3.txt
3	Tony	Smith	02/12/2022	Meals	$50	Test4.txt
4	Eric	XXX	30/11/2022	Supplies	$20	Test5.txt	Misc	$50	Test6.txt
5
6	John	Doe	01/12/2022	Airfare	$100	Test.txt
7	John	Doe	01/12/2022	Hotel	$200	Test2.txt
8	John	Doe	01/12/2022	Meals	$300	Test3.txt
9	Tony	Smith	02/12/2022	Meals	$50	Test4.txt
10	Eric	XXX	30/11/2022	Supplies	$20	Test5.txt
11	Eric	XXX	30/11/2022	Misc	$50	Test6.txt
12
Main

Cell Formulas
Range	Formula
A6:F11	A6	=LET(a,CHOOSECOLS(D2:J4,1,4,7),HSTACK(TOCOL(IF(a="",x,A2:A4),2),TOCOL(IF(a="",x,B2:B4),2),TOCOL(IF(a="",x,C2:C4),2),WRAPROWS(TOCOL(D2:L4,1),3)))
Dynamic array formulas.

 

[Automatrix]

Power Query solution: Choose all columns except First Name, Last Name and Date, and choose the "unPivot Columns" action.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	First Name	Last Name	Date	ET1	Amount	Receipt	ET2	Amount2	Receipt3	ET3	Amount4	Receipt5
2	John	Doe	01/12/2022	Airfare	$100	Test.txt	Hotel	$200	Test2.txt	Meals	$300	Test3.txt
3	Tony	Smith	02/12/2022	Meals	$50	Test4.txt
4	Eric	XXX	30/11/2022	Supplies	$20	Test5.txt	Misc	$50	Test6.txt
5
6	First Name	Last Name	Date	Attribute	Value
7	John	Doe	01/12/2022	ET1	Airfare
8	John	Doe	01/12/2022	Amount	$100
9	John	Doe	01/12/2022	Receipt	Test.txt
10	John	Doe	01/12/2022	ET2	Hotel
11	John	Doe	01/12/2022	Amount2	$200
12	John	Doe	01/12/2022	Receipt3	Test2.txt
13	John	Doe	01/12/2022	ET3	Meals
14	John	Doe	01/12/2022	Amount4	$300
15	John	Doe	01/12/2022	Receipt5	Test3.txt
16	Tony	Smith	02/12/2022	ET1	Meals
17	Tony	Smith	02/12/2022	Amount	$50
18	Tony	Smith	02/12/2022	Receipt	Test4.txt
19	Eric	XXX	30/11/2022	ET1	Supplies
20	Eric	XXX	30/11/2022	Amount	$20
21	Eric	XXX	30/11/2022	Receipt	Test5.txt
22	Eric	XXX	30/11/2022	ET2	Misc
23	Eric	XXX	30/11/2022	Amount2	$50
24	Eric	XXX	30/11/2022	Receipt3	Test6.txt
Sheet1