Macro help

Emmily

Well-known Member
Joined
Oct 5, 2008
Messages
676
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
 
Last edited by a moderator:

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
This is sample data in Sheet1

Excel Workbook
ABCDEFGHI
10016308SPEEDY HIRE PLC GB000016308816308000016308SDY LN EQUITY
20036577ANGLO-EASTERN PLANTATIONS PLC GB000036577436577000036577AEP LN equity
30045614ANTOFAGASTA PLC GB000045614445614000045614ANTO LN EQUITY
Sheet1


This is the result i want in Sheet1

Excel Workbook
ABCDEFGHIJK
10016308SPEEDY HIRE PLC GB000016308816308000016308SDY LN EQUITY
20036577ANGLO-EASTERN PLANTATIONS PLC GB000036577436577000036577AEP LN equity
30045614ANTOFAGASTA PLC GB000045614445614000045614ANTO LN EQUITY00456144561
Sheet1
 
Upvote 0
Is there a certain reason you need it in a macro? Would an Excel query work?

hmm, i'm not too familiar with excel query. I do have around 12000 rows of data in sheet 1 and 6000 in sheet2, i thought macro would be the best solution since i need to find a match and copy data to sheet 1 according to the match.
 
Upvote 0
Any reason why a vlookup won't work?

How would i get Vlookup to do this, below i have provided sample in sheet 1 and sheet 2, and the third screen shot is what i would like to achieve, you can see i have taken duplicates out when copying to sheet 1 in the third screen shot.

Excel Workbook
ABCDEFGHI
10016308SPEEDY HIRE PLC GB000016308816308000016308SDY LN EQUITY
20036577ANGLO-EASTERN PLANTATIONS PLC GB000036577436577000036577AEP LN equity
Sheet1



Excel Workbook
ABCD
100365770036577
20045614004561400456144561
Sheet2


Final result in sheet 1

Excel Workbook
ABCDEFGHIJK
10016308SPEEDY HIRE PLC GB000016308816308000016308SDY LN EQUITY
20036577ANGLO-EASTERN PLANTATIONS PLC GB000036577436577000036577AEP LN equity0036577
30045614ANTOFAGASTA PLC GB000045614445614000045614ANTO LN EQUITY00456144561
Sheet1
 
Upvote 0
So you want to bring all new records from sheet1 to sheet2, but there are duplicate records in sheet2. You only want to copy over unique records?

This is how I would do this.

insert a column into sheet2 as a new columnA. Insert the formula:

=if(isna(vlookup(B2,sheet1!A1:A40000, 1, false), "N", "Y")

The bolded is your range in the sheet1 that you are looking to find an exact match of what you have in columnB (formerly comunA) of your sheet2. If ther is a match, this will return a "Y". If there is no match found, then it will return a "N". Copy the formula down the spreadsheet.

Copy the column with the formula | Paste Special | Values

Sort and remove all "N" recrods from your spreadsheet. Now you have any new records, so let's check for duplicates.

clear the contents of column A. Sort ascending by columnB. In column A, enter this formula

=if(B2=B3, 0, 1)

Copy this formula down the spreadsheet.

Copy that column and paste special | values

No sort by your column A and remove all zeros. The remaining records will be new uniue records to copy over to sheet1.

HTH
Roger
 
Upvote 0
Nope, i think you misread my question, what i am after is to do a lookup in Col A in sheet 2 to col A in sheet 1, if there is a match then copy records from Sheet 2 to sheet 1, so this is the data in sheet 1. A macro would be the mest method since i have so many rows of data.


Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 88px"><COL style="WIDTH: 178px"><COL style="WIDTH: 64px"><COL style="WIDTH: 122px"><COL style="WIDTH: 78px"><COL style="WIDTH: 96px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 164px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD>ANTOFAGASTA PLC </TD><TD> </TD><TD>GB0000456144</TD><TD style="TEXT-ALIGN: right">45614</TD><TD style="TEXT-ALIGN: right">000045614</TD><TD> </TD><TD> </TD><TD>ANTO LN EQUITY</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


Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 157px"><COL style="WIDTH: 144px"><COL style="WIDTH: 125px"><COL style="WIDTH: 129px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">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></TBODY></TABLE>

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

This is what i want to achieve in Sheet1, can you see there is a match for 0045614 in sheet2 Col A and Sheet 1 Col A, and i then copied unique records from Sheet 2 to Sheet 1 for for 0045614

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 88px"><COL style="WIDTH: 178px"><COL style="WIDTH: 64px"><COL style="WIDTH: 122px"><COL style="WIDTH: 78px"><COL style="WIDTH: 96px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 164px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD>ANTOFAGASTA PLC </TD><TD> </TD><TD>GB0000456144</TD><TD style="TEXT-ALIGN: right">45614</TD><TD style="TEXT-ALIGN: right">000045614</TD><TD> </TD><TD> </TD><TD>ANTO LN EQUITY</TD><TD style="TEXT-ALIGN: right">0045614</TD><TD style="TEXT-ALIGN: right">4561</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




So you want to bring all new records from sheet1 to sheet2, but there are duplicate records in sheet2. You only want to copy over unique records?

This is how I would do this.

insert a column into sheet2 as a new columnA. Insert the formula:

=if(isna(vlookup(B2,sheet1!A1:A40000, 1, false), "N", "Y")

The bolded is your range in the sheet1 that you are looking to find an exact match of what you have in columnB (formerly comunA) of your sheet2. If ther is a match, this will return a "Y". If there is no match found, then it will return a "N". Copy the formula down the spreadsheet.

Copy the column with the formula | Paste Special | Values

Sort and remove all "N" recrods from your spreadsheet. Now you have any new records, so let's check for duplicates.

clear the contents of column A. Sort ascending by columnB. In column A, enter this formula

=if(B2=B3, 0, 1)

Copy this formula down the spreadsheet.

Copy that column and paste special | values

No sort by your column A and remove all zeros. The remaining records will be new uniue records to copy over to sheet1.

HTH
Roger
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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