Looking for the best way to transpose data from a single row into multiple rows

[mminten]

I have done VBA stuff in the past but it has been a few years. I attempted to do this with a pivot table but couldn't get it to do it right. Lets start with the raw data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1	#	Date Submitted	Enter Group Code	How many people do you want to register?	my_emma_1	Please select from the following:	Guest 1: First Name	Guest 1: Last Name	Guest 1: Email	guest_1_address	guest_1_city	guest_1_state	guest_1_zip	my_emma_1	Guest 2: First Name	Guest 2: Last Name	Guest 2: Email	Address Same As Guest 1	guest_2_address	guest_2_city	guest_2_state	guest_2_zip	my_emma_2	Guest 3: First Name	Guest 3: Last Name	Guest 3: Email	guest_3_address	guest_3_city	guest_3_state	guest_3_zip	my_emma_3	Guest 4: First Name	Guest 4: Last Name	Guest 4: Email	guest_4_address	guest_4_city	guest_4_state	guest_4_zip	my_emma_4	Guest 5: First Name	Guest 5: Last Name	Guest 5: Email	guest_5_address	guest_5_city	guest_5_state	guest_5_zip	my_emma_5	SECURITY QUESTION: What is 2 plus 2?
2	641	########	348904	2	Checked	no	Joe	Blow	email@email.com	123 Main St	Springfield	CA	95843	Checked	Jane	Blow		Checked					Unchecked								Unchecked								Unchecked								Unchecked
3	644	########	348024	1	Checked	no	John	Smith	fake@email.com	1 First St	RENO	NV	89521-4404	Unchecked				Unchecked			select-state		Unchecked								Unchecked								Unchecked								Unchecked
4	645	########	214704	5	Checked	yes	Jack	Lopez	fakelopez@emai.com	1 Virginia Street	Reno	NV	89501	Checked	Dianna	Lopez	fakelopez2@emai.com	Checked					Checked	Anette	Lopez	fakelopez3@emai.com	100 Second St	Reno	NV	89501	Checked	Dee	Lopez	fakelopez4@emai.com	100 Third Street	Reno	NV	89501	Checked	Breana	Lopez	fakelopez5@emai.com	100 Forth Street	Reno	NV	89501	Checked
raw

Now we want to break out this data to look like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Group No	Band No	Fname	Lname	Email	Address	City	State	Zip	Judging
2	348904	2	Joe	Blow	email@email.com	123 Main St	Springfield	CA	95843	no
3	348904		Jane	Blow	email@email.com	123 Main St	Springfield	CA	95843	no
4	348024	1	John	Smith	fake@email.com	1 First St	RENO	NV	89521-4404	no
5	214704	5	Jack	Lopez	fakelopez@emai.com	1 Virginia Street	Reno	NV	89501	yes
6	214704		Dianna	Lopez	fakelopez2@emai.com	1 Virginia Street	Reno	NV	89501	yes
7	214704		Anette	Lopez	fakelopez3@emai.com	100 Second St	Reno	NV	89501	yes
8	214704		Dee	Lopez	fakelopez4@emai.com	100 Third Street	Reno	NV	89501	yes
9	214704		Breana	Lopez	fakelopez5@emai.com	100 Forth Street	Reno	NV	89501	yes
wanted

This is a key to which col goes where:

Book1
	A	B
1	Enter Group Code	Group No
2	How many people do you want to register?	Band No
3	Guest 1: First Name	Fname
4	Guest 1: Last Name	Lname
5	Guest 1: Email	Email
6	guest_1_address	Address
7	guest_1_city	City
8	guest_1_state	State
9	guest_1_zip	Zip
10	Please select from the following:	Judging
key

Basically what I need to do is look at the How many people do you want to register field and break it out into multiple rows adding each guest's information to the same Group No and Judging. If they don't have a physical address or email we want the data from the original entry to show (although that may be optional).
Is my best bet a Macro enabled spreadsheet and programming complicated VBA or is there a simpler way to do this?

 

[mminten]

No. I didn't explain myself well. Just copy my example from the post #8 and that's it.
The macro is designed to identify the carcacter "#" and replace it with the number 1, 2, 3, etc. That is already done by the macro in automatic, you do not have to put that number.




I mean you can add another column, for example the "Date Submitted" column, and the macro, automatically adds it to the result.

Absolutely Perfect! Thank you!!!! You are a lifesaver and thank you for your patience.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.