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.



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


[TABLE="width: 613"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]John Doe (Fred1 Tru2)[/TD]
[TD]612 Greenhill Gardens[/TD]
[TD][/TD]
[TD]HERNE BAY[/TD]
[TD]CA[/TD]
[TD="align: right"]90210[/TD]
[/TR]
[TR]
[TD]Kevin Lowe (Kev-g1 Dwi-e2 Kim-U5)[/TD]
[TD]6024 Ne Revere Ave[/TD]
[TD][/TD]
[TD]BEND[/TD]
[TD]OR[/TD]
[TD="align: right"]97701[/TD]
[/TR]
[TR]
[TD]Terry Green (Joe-e1)[/TD]
[TD]122415 Windmill Cove Dr[/TD]
[TD]Unit 2[/TD]
[TD]RIVERVIEW[/TD]
[TD]FL[/TD]
[TD="align: right"]84532[/TD]
[/TR]
[TR]
[TD]Jerry Yellow (ki-m1 lem-b3 bey-t6)[/TD]
[TD]33333 Windmill Cove Dr[/TD]
[TD]Unit 2[/TD]
[TD]RIVERVIEW[/TD]
[TD]FL[/TD]
[TD="align: right"]84532[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?