Search multiple criteria, if match append multiple cells to one cell.

F

furious00

New Member
Joined
Feb 27, 2011
Messages
6
I am having such a difficult time creating a macro that will help me reduce the 5+hours I have to spend each week manually copying & pasting all of this data. If anyone can help, please know that you will be making an IMMENSE difference in this worker bee's life! :)

I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:

<TABLE style="WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; WIDTH: 500px; LETTER-SPACING: normal; BORDER-COLLAPSE: collapse; FONT: 13px Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; WHITE-SPACE: normal; ORPHANS: 2; COLOR: rgb(34,34,34); WORD-SPACING: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" class=wysiwyg_dashes><TBODY><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN A</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN B</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11111</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alabama</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11113</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11114</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD></TR></TBODY></TABLE>
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.

<TABLE style="WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; WIDTH: 500px; LETTER-SPACING: normal; BORDER-COLLAPSE: collapse; FONT: 13px Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; WHITE-SPACE: normal; ORPHANS: 2; COLOR: rgb(34,34,34); WORD-SPACING: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" class=wysiwyg_dashes><TBODY><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN A</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN B</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN C</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11111</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Property</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11111</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Casualty</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Life</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Health</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alabama</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Property</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alabama</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Casualty</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11113</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Life</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11113</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Health</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11114</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Life</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11114</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Health</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>12345</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arizona</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Property</TD></TR></TBODY></TABLE>

I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.

Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.

After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:

<TABLE style="WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; WIDTH: 500px; LETTER-SPACING: normal; BORDER-COLLAPSE: collapse; FONT: 13px Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; WHITE-SPACE: normal; ORPHANS: 2; COLOR: rgb(34,34,34); WORD-SPACING: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px" class=wysiwyg_dashes><TBODY><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN A</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN B</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>COLUMN C</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11111</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alaska</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Property Casualty</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11112</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Alabama</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Property Casualty</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11113</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Life Health</TD></TR><TR class=wysiwyg_dashes_tr vAlign=top><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>11114</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Arkansas</TD><TD style="BORDER-BOTTOM: rgb(153,153,153) 1px dotted; BORDER-LEFT: rgb(153,153,153) 1px dotted; PADDING-BOTTOM: 3px; PADDING-LEFT: 3px; PADDING-RIGHT: 3px; BORDER-TOP: rgb(153,153,153) 1px dotted; BORDER-RIGHT: rgb(153,153,153) 1px dotted; PADDING-TOP: 3px; border-image: initial" class=wysiwyg_dashes_td>Life Health</TD></TR></TBODY></TABLE>
PLEASE HELP!!!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Someone has helped me with some VBA code that almost works. However this macro doesn't seem to be comparing the "State" column and only appears to be comparing the license #'s (column A).

Can anyone help me modify this so that it works correctly?

Code: 
Option Explicit
Sub HopeThisHelp()
 Dim Rng As Range, sRng As Range, Cls As Range, Sh As Worksheet
 Dim MyAdd As String, Rws As Long
  
 Set Sh = Sheet2: Rws = Sh.Rows.Count
 Sheet1.Select
 Set Rng = Sh.Range(Sh.[A1], Sh.Cells(Rws, "A").End(xlUp))
 For Each Cls In Range([A2], [A2].End(xlDown))
    Cls.Offset(, 2).Clear
     
    Set sRng = Rng.Find(Cls.Value, , xlValues, xlWhole)
    If Not sRng Is Nothing Then
        MyAdd = sRng.Address
        Do
            With Cls.Offset(, 2)
                .Value = .Value & " " & sRng.Offset(, 2).Value
            End With
             
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    Else
        MsgBox "Nothing", , Cls.Value
    End If
 Next Cls
End Sub
 
Upvote 0
This seemed to work as you described, but try it on a copy of your worksheets or a mock up before running it on your original.

Code:
Sub TagURit()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, lr1 As Long, lr2 As Long, c As Range, i As Long
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr1)
For Each c In rng
For i = 2 To lr2
If UCase(sh2.Cells(i, 1).Value) = UCase(c.Value) And _
LCase(sh2.Cells(i, 2).Value) = LCase(c.Offset(0, 1).Value) Then
If c.Offset(0, 2) = "" Then
c.Offset(0, 2) = sh2.Cells(i, 3).Value
ElseIf c.Offset(0, 2) > "" Then
c.Offset(0, 2) = c.Offset(0, 2).Value & ", " & sh2.Cells(i, 3).Value
End If
End If
Next
Next
sh1.Columns("C").AutoFit
End Sub
Code
 
Upvote 0
I just ran your macro on the actual data file that I last worked-on this past Monday. What took me over 3 hours to do, your macro did in 20 seconds.

Thank you, again JLGWhiz. THANK YOU, THANK YOU, THANK YOU!
:biggrin:
 
Upvote 0
furious00 said:
I just ran your macro on the actual data file that I last worked-on this past Monday. What took me over 3 hours to do, your macro did in 20 seconds.

Thank you, again JLGWhiz. THANK YOU, THANK YOU, THANK YOU!
:biggrin:
Click to expand...


Glad I could help,
Regards, JLG
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
495
VBAEXCELNew
V
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
513
esben
E
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
913
epactheactor
E

Forum statistics

Threads
1,223,954
Messages
6,175,601
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top