finding duplicate then copying macro..

Ok, first of all, this community has been so great, and I wanted to thank you
all. I have a new thing i need to do, and i was wanting to see if anyone can
help me.

I need a macro that will look at the information on the current sheet, and
compare it to the information on the previous sheet and copy a value.
here is what i need

If any value on the current sheet in column A B and C matches anything on
the previous sheet in column A B and C (The line needs to have all 3 match),
then the macro would copy the value in the G column to the new page.


For example
Sheet 13
A B C G

Cat Dog Cat Not a Moose


then when the macro finds the same ABC on the new sheet, it fills in G for
me.

any help would be great.. Thanks!

Michael

See my reply to in Programming group.

Please donot cross post your query.

Perhaps a formulas approach might also work ?

Assume your source sheet is Sheet1,
cols A to C, with answers in col G,
data from row2 down

Cat Dog Cat Not a Moose
Cat Cat Cat Definitely a Cat
Cat Cat Dog Not a Moose2
etc

Answers: "Not a Moose", "Definitely a Cat", etc are in col G

Assume that Sheet2 will be the inputs sheet,
where the corresponding inputs below will be made
in cols A to C, row2 down:

Cat Dog Cat
Cat Cat Cat
Cat Cat Dog
etc

In Sheet3
------------
Assume col A is where we want the answers to be

Put in the formula bar for A2:

=IF(COUNTBLANK(Sheet2!A2:C2)<>0,"",IF(ISNA(MATCH(Sheet2!A2&Sheet2!B2&Sheet2!
C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:C100,0)),"",INDEX(Sheet1!G2:G100,
MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:
C100,0))))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy A2 down to A100
(can copy down ahead of expected inputs in Sheet2)

Col A will return the corresponding results from col G in Sheet1 for the
inputs made in Sheet2's cols A to C matched against the source data in
Sheet1's cols A to C

If the inputs in cols A to C in Sheet2 are incomplete, or the inputs made do
not match with what's in Sheet1, blanks: "" will be returned

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Michael A" <MichaelA@discussions.microsoft.com> wrote in message
news:6647DDDE-8956-4716-B69A-A448D1816021@microsoft.com...
> Ok, first of all, this community has been so great, and I wanted to thank
you
> all. I have a new thing i need to do, and i was wanting to see if anyone
can
> help me.
>
> I need a macro that will look at the information on the current sheet, and
> compare it to the information on the previous sheet and copy a value.
> here is what i need
>
> If any value on the current sheet in column A B and C matches anything on
> the previous sheet in column A B and C (The line needs to have all 3
match),
> then the macro would copy the value in the G column to the new page.
>
>
> For example
> Sheet 13
> A B C G
>
> Cat Dog Cat Not a Moose
>
>
> then when the macro finds the same ABC on the new sheet, it fills in G for
> me.
>
> any help would be great.. Thanks!
>
>

ok, here is what I have so far.. im sure this isnt right. i get an
application or user defined error.. could someone help me improve this? Or am
off in left field.


Sub check_several_matches()
Dim i As Integer
Dim check As Boolean
check = True
For i = 1 To 3
If Sheets(7).Cells(1, i) <> Sheets(8).Cells(1, i) Then
check = False
Exit For
End If
Next i
If check = True Then Copy Sheets(8).Cells(1, 7)
End Sub


"Michael A" wrote:

> Ok, first of all, this community has been so great, and I wanted to thank you
> all. I have a new thing i need to do, and i was wanting to see if anyone can
> help me.
>
> I need a macro that will look at the information on the current sheet, and
> compare it to the information on the previous sheet and copy a value.
> here is what i need
>
> If any value on the current sheet in column A B and C matches anything on
> the previous sheet in column A B and C (The line needs to have all 3 match),
> then the macro would copy the value in the G column to the new page.
>
>
> For example
> Sheet 13
> A B C G
>
> Cat Dog Cat Not a Moose
>
>
> then when the macro finds the same ABC on the new sheet, it fills in G for
> me.
>
> any help would be great.. Thanks!
>
>

Max, thanks for the reply, I dont think a formula would work in this
instance. There is over 400 different entries on each sheet, and over 70 of
them are duplicates from the sheet before, so the ones that arent duplicates
on the new sheet, would need to have the G column filled out manualy. I have
one sheet for each day of the month that I have to go in and do this for
every day.



"Max" wrote:

> Perhaps a formulas approach might also work ?
>
> Assume your source sheet is Sheet1,
> cols A to C, with answers in col G,
> data from row2 down
>
> Cat Dog Cat Not a Moose
> Cat Cat Cat Definitely a Cat
> Cat Cat Dog Not a Moose2
> etc
>
> Answers: "Not a Moose", "Definitely a Cat", etc are in col G
>
> Assume that Sheet2 will be the inputs sheet,
> where the corresponding inputs below will be made
> in cols A to C, row2 down:
>
> Cat Dog Cat
> Cat Cat Cat
> Cat Cat Dog
> etc
>
> In Sheet3
> ------------
> Assume col A is where we want the answers to be
>
> Put in the formula bar for A2:
>
> =IF(COUNTBLANK(Sheet2!A2:C2)<>0,"",IF(ISNA(MATCH(Sheet2!A2&Sheet2!B2&Sheet2!
> C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:C100,0)),"",INDEX(Sheet1!G2:G100,
> MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:
> C100,0))))
>
> Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
> instead of just pressing ENTER
>
> Copy A2 down to A100
> (can copy down ahead of expected inputs in Sheet2)
>
> Col A will return the corresponding results from col G in Sheet1 for the
> inputs made in Sheet2's cols A to C matched against the source data in
> Sheet1's cols A to C
>
> If the inputs in cols A to C in Sheet2 are incomplete, or the inputs made do
> not match with what's in Sheet1, blanks: "" will be returned
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Michael A" <MichaelA@discussions.microsoft.com> wrote in message
> news:6647DDDE-8956-4716-B69A-A448D1816021@microsoft.com...
> > Ok, first of all, this community has been so great, and I wanted to thank
> you
> > all. I have a new thing i need to do, and i was wanting to see if anyone
> can
> > help me.
> >
> > I need a macro that will look at the information on the current sheet, and
> > compare it to the information on the previous sheet and copy a value.
> > here is what i need
> >
> > If any value on the current sheet in column A B and C matches anything on
> > the previous sheet in column A B and C (The line needs to have all 3
> match),
> > then the macro would copy the value in the G column to the new page.
> >
> >
> > For example
> > Sheet 13
> > A B C G
> >
> > Cat Dog Cat Not a Moose
> >
> >
> > then when the macro finds the same ABC on the new sheet, it fills in G for
> > me.
> >
> > any help would be great.. Thanks!
> >
> >
>
>
>

You're welcome, Michael
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Michael A" <MichaelA@discussions.microsoft.com> wrote in message
news:1CD31AF2-831D-41E0-98F4-6118A1545A0A@microsoft.com...
> Max, thanks for the reply, I dont think a formula would work in this
> instance. There is over 400 different entries on each sheet, and over 70
of
> them are duplicates from the sheet before, so the ones that arent
duplicates
> on the new sheet, would need to have the G column filled out manualy. I
have
> one sheet for each day of the month that I have to go in and do this for
> every day.
>
>
>
> "Max" wrote:
>
> > Perhaps a formulas approach might also work ?
> >
> > Assume your source sheet is Sheet1,
> > cols A to C, with answers in col G,
> > data from row2 down
> >
> > Cat Dog Cat Not a Moose
> > Cat Cat Cat Definitely a Cat
> > Cat Cat Dog Not a Moose2
> > etc
> >
> > Answers: "Not a Moose", "Definitely a Cat", etc are in col G
> >
> > Assume that Sheet2 will be the inputs sheet,
> > where the corresponding inputs below will be made
> > in cols A to C, row2 down:
> >
> > Cat Dog Cat
> > Cat Cat Cat
> > Cat Cat Dog
> > etc
> >
> > In Sheet3
> > ------------
> > Assume col A is where we want the answers to be
> >
> > Put in the formula bar for A2:
> >
> >
=IF(COUNTBLANK(Sheet2!A2:C2)<>0,"",IF(ISNA(MATCH(Sheet2!A2&Sheet2!B2&Sheet2!
> >
C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:C100,0)),"",INDEX(Sheet1!G2:G100,
> >
MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!A2:A100&Sheet1!B2:B100&Sheet1!C2:
> > C100,0))))
> >
> > Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
> > instead of just pressing ENTER
> >
> > Copy A2 down to A100
> > (can copy down ahead of expected inputs in Sheet2)
> >
> > Col A will return the corresponding results from col G in Sheet1 for the
> > inputs made in Sheet2's cols A to C matched against the source data in
> > Sheet1's cols A to C
> >
> > If the inputs in cols A to C in Sheet2 are incomplete, or the inputs
made do
> > not match with what's in Sheet1, blanks: "" will be returned
> >
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "Michael A" <MichaelA@discussions.microsoft.com> wrote in message
> > news:6647DDDE-8956-4716-B69A-A448D1816021@microsoft.com...
> > > Ok, first of all, this community has been so great, and I wanted to
thank
> > you
> > > all. I have a new thing i need to do, and i was wanting to see if
anyone
> > can
> > > help me.
> > >
> > > I need a macro that will look at the information on the current sheet,
and
> > > compare it to the information on the previous sheet and copy a value.
> > > here is what i need
> > >
> > > If any value on the current sheet in column A B and C matches anything
on
> > > the previous sheet in column A B and C (The line needs to have all 3
> > match),
> > > then the macro would copy the value in the G column to the new page.
> > >
> > >
> > > For example
> > > Sheet 13
> > > A B C G
> > >
> > > Cat Dog Cat Not a Moose
> > >
> > >
> > > then when the macro finds the same ABC on the new sheet, it fills in G
for
> > > me.
> > >
> > > any help would be great.. Thanks!
> > >
> > >
> >
> >
> >

Oops, sorry, this is just for the records ..
(forgot to fix the range references for the array formula in A2)

Put instead in the formula bar for A2:

=IF(COUNTBLANK(Sheet2!A2:C2)<>0,"",IF(ISNA(MATCH(Sheet2!A2&Sheet2!B2&Sheet2!
C2,Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100&Sheet1!$C$2:$C$100,0)),"",INDEX(She
et1!$G$2:$G$100,MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!$A$2:$A$100&Sheet
1!$B$2:$B$100&Sheet1!$C$2:$C$100,0))))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Michael

Try this on a back up copy of your data


Sub CompareEntries()
Dim wsN As Worksheet ' new sheet
Dim wsP As Worksheet ' previous sheet
Dim lnRow As Long
Dim lpRow As Long
Dim sTxt(1 To 3) As String

Set wsN = Sheets("sheet2")
Set wsP = Sheets("sheet1")


For lnRow = 1 To wsN.Cells(Rows.Count, "a").End(xlUp).Row Step 1
sTxt(1) = wsN.Cells(lnRow, "a").Value
sTxt(2) = wsN.Cells(lnRow, "b").Value
sTxt(3) = wsN.Cells(lnRow, "c").Value
For lpRow = 1 To wsP.Cells(Rows.Count, "a").End(xlUp).Row Step 1
If wsP.Cells(lpRow, "a").Value = sTxt(1) Then
If wsP.Cells(lpRow, "b").Value = sTxt(2) Then
If wsP.Cells(lpRow, "b").Value = sTxt(3) Then
wsN.Cells(lnRow, "g").Value = wsP.Cells(lpRow, "g").Value
Exit For
End If
End If
End If
Next lpRow
Next lnRow
End Sub