Comparing two coloumns in two worksheets and inserting it into a third one

[lazycoder89]

Hi,

I'm having problem in doing the following.
Suppose I have two excel files book1 and book2. Both have two coloumns ID and flag.
I need a macro that can read data from the 'ID' column of the excel(book1) where the value of 'flag' in column is "Y" from one sheet , compare it with the 'ID' of the second excel (book2) and only copy the data only where the IDs matches, to another sheet.

Also I need to know how can it be done in case it is only for two sheets instead of two different excel.

Thanks

 

[pushkardey]

it doesnt matter if the ranges are in same workbook or different

if the ranges are in same workbook refer to the sheets i.e.

thisworkbook.sheets( index_no / "sheet_name").range("A:A")

e.g.

thisworkbook.sheets("Sheet1").range("A:A")
and
thisworkbook.sheets("Sheet2").range("A:A")

If they are in different workbooks give workbook name as well. Make sure both the workbooks are open.

workbook("xyz.xls").sheets("name"/index_no).range("A:A")
and 
workbook("pqr.xls").sheets("name"/index_no).range("A:A")

...
Hope this hepls.....

 

[lazycoder89]

Thank you but I couldn't get it.
can you give me tho code how to do it?

 

[VoG]

You'll need to be more precise - which columns etc.

 

[lazycoder89]

Say I have two workbooks book1.xls and bok2.xls

Both have two coloumns "id" and "flag". In book1 suppose there are 4 id rangin from 1 to 4 and the flags are Y,N,Y,Y respectively.
In the book 2 there are 4 id's ranging from 1 to 4 with flags Y,Y,N,Y respectively.

Now I want a macro that will open these two workbooks and go through the data and find the field that has same flag and copy those in a new excel file.
Like in this case the id 1 an 4 with their flags should get copied.
Hope I have made myself clear

 

[VoG]

Still pretty vague. Maybe this http://download.cnet.com/Excel-Overlay/3000-2077_4-10963782.html

 

[lazycoder89]

what more setails you want. I'll provide.

WHat do you want to know more?

I need a macro for this.