Macro to re-format source data for physical address list

[lhnorth]

Please see the below. Any help is greatly appreciated! In orange is a sample of what the address list looks like pulled from our master file. I need a macro (or something), than can run on a file like this and generate the sample format / layout in green, which is currently being created manually with a lot of moving cells, etc., as you can imagine.

Format Help 5.7.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	↓↓↓This layout in green is what I need to have generated from the source example given in orange below↓↓↓
2	N Hill Rd. Anytown, PA 90210
3	Last Name	First Name	House Number	Apartment Number	Phone Number
4	Smith	Katherine	100	1	Not Available
5	Grover	David	102		(123) 456-7890
6	Dunn	Shelby	104		Not Available
7
8	Carver St. Anytown, PA 90210
9	Last Name	First Name	House Number	Apartment Number	Phone Number
10	Browser	Carl	202		(123) 867-5309
11	Karter	Mario	204	3	Not Available
12	Glibb	Phil	206		Not Available
13
14	↓↓↓Below this orange line is the source export layout that I need to auto-format as above↓↓↓
15	Last Name	First Name	House Number	Pre-directional	Street	Street Suffix	Post-directional	Apartment Number	City	State	Zip Code	Phone Number
16	Smith	Katherine	100	N	Hill	Rd.		1	Anytown	PA	90210	Not Available
17	Grover	David	102	N	Hill	Rd.			Anytown	PA	90210	(123) 456-7890
18	Dunn	Shelby	104	N	Hill	Rd.			Anytown	PA	90210	Not Available
19	Browser	Carl	202		Carver	St			Anytown	PA	90210	(123) 867-5309
20	Karter	Mario	204		Carver	St		3	Anytown	PA	90210	Not Available
21	Glibb	Phil	206		Carver	St			Anytown	PA	90210	Not Available
Sheet1

 

[igold]

You're welcome. We were happy to help. Thanks for the feedback!

 

[Peter_SSs]

This also works really well- thank you so much for the expertise!

You're welcome. Glad you found it useful.