Macro Help - Delete Duplicated Data

[mccrimmon]

Hi

I was wondering if anyone could help with the following spreadsheet I am trying to produce.

Basically, everyday I run a report showing volumes of trading (varies every day, some days only a few trades, others there are hundreds)

Each trade has a unique transaction number.

Everyday we report the trades that have been confirmed within a four day period. However, if some trades have been reported in the previous days report, we do not want to duplicate it, therefor have to manually delete it.

I was wondering if there was a macro that I could create so that I can delete any data from the sheet named "NEW" that already appears on sheets "Day1" "Day2" "Day3" "Day4"

Then after the first macro is run i can just paste the data from sheet "NEW" into "Day 4" the data from Day 4 into sheet "Day 3" and so on etc.

Any help would be greatly appreciated and I hope ive made some sense of what im trying to achieve!

Cheers

Brian

[Norman Jones]

Hi McCrimmon,

> I was wondering if there was a macro that I could create so that I can
> delete any data from the sheet named "NEW" that already appears on
> sheets "Day1" "Day2" "Day3" "Day4"

See Chip Pearson's Tagging Duplicates page at:

http://www.cpearson.com/excel/duplic...gingDuplicates


See particularly, the section entitled: 'Extracting Values Common To Two
Lists'


---
Regards,
Norman



"mccrimmon" <[email protected]> wrote
in message news:[email protected]...
>
> Hi
>
> I was wondering if anyone could help with the following spreadsheet I
> am trying to produce.
>
> Basically, everyday I run a report showing volumes of trading (varies
> every day, some days only a few trades, others there are hundreds)
>
> Each trade has a unique transaction number.
>
> Everyday we report the trades that have been confirmed within a four
> day period. However, if some trades have been reported in the previous
> days report, we do not want to duplicate it, therefor have to manually
> delete it.
>
> I was wondering if there was a macro that I could create so that I can
> delete any data from the sheet named "NEW" that already appears on
> sheets "Day1" "Day2" "Day3" "Day4"
>
> Then after the first macro is run i can just paste the data from sheet
> "NEW" into "Day 4" the data from Day 4 into sheet "Day 3" and so on
> etc.
>
> Any help would be greatly appreciated and I hope ive made some sense of
> what im trying to achieve!
>
> Cheers
>
> Brian
>
>
> +-------------------------------------------------------------------+
> |Filename: NewTest.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3661 |
> +-------------------------------------------------------------------+
>
> --
> mccrimmon
>
>
> ------------------------------------------------------------------------
> mccrimmon's Profile:
> http://www.excelforum.com/member.php...fo&userid=6338
> View this thread: http://www.excelforum.com/showthread...hreadid=391474
>

[mccrimmon]

The problem is that Its only one column that is unique with its own reference so a formula like that would work, however, i need to keep all the other data in the spreadsheet aswell, just deleting any rows that have already been previously reported.

Could anyone have a bash at this and see what they come up with?

Cheers

McCrimmon

[mccrimmon]

Anyone please?

[mccrimmon]

pretty please

[Norman Jones]

Hi McCrimon,

Assume that the unique transaction numbers start in A2 on each sheet.

The following deletes all rows on New whose transaction number is found in
column A on any of the Day1, Day2, Day3 or Day4 sheets.

The Day1 sheet is then renamed "ArchiveCopy" with an appended date.

The following name changes are effecte:

Day4===> Day3
Day3===> Day2
Day2===>Day1

Finally, the New sheet is renamed Day4.

Test this on a *copy* of your workbook.

'===============================>>
Sub TestIt()

Dim Arr As Variant
Dim sh As Worksheet, sh2 As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim delRng As Range
Dim rCell As Range
Dim Lrow As Long
Dim lastRow As Long
Dim i As Long

Application.ScreenUpdating = False

Arr = Array("Day1", "Day2", "Day3", "Day4")

Set sh = ThisWorkbook.Sheets("New")

Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng1 = sh.Range("A2").Resize(Lrow - 1)

For Each rCell In Rng1.Cells
For i = LBound(Arr) To UBound(Arr)
Set sh2 = Sheets(Arr(i))
lastRow = sh2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = sh2.Range("A1").Resize(lastRow)
If Not IsError _
(Application.Match(rCell.Value, Rng2, 0)) Then
If Not delRng Is Nothing Then
Set delRng = Union(rCell, delRng)
Else
Set delRng = rCell
End If
Exit For
End If
Next i
Next rCell

If Not delRng Is Nothing Then delRng.EntireRow.Delete

Sheets(Arr(0)).Name = "ArchiveCopy" & _
Format(Date, "yyyy-mm-dd")

For i = LBound(Arr) + 1 To UBound(Arr)
Sheets(Arr(i)).Name = Arr(i - 1)
Next i

Sheets("New").Name = Arr(UBound(Arr))

Application.ScreenUpdating = False

End Sub
'===============================>>

The bove code should be pated into a normal module in the worbook holding
the data sheets.


---
Regards,
Norman



"mccrimmon" <[email protected]> wrote
in message news:[email protected]...
>
> pretty please
>
>
> --
> mccrimmon
>
>
> ------------------------------------------------------------------------
> mccrimmon's Profile:
> http://www.excelforum.com/member.php...fo&userid=6338
> View this thread: http://www.excelforum.com/showthread...hreadid=391474
>

[mccrimmon]

with doing all of the above i seem to be getting a runtime error "9": subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?

[Dave Peterson]

Norman used this line:
Arr = Array("Day1", "Day2", "Day3", "Day4")

These are the worksheet names that his code expects to find.

If you don't have a worksheet named Day1, Day2, ...

Then you'll get that error.

So you can either rename sheets or adjust that line:
Arr = Array("Day1", "Day2", "Day3", "Day4")
to match your worksheet names.

mccrimmon wrote:
>
> with doing all of the above i seem to be getting a runtime error "9":
> subscript out of range
>
> The reference it relates to seems to be:
>
> Set sh2 = Sheets(Arr(i))
>
> Any suggestions?
>
> --
> mccrimmon
>
> ------------------------------------------------------------------------
> mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
> View this thread: http://www.excelforum.com/showthread...hreadid=391474

--

Dave Peterson

[Norman Jones]

Hi McCrimmon,

The error indicates that one of the sheets whose names is included in:

Arr = Array("Day1", "Day2", "Day3", "Day4")

has not been found.

Check the names and, if necessary, look for initial, traling or intermrdiate
spaces.

If you check the value of i when the error occurs, it will point you to the
problematic sheet name. Bear in mind that the array is 0-based, so if the
error occurs when (say) i = 1, you should check for the presence of a sheet
named "Day2" (i.e, the 2nd array element). To check the value of i, set a
watch on it in the VBE.

I have a working test book if you would like it.

---
Regards,
Norman



"mccrimmon" <[email protected]> wrote
in message news:[email protected]...
>
> with doing all of the above i seem to be getting a runtime error "9":
> subscript out of range
>
> The reference it relates to seems to be:
>
> Set sh2 = Sheets(Arr(i))
>
> Any suggestions?
>
>
> --
> mccrimmon
>
>
> ------------------------------------------------------------------------
> mccrimmon's Profile:
> http://www.excelforum.com/member.php...fo&userid=6338
> View this thread: http://www.excelforum.com/showthread...hreadid=391474
>

[Norman Jones]

Hi McCrimmon,

Dave has already responded to your problem but, unless my selective
blindness is even more pronounced than normal, it was not there when *I*
replied.

Sorry Dave!


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi McCrimmon,
>
> The error indicates that one of the sheets whose names is included in:
>
> Arr = Array("Day1", "Day2", "Day3", "Day4")
>
> has not been found.
>
> Check the names and, if necessary, look for initial, traling or
> intermrdiate spaces.
>
> If you check the value of i when the error occurs, it will point you to
> the problematic sheet name. Bear in mind that the array is 0-based, so if
> the error occurs when (say) i = 1, you should check for the presence of a
> sheet named "Day2" (i.e, the 2nd array element). To check the value of i,
> set a watch on it in the VBE.
>
> I have a working test book if you would like it.
>
> ---
> Regards,
> Norman
>
>
>
> "mccrimmon" <[email protected]> wrote
> in message news:[email protected]...
>>
>> with doing all of the above i seem to be getting a runtime error "9":
>> subscript out of range
>>
>> The reference it relates to seems to be:
>>
>> Set sh2 = Sheets(Arr(i))
>>
>> Any suggestions?
>>
>>
>> --
>> mccrimmon
>>
>>
>> ------------------------------------------------------------------------
>> mccrimmon's Profile:
>> http://www.excelforum.com/member.php...fo&userid=6338
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391474
>>
>
>

[Dave Peterson]

No problem. There's always a time lag between loading the messages, reading,
and responding.

And I think it helps--sometimes it gives alternative suggestions to solve the
same challenge.

Norman Jones wrote:
>
> Hi McCrimmon,
>
> Dave has already responded to your problem but, unless my selective
> blindness is even more pronounced than normal, it was not there when *I*
> replied.
>
> Sorry Dave!
>
> ---
> Regards,
> Norman
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
> > Hi McCrimmon,
> >
> > The error indicates that one of the sheets whose names is included in:
> >
> > Arr = Array("Day1", "Day2", "Day3", "Day4")
> >
> > has not been found.
> >
> > Check the names and, if necessary, look for initial, traling or
> > intermrdiate spaces.
> >
> > If you check the value of i when the error occurs, it will point you to
> > the problematic sheet name. Bear in mind that the array is 0-based, so if
> > the error occurs when (say) i = 1, you should check for the presence of a
> > sheet named "Day2" (i.e, the 2nd array element). To check the value of i,
> > set a watch on it in the VBE.
> >
> > I have a working test book if you would like it.
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "mccrimmon" <[email protected]> wrote
> > in message news:[email protected]...
> >>
> >> with doing all of the above i seem to be getting a runtime error "9":
> >> subscript out of range
> >>
> >> The reference it relates to seems to be:
> >>
> >> Set sh2 = Sheets(Arr(i))
> >>
> >> Any suggestions?
> >>
> >>
> >> --
> >> mccrimmon
> >>
> >>
> >> ------------------------------------------------------------------------
> >> mccrimmon's Profile:
> >> http://www.excelforum.com/member.php...fo&userid=6338
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=391474
> >>
> >
> >

--

Dave Peterson