Transform horizontal data to vertical data

[christian79]

Hello,

I have a sheet I need to transform from data being stored horizontally, like the following:

Company ID 1	DoB 1	Name 1	DoB 2	Name 2	DoB 3	Name 3
Company ID 2	DoB 1	Name 1	DoB 2	Name 2
...	...	...	...	...	...	...

Into the following:

Company ID 1	DoB 1	Name 1
Company ID 1	DoB 2	Name 2
Company ID 1	DoB 3	Name 3
Company ID 2	DoB 1	Name 1
Company ID 2	DoB 2	Name 2

The data is structured such that the DoB/name entities always occur in as above horizontally (so that offsets can be used).

I've written a VBA script identifying and coloring/counting all the DoBs, but I need one row for each DoB/name entity, in relation to the company ID. Being a one-time operation and thus making performance uninportant, I just loop over all rows and identify things based on offsets.

How can I break each row up into several rows and paste the parts sequentially after eachother?

Thankful for any help!

 

[Fluff]

Hi & welcome to MrExcel.
Do you need a macro as this can be done with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Company ID 1	DoB 1	Name 1	DoB 2	Name 2	DoB 3	Name 3			Company ID 1	DoB 1	Name 1
3	Company ID 2	DoB 1	Name 1	DoB 2	Name 2					Company ID 1	DoB 2	Name 2
4										Company ID 1	DoB 3	Name 3
5										Company ID 2	DoB 1	Name 1
6										Company ID 2	DoB 2	Name 2
7
Main

Cell Formulas
Range	Formula
J2:L6	J2	=LET(RngA,A2:A3,RngB,B2:G3,c,COLUMNS(RngB)/2,s,SEQUENCE(ROWS(RngA)*c,,0),hs,HSTACK(INDEX(RngA,INT(s/c)+1),INDEX(RngB,INT(s/c)+1,MOD(s,c)*2+{1,2})),FILTER(hs,INDEX(hs,,2)<>""))
Dynamic array formulas.

 

[christian79]

Hi & welcome to MrExcel.
Do you need a macro as this can be done with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Company ID 1	DoB 1	Name 1	DoB 2	Name 2	DoB 3	Name 3			Company ID 1	DoB 1	Name 1
3	Company ID 2	DoB 1	Name 1	DoB 2	Name 2					Company ID 1	DoB 2	Name 2
4										Company ID 1	DoB 3	Name 3
5										Company ID 2	DoB 1	Name 1
6										Company ID 2	DoB 2	Name 2
7
Main

Cell Formulas
Range	Formula
J2:L6	J2	=LET(RngA,A2:A3,RngB,B2:G3,c,COLUMNS(RngB)/2,s,SEQUENCE(ROWS(RngA)*c,,0),hs,HSTACK(INDEX(RngA,INT(s/c)+1),INDEX(RngB,INT(s/c)+1,MOD(s,c)*2+{1,2})),FILTER(hs,INDEX(hs,,2)<>""))
Dynamic array formulas.

Thank you for the reply! Very cool, and somewhat above my formula knowledge level

I get #NAME and #UNKNOWN errors in evaluation, though. Any ideas why?

 

[Fluff]

Were you trying this in xl 365?

 

[christian79]

Were you trying this in xl 365?

Yes. And replaced the commas with semicolons.

 

[JvdV]

Assuming data in A1: G2 and further down if need be:

=HSTACK(TOCOL(IF(ISEVEN(COLUMN(B1:G2))*(B1:G2<>""),A1:A2,NA()),3),WRAPROWS(TOCOL(B1:G2,1),2)

 

[Fluff]

Ok, you may not have HSTACK yet, how about

=LET(RngA,A2:A3,RngB,B2:G3,c,COLUMNS(RngB)/2,s,SEQUENCE(ROWS(RngA)*c,,0),b,INDEX(RngB,INT(s/c)+1,MOD(s,c)*2+{1\2}),hs,choose({1\2\3},INDEX(RngA,INT(s/c)+1),index(b,,1),INDEX(b,,2)),FILTER(hs,INDEX(hs,,2)<>""))

 

[christian79]

Ok, you may not have HSTACK yet, how about

=LET(RngA,A2:A3,RngB,B2:G3,c,COLUMNS(RngB)/2,s,SEQUENCE(ROWS(RngA)*c,,0),b,INDEX(RngB,INT(s/c)+1,MOD(s,c)*2+{1\2}),hs,choose({1\2\3},INDEX(RngA,INT(s/c)+1),index(b,,1),INDEX(b,,2)),FILTER(hs,INDEX(hs,,2)<>""))

This one worked! Seems like HSTACK is indeed missing for some reason. JvdV, couldn't try your solution for that reason.

Many thanks to both of you!

 

[Fluff]

Glad we could help & thanks for the feedback.
If you are on the semi-annual channel you'll probably get HSTACK (and 13 other functions) in January.

 

[gargamalebarbosa]

Hi @JvdV , is it possible change the formula for this result ?

Thank you,