VBA code - organize data

[Jtwa]

I'm looking for someone to help me with VBA code so I click a button and my sheet 1 becomes organized like the data in Sheet 2


I would want the data in Column A,B,C to be used and added to the end of column D.


Key Points, I would want
Groomsman converted to the letter "g"
Best Man converted to the letter "e"
Usher converted to the letter "u"
Bridesmaid to "b"
Maid of Honor to "m"
Matron of Honor to "t"


You'll notice that "Card Name" is empty in 1 row so the data is structured differently.

Title	Quantity	Card Name	Name	Address 1	Address 2	City	State	Zip
Funny Card (Fred) I want this (Tru)	1 2		John Doe	612 Greenhill Gardens		HERNE BAY	CA	90210
Funny Card (Kev) I want this (dwi) and (kim)	1 2 5	Groomsman Best Man Usher	Kevin Lowe	6024 Ne Revere Ave		BEND	OR	97701
Birthday Card (Joe)	1	Best Man	Terry Green	122415 Windmill Cove Dr	Unit 2	RIVERVIEW	FL	84532
Another Card (Ki) and (lem) and (bey)	1 3 6	Maid of Honor Bridesmaid Matron of Honor	Jerry Yellow	33333 Windmill Cove Dr	Unit 2	RIVERVIEW	FL	84532

<tbody>
</tbody>

Name	Address 1	Address 2	City	State	Zip
John Doe (Fred1 Tru2)	612 Greenhill Gardens		HERNE BAY	CA	90210
Kevin Lowe (Kev-g1 Dwi-e2 Kim-U5)	6024 Ne Revere Ave		BEND	OR	97701
Terry Green (Joe-e1)	122415 Windmill Cove Dr	Unit 2	RIVERVIEW	FL	84532
Jerry Yellow (ki-m1 lem-b3 bey-t6)	33333 Windmill Cove Dr	Unit 2	RIVERVIEW	FL	84532

<tbody>
</tbody>

 

[JLGWhiz]

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
please read these guidelines for posting. There are many contributors who would be willing to help if there were row and column headers provided in the post so the grid locations could be identified. It is extremely difficult to guess at where the data resides and write efficient code. The better the details in the post the better the code provided and the less back and forth in reply posting. How do we determine who the codes for the wedding party belong to?