Trying to Separate multiple products within the same line automatically

[alleneure]

I am trying to separate claim data in a wooksheet similar to the table below.

Claim #	Customer Data	Tire 1	Serial #1	Refund 1	Tire 2	Serial #2	Refund 2	Tire 3	Serial #3	Refund 3
W0001	Jane Doe	225	P3RTY	$ 100.00	225	P4RTY	150	225	P5RTY	200

I would like to take the data above and turn it into the table below preferably with formulas but I know I may have to resort to macros.

Claim #	Customer Data	Tire	Serial	Refund
W0001	Jane Doe	225	P3RTY	$ 100.00
W0001	Jane Doe	225	P4RTY	$ 150.00
W0001	Jane Doe	225	P5RTY	$ 200.00

Right now I have a sheet with a table with the data from the top table and I have a converter sheet to make the below table format and then I copy and paste the ew format into another table.
Is there another way to do this to update this automatically with formulas? Sometimes the claim will only have one or two tires not the full three.

 

[hagia_sofia]

Hello, the following should work for the example but the question is whether it covers all of what is needed:

=LET(
a,WRAPROWS(TOCOL(C2:K2,1),3),
b,IF(SEQUENCE(ROWS(a)),A2:B2),
c,HSTACK(A1:B1,TEXTBEFORE(C1:E1," ")),
VSTACK(c,HSTACK(b,a)))

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Claim #	Customer Data	Tire 1	Serial #1	Refund 1	Tire 2	Serial #2	Refund 2	Tire 3	Serial #3	Refund 3
2	W0001	Jane Doe	225	P3RTY	100	225	P4RTY	150	225	P5RTY	200
3	W0002	John Smith	400	P6RTY	100	500	P7RTY	150
4
5	Claim #	Customer Data	Tire	Serial	Refund
6	W0001	Jane Doe	225	P3RTY	100
7	W0001	Jane Doe	225	P4RTY	150
8	W0001	Jane Doe	225	P5RTY	200
9	W0002	John Smith	400	P6RTY	100
10	W0002	John Smith	500	P7RTY	150
Sheet1

Cell Formulas
Range	Formula
A6:E10	A6	=LET(h,HSTACK(TOCOL(IFS(A2:A3>=SEQUENCE(,3),A2:A3),2),TOCOL(IFS(B2:B3>=SEQUENCE(,3),B2:B3),2),WRAPROWS(TOROW(C2:K3),3)),FILTER(h,CHOOSECOLS(h,5)<>0))
Dynamic array formulas.

 

[alleneure]

Hello, the following should work for the example but the question is whether it covers all of what is needed:

=LET(
a,WRAPROWS(TOCOL(C2:K2,1),3),
b,IF(SEQUENCE(ROWS(a)),A2:B2),
c,HSTACK(A1:B1,TEXTBEFORE(C1:E1," ")),
VSTACK(c,HSTACK(b,a)))

This looks like its almost there! Any way you could provide a little more insight into the logic behind it?

 

[alleneure]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Claim #	Customer Data	Tire 1	Serial #1	Refund 1	Tire 2	Serial #2	Refund 2	Tire 3	Serial #3	Refund 3
2	W0001	Jane Doe	225	P3RTY	100	225	P4RTY	150	225	P5RTY	200
3	W0002	John Smith	400	P6RTY	100	500	P7RTY	150
4
5	Claim #	Customer Data	Tire	Serial	Refund
6	W0001	Jane Doe	225	P3RTY	100
7	W0001	Jane Doe	225	P4RTY	150
8	W0001	Jane Doe	225	P5RTY	200
9	W0002	John Smith	400	P6RTY	100
10	W0002	John Smith	500	P7RTY	150
Sheet1

Cell Formulas
Range	Formula
A6:E10	A6	=LET(h,HSTACK(TOCOL(IFS(A2:A3>=SEQUENCE(,3),A2:A3),2),TOCOL(IFS(B2:B3>=SEQUENCE(,3),B2:B3),2),WRAPROWS(TOROW(C2:K3),3)),FILTER(h,CHOOSECOLS(h,5)<>0))
Dynamic array formulas.

Any way you could provide a little more insight into the logic behind it?

 

[alleneure]

Hello, the following should work for the example but the question is whether it covers all of what is needed:

=LET(
a,WRAPROWS(TOCOL(C2:K2,1),3),
b,IF(SEQUENCE(ROWS(a)),A2:B2),
c,HSTACK(A1:B1,TEXTBEFORE(C1:E1," ")),
VSTACK(c,HSTACK(b,a)))

When implementing this the tires are good but the specific claim and customer number just populate once as shown in the table below.

Claim Number	Customer Data	Tire	Serial	Refend
W0001	Jane Doe	255	P3RTY	100
W0002	John Smith	225	P4RTY	150
#N/A	#N/A	255	P5RTY	200
#N/A	#N/A	256	P6RTY	101
#N/A	#N/A	225	P7RTY	151

Source Data:

Claim Number	Customer Data	Tire 1	Serial 1	Refend 1	Tire 2	Serial 2	Refend 2	Tire 3	Serial 3	Refend 3
W0001	Jane Doe	255	P3RTY	100	225	P4RTY	150	255	P5RTY	200
W0002	John Smith	256	P6RTY	101	225	P7RTY	151

 

[hagia_sofia]

Hello, please use the formula posted by @Cubist - it will work.

 

[alleneure]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Claim #	Customer Data	Tire 1	Serial #1	Refund 1	Tire 2	Serial #2	Refund 2	Tire 3	Serial #3	Refund 3
2	W0001	Jane Doe	225	P3RTY	100	225	P4RTY	150	225	P5RTY	200
3	W0002	John Smith	400	P6RTY	100	500	P7RTY	150
4
5	Claim #	Customer Data	Tire	Serial	Refund
6	W0001	Jane Doe	225	P3RTY	100
7	W0001	Jane Doe	225	P4RTY	150
8	W0001	Jane Doe	225	P5RTY	200
9	W0002	John Smith	400	P6RTY	100
10	W0002	John Smith	500	P7RTY	150
Sheet1

Cell Formulas
Range	Formula
A6:E10	A6	=LET(h,HSTACK(TOCOL(IFS(A2:A3>=SEQUENCE(,3),A2:A3),2),TOCOL(IFS(B2:B3>=SEQUENCE(,3),B2:B3),2),WRAPROWS(TOROW(C2:K3),3)),FILTER(h,CHOOSECOLS(h,5)<>0))
Dynamic array formulas.

Once the logic was understood I easily modified this to work with my full data set! Thank you!