VBA to combine multiple rows into one

[baby0bugs]

I have tried seveeral different scripts and formula, but I can't seem to get this right.

I have several different conditions, but if I can get help with the first one, then I can figure the rest out:

SI = Studlent ID
'MA = _MailingAddress Contact Type
'SA = ShippingAddress Contact Type
'COA = COA Contact Type

For each Student ID Matching Group

'First Case: MA, SA, & COA Exist

'IF _MailingAddress and COA are all listed as a Contact Type THEN
'Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns and add "COA Pending" to Mailing2 column
'Copy the Contact Address columns where the Contact Type = ShippingAddress to the Shipping columns and add "COA Pending" to Shipping2 column

Here is the code I have. It's not looking at the Student ID, it's focused on the RegularAddress. I need it focused on the StudentID and cycle to the next one, after it has found all of the first one.

Note: The final spreadsheet will be sorted by the Student ID.

Below is the sample sheet:

Sample.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	STUDENT_ID	RegularAddress1	RegularAddress2	RegularCity	RegularState	REgularZip	CONTACT_TYPE	CONTACT_ADDRESS_1	CONTACT_ADDRESS_2	CONTACT_ADDRESS_CITY	CONTACT_ADDRESS_STATE	CONTACT_ADDRESS_ZIP
2	46344	608 Platte Road		Fairless Hills	PA	19030	Father	608 Platte Road		Fairless Hills	PA	19030
3	46344	608 Platte Road		Fairless Hills	PA	19030	Mother	608 Platte Road		Fairless Hills	PA	19030
4	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	_MailingAddress	PO Box 429		Mount Jewett	PA	16740
5	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	Father	555 Anderson Street Extension		Mount Jewett	PA	16740
6	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	Mother	555 Anderson Street Extension		Mount Jewett	PA	16740
7	200082	123 Academy Road		Brownsville	PA	15417	_MailingAddress	PO Box 106		Merrittstown	PA	15463
8	200082	123 Academy Road		Brownsville	PA	15417	Father	123 Academy Road		Merrittstown	PA	15463
9	200082	123 Academy Road		Brownsville	PA	15417	Mother	123 Academy Road		Merrittstown	PA	15463
10	200246	102 Division Street		Portland	PA	18351	Father	102 Division Street		Portland	PA	18351
11	200246	102 Division Street		Portland	PA	18351	Mother	102 Division Street		Portland	PA	18351
12	306856	2553 Lynnwood Drive	COA PENDING	East Stroudsburg	PA	18302	_MailingAddress	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301
13	306856	2553 Lynnwood Drive	COA PENDING	East Stroudsburg	PA	18302	COA	111 Aspen Circle	COA Pending	East Stroudsburg	PA	18302
14	306856	2553 Lynnwood Drive	COA PENDING	East Stroudsburg	PA	18302	Father	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302
15	306856	2553 Lynnwood Drive	COA PENDING	East Stroudsburg	PA	18302	Mother	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302
16	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	COA	111 Aspen Circle		East Stroudsburg	PA	18302
17	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Father	2553 Lynnwood Drive		East Stroudsburg	PA	18302
18	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Mother	2553 Lynnwood Drive		East Stroudsburg	PA	18302
19	308438	112 Custer Avenue		Vandergrift	PA	15690	_MailingAddress	PO Box 4			PA
20	308438	112 Custer Avenue		Vandergrift	PA	15690	COA	128 Elder Run Road		Leechburg	PA	15656
21	308438	112 Custer Avenue		Vandergrift	PA	15690	Mother	112 Custer Avenue		Vandergrift	PA	15690
Sample Data

Here is the Sample Output - I'm trying to achieve:

Sample.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	STUDENT_ID	RegularAddress1	RegularAddress2	RegularCity	RegularState	REgularZip	CONTACT_TYPE	CONTACT_ADDRESS_1	CONTACT_ADDRESS_2	CONTACT_ADDRESS_CITY	CONTACT_ADDRESS_STATE	CONTACT_ADDRESS_ZIP	Mailing1	Mailing2	MailingCity	MailingState	MailingZip	Shipping1	Shipping2	ShippingCity	ShippingState	ShippingZip
2	46344	608 Platte Road		Fairless Hills	PA	19030	Father	608 Platte Road		Fairless Hills	PA	19030	608 Platte Road		Fairless Hills	PA	19030	608 Platte Road		Fairless Hills	PA	19030
3	46344	608 Platte Road		Fairless Hills	PA	19030	Mother	608 Platte Road		Fairless Hills	PA	19030	608 Platte Road		Fairless Hills	PA	19030	608 Platte Road		Fairless Hills	PA	19030
4	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	_MailingAddress	PO Box 429		Mount Jewett	PA	16740	PO Box 429		Mount Jewett	PA	16740	555 Anderson Street Extension		Mount Jewett	PA	16740
5	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	Father	555 Anderson Street Extension		Mount Jewett	PA	16740	PO Box 429		Mount Jewett	PA	16740	555 Anderson Street Extension		Mount Jewett	PA	16740
6	80173	555 Anderson Street Extension		Mount Jewett	PA	16740	Mother	555 Anderson Street Extension		Mount Jewett	PA	16740	PO Box 429		Mount Jewett	PA	16740	555 Anderson Street Extension		Mount Jewett	PA	16740
7	200082	123 Academy Road		Brownsville	PA	15417	_MailingAddress	PO Box 106		Merrittstown	PA	15463	PO Box 106		Merrittstown	PA	15463	123 Academy Road		Brownsville	PA	15417
8	200082	123 Academy Road		Brownsville	PA	15417	Father	123 Academy Road		Merrittstown	PA	15463	PO Box 106		Merrittstown	PA	15463	123 Academy Road		Brownsville	PA	15417
9	200082	123 Academy Road		Brownsville	PA	15417	Mother	123 Academy Road		Merrittstown	PA	15463	PO Box 106		Merrittstown	PA	15463	123 Academy Road		Brownsville	PA	15417
10	200246	102 Division Street		Portland	PA	18351	Father	102 Division Street		Portland	PA	18351	102 Division Street		Portland	PA	18351	102 Division Street		Portland	PA	18351
11	200246	102 Division Street		Portland	PA	18351	Mother	102 Division Street		Portland	PA	18351	102 Division Street		Portland	PA	18351	102 Division Street		Portland	PA	18351
12	306856	2553 Lynnwood Drive		East Stroudsburg	PA	18302	_MailingAddress	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301
13	306856	2553 Lynnwood Drive		East Stroudsburg	PA	18302	COA	111 Aspen Circle	COA Pending	East Stroudsburg	PA	18302	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301
14	306856	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Father	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301
15	306856	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Mother	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301	221 Skyline Drive	COA Pending	East Stroudsburg	PA	18301
16	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	COA	111 Aspen Circle		East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302
17	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Father	2553 Lynnwood Drive		East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302
18	308066	2553 Lynnwood Drive		East Stroudsburg	PA	18302	Mother	2553 Lynnwood Drive		East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302	2553 Lynnwood Drive	COA Pending	East Stroudsburg	PA	18302
19	308438	112 Custer Avenue		Vandergrift	PA	15690	_MailingAddress	PO Box 4			PA		PO Box 4	COA Pending		PA		112 Custer Avenue	COA Pending	Vandergrift	PA	15690
20	308438	112 Custer Avenue		Vandergrift	PA	15690	COA	128 Elder Run Road		Leechburg	PA	15656	PO Box 4	COA Pending		PA		112 Custer Avenue	COA Pending	Vandergrift	PA	15690
21	308438	112 Custer Avenue		Vandergrift	PA	15690	Mother	112 Custer Avenue		Vandergrift	PA	15690	PO Box 4	COA Pending		PA		112 Custer Avenue	COA Pending	Vandergrift	PA	15690
22	204561	5732 Chestnut Street		Philadelphia	PA	19139	COA	6010 Cedarhurst Street		Philadelphia	PA	19143	P.O. Box	COA Pending	Philadelphia	PA	19139	5905 Cobbs Creek Parkway	COA Pending	Philadelphia	PA	19149
23	204561	5732 Chestnut Street		Philadelphia	PA	19139	_MailingAddress	P.O. Box		Philadelphia	PA	19139	P.O. Box	COA Pending	Philadelphia	PA	19139	5905 Cobbs Creek Parkway	COA Pending	Philadelphia	PA	19149
24	204561	5732 Chestnut Street		Philadelphia	PA	19139	Other	6010 Cedarhurst Street		Philadelphia	PA	19143	P.O. Box	COA Pending	Philadelphia	PA	19139	5905 Cobbs Creek Parkway	COA Pending	Philadelphia	PA	19149
25	204561	5732 Chestnut Street		Philadelphia	PA	19139	ShippingAddress	5905 Cobbs Creek Parkway		Philadelphia	PA	19149	P.O. Box	COA Pending	Philadelphia	PA	19139	5905 Cobbs Creek Parkway	COA Pending	Philadelphia	PA	19149
Output

And here are all of the Conditions:

Sample.xls
	A
1	SI = Studlent ID
2	'MA = _MailingAddress Contact Type
3	'SA = ShippingAddress Contact Type
4	'COA = COA Contact Type
5
6	For each Student ID Matching Group
7
8	'First Case: MA, SA, & COA Exist
9
10	'IF _MailingAddress and COA are all listed as a Contact Type THEN
11	'Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns and add "COA Pending" to Mailing2 column
12	'Copy the Contact Address columns where the Contact Type = ShippingAddress to the Shipping columns and add "COA Pending" to Shipping2 column
13
14	'Second Case: MA & SA Exist, COA Does Not
15
16	'IF _MailingAddress and ShippingSAddress ARE listed as a Contact Type but COA is NOT listed as a Contact Type THEN
17	'Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns
18	'Copy the Contact Address columns where the Contact Type = ShippingAddress to the Shipping columns
19
20	'Third Case: MA & COA, but not SA
21
22	'If the _MailingAddress and the COA ARE listed as a Contsact Type but SA is NOT listed as a Contact Type THEN
23	'Copy the Contact Address columns where the Contact Type = _MailingAddress to the Mailing columns and add "COA Pending" to Mailing2 column
24	'Copy the Contact Address columns where the Contact Type = COA to the Shipping columns
25
26	'Fourth Case: SA & COA, but not MA
27
28	'If ShippingSAddress and COA ARE listed as a Contact Type but MA is NOT listed as a Contact Type THEN
29	'Copy the Contact Address columns where the Contact Type = COA to the Mailing columns
30	'Copy the Contact Address columns where the Contact Type = ShippingAddress to the Shipping columns
31
32	'Fifth Case: COA, but not MA or SA
33	'If COA IS listed as a Contact Type but MA and SA ARE NOT listed as a Contact Type THEN
34	'Copy the Contact Address columns where the Contact Type = COA to the Mailing columns
35	'Copy the Contact Address columns where the Contact Type = COA to the Shipping columns
36
37	'Sixth Case: No MA, SA or COA
38	'If the _MailingAddress, ShippingAddress and COA is NOT listed as a Contact Type THEN
39	'Copy the Regular Address columns to the Mailing columns
40	'Copy the Regular Address columns to the Shipping columns
Conditions

 

[baby0bugs]

SOLVED