match two excel spread sheet using macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lillian Lian - 17 Sep 2007 18:24 GMT

I have two excel spreed sheet, sheet1 and sheet2 both has same information as
following:
IPAddress, Domain, Username, machinename

sheet1 has 900 records.
sheet2 has 1100 records.

I would like match IPaddress on columnA on sheet1 and sheet2, if they are
match then then move all the sheet1 900 records to sheet2 on columnF,G.H.I

how can I write the macro for this?

Thank you so much

Lillian

Reply to this Message

Otto Moehrbach - 17 Sep 2007 21:39 GMT

Lillian
This macro will loop through all the cells in Column A of sheet1 and
look for that value in Column A of Sheet2. If the value is found, it will
copy Columns A:D of that row from sheet1 and paste it to the next empty cell
in Column F of sheet2. Post back if this is not what you want. HTH Otto
Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is
Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

>I have two excel spreed sheet, sheet1 and sheet2 both has same information
>as
[quoted text clipped - 12 lines]
>
> Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 15:24 GMT

Otto,

I copy your macro to sheet1, but it has compiler error on

Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))

what is that means?

Thank for the help

Lillian

> Lillian
> This macro will loop through all the cells in Column A of sheet1 and
[quoted text clipped - 36 lines]
> >
> > Lillian

Reply to this Message

Otto Moehrbach - 18 Sep 2007 16:12 GMT

Lillian
I don't know without seeing your code. Perhaps you are a victim of word
wrapping. In your actual code,
Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
should all be on one line. You will get an error if it is on two lines.
HTH Otto

> Otto,
>
[quoted text clipped - 55 lines]
>> >
>> > Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 17:24 GMT

Otto,

I following your instruction, but I realize that sheet2 repeatedly copy
by himself.
on sheet1 has 900 records.
on sheet2 has 1100 records.
I look at sheet2 from column F to I it show 1100 records as well, I thought
only 900 records moved to sheet2 of Column F to I.

any idea?

Lillian

> Lillian
> I don't know without seeing your code. Perhaps you are a victim of word
[quoted text clipped - 63 lines]
> >> >
> >> > Lillian

Reply to this Message

Otto Moehrbach - 18 Sep 2007 17:45 GMT

Lillian
I thought you wanted to copy only those records in Sheet1 that appeared
in Sheet2 and the data copied to be the 4 columns in sheet1 to Column F of
sheet2. Is that not what you wanted? You may be having this problem
because of what sheet is the active sheet. Sheet1 must be the active sheet
when you run the macro. If you wish, copy this line
Sheets("Sheet1").Select
and place it after the line:
Dim Dest As Range
When you do this, it doesn't matter what sheet is the active sheet. HTH
Otto

> Otto,
>
[quoted text clipped - 81 lines]
>> >> >
>> >> > Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 19:50 GMT

Otto,

Sheet1 has 900 records,
Sheet2 has 1100 records,

if sheet1 of ColumnA(IP Address) match sheet2 of ColumnA(IP Address),

then move all the match from sheet1 of ColumnA, ColumnB, ColumnC, ColumnD
to sheet2 of Column F, ColumnG, ColumnH ColumnI

so sheet2 will have
ColumnA, B, C, D, F, G, H, I

Thanks

Lillian

> Lillian
> I thought you wanted to copy only those records in Sheet1 that appeared
[quoted text clipped - 93 lines]
> >> >> >
> >> >> > Lillian

Reply to this Message

Otto Moehrbach - 18 Sep 2007 20:19 GMT

Lillian
Add that little line of code that I gave you in my last posting and it
should work like you want. Let me know. Otto

> Otto,
>
[quoted text clipped - 121 lines]
>> >> >> >
>> >> >> > Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 22:12 GMT

Otto,

Is this correct? I run this nothing show on sheet2 at all.

Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

Lillian

> Lillian
> Add that little line of code that I gave you in my last posting and it
[quoted text clipped - 124 lines]
> >> >> >> >
> >> >> >> > Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 22:16 GMT

Otto,

Is this correct? after I run this nothing happen?

Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

Lillian

> Lillian
> Add that little line of code that I gave you in my last posting and it
[quoted text clipped - 124 lines]
> >> >> >> >
> >> >> >> > Lillian

Reply to this Message

Otto Moehrbach - 19 Sep 2007 15:17 GMT

Lillian
The code works for me. We must have a disconnect between us. Maybe I
don't understand what you have. If you wish, send me, direct via email,
your file or a sample of it. Fake the data if you wish. I need only the
layout, sheet names, etc. My email address is ottokmnop@comcast.net.
Remove the "nop" from this address. HTH Otto

> Otto,
>
[quoted text clipped - 160 lines]
>> >> >> >> >
>> >> >> >> > Lillian

Reply to this Message

Lillian Lian - 18 Sep 2007 22:14 GMT

Otto,

Is this correct? after I run this nothing showing on sheet2 of ColumnF,G,H,I

Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

Thanks for all the help

Lillian

> Lillian
> I thought you wanted to copy only those records in Sheet1 that appeared
[quoted text clipped - 93 lines]
> >> >> >
> >> >> > Lillian

Reply to this Message