Macro help

[Emmily]

Hi, i need help in creating a macro.

Assumptions: In sheet1 I have the following data

Sheet1

Image deleted. Please use only Sample data - Moderator

In Sheet2, i have the following data which stretches to Column O


Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 157px"><COL style="WIDTH: 144px"><COL style="WIDTH: 125px"><COL style="WIDTH: 129px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">0036577</TD><TD style="TEXT-ALIGN: right">0036577</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD style="TEXT-ALIGN: right">4561</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">0060093</TD><TD style="TEXT-ALIGN: right">0060093</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">0062356</TD><TD>GB0000623560</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">0090498</TD><TD>GB0000904986</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">00100159</TD><TD style="TEXT-ALIGN: right">0100159</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">00118271</TD><TD style="TEXT-ALIGN: right">118271</TD><TD style="TEXT-ALIGN: right">118271</TD><TD>GB0055007982</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I need to compare Col A in sheet 2 to col A in Sheet 1, if there is a match then export data from Sheet 2 to Sheet 1. In sheet 2 there will be duplicates i only require unique data

So in sheet1, the result will look something like this.

Sheet1

Ditto - Moderator

 

[Emmily]

I see now.

In your second sheet, are there always four columns or does it vary? Also, why do you have blank cells in your first sheet? How do I know where to paste the new unique value? Will it always be after column k?

The columns in sheet2 varies, with this particular data it stretches to Col O, but next month it could be S or T. New unique value will be from Col K onwards, where col J will be utilised for value in Col A in sheet 2.

 

[rconverse]

The columns in sheet2 varies, with this particular data it stretches to Col O, but next month it could be S or T. New unique value will be from Col K onwards, where col J will be utilised for value in Col A in sheet 2.

Okay, so we are going cell by cell down columnA in sheet2. Take the cell value from sheet2 and compare to columnA in sheet1. If we don't find a match, we move to the next cell in sheet2. If we find a match in sheet1, then we paste the value from sheet2 into the J column of the row where we matched. So now in that record, cells A and J will be the same value. (I may still be not getting that part, sorry). But, after we find our match, we then check the cells to the right of the cell in sheet2 until there is a blank cell. We're checking to see if that value is in the record on sheet1. If we don't find a match, we paste in k or whatever the next open cell is past k.

Does that sound about right?

What happens next time you need to do this or is this a one time thing?

 

[Emmily]

Almost, maybe it will be easier getting rid of the duplicates in Sheet 2 first before we do the matching.

1. Find duplicates in Sheet, if found then shift one cell to the left, so lets take 0045614 as an example, we can see there are 3* 0045614,

<TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=555 border=0><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 118pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=157 height=17>0045614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 108pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=144>0045614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=125>0045614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=129>4561</TD></TR></TBODY></TABLE>

So we need the code to get rid of the duplicates, so we end up with this

<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=240 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=117 height=17>0045614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=123>4561</TD></TR></TBODY></TABLE>

Assuming we have got rid of all duplicates, we proceed to step 2.

Step2

Compare Col A in sheet 2 to Col A in shee1, if there is a match then copy data in sheet 2 to Sheet 1 and in Col J, so this is the data in Sheet 1

<TABLE style="WIDTH: 690pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=918 border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6509" width=178><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>0045614</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 134pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=178>ANTOFAGASTA PLC </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=122>GB0000456144</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>45614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=96>000045614</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 123pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=164>ANTO LN EQUITY</TD></TR></TBODY></TABLE>

once there is match, the code will do this. Make sense?

<TABLE style="WIDTH: 786pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1046 border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6509" width=178><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=88 height=17>0045614</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 134pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=178>ANTOFAGASTA PLC </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=122>GB0000456144</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>45614</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=96>000045614</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 123pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=164>ANTO LN EQUITY</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64>0045614</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64>4561</TD></TR></TBODY></TABLE>

I will performing the same thing next month, but with new data.

Okay, so we are going cell by cell down columnA in sheet2. Take the cell value from sheet2 and compare to columnA in sheet1. If we don't find a match, we move to the next cell in sheet2. If we find a match in sheet1, then we paste the value from sheet2 into the J column of the row where we matched. So now in that record, cells A and J will be the same value. (I may still be not getting that part, sorry). But, after we find our match, we then check the cells to the right of the cell in sheet2 until there is a blank cell. We're checking to see if that value is in the record on sheet1. If we don't find a match, we paste in k or whatever the next open cell is past k.

Does that sound about right?

What happens next time you need to do this or is this a one time thing?

 

[rconverse]

I will performing the same thing next month, but with new data.

Okay, I understand now. And your numbers are aligned left, so they're text, right?

 

[Emmily]

Okay, I understand now. And your numbers are aligned left, so they're text, right?

There is mixture of text and numeric

 

[rconverse]

There is mixture of text and numeric

Are the values in your column A always text or do they vary?

 

[Emmily]

Are the values in your column A always text or do they vary?

They vary

 

[rconverse]

They vary

Fun. I'll give this a shot, but it may be too much for me to figure out. I'll try though.

 

[Emmily]

Fun. I'll give this a shot, but it may be too much for me to figure out. I'll try though.

Thanks, appreciate your help, this is a challenging macro, which i was told would be impossible to write for, but i always know there is a way, and i know there are experts on this board that can figure this out.

 

[Emmily]

Fun. I'll give this a shot, but it may be too much for me to figure out. I'll try though.

Just to let you know the issue has now been resolved.