Please Help (Unusual Lookup)

[1plane]

Hi Guys,

Below is a list of invoices from a supplier. What I am trying to do is
to extract or delete the line items that are not duplicates.

Ideally I want a code that will leave me with Row C1, C2 C6 and C7.
Rows C3, C4, C5, C8 C9 and C10 can be deleted.

I shall be grateful if anyoe can suggest how to extract these.

C1 13/03/2009 V 00182675 £97,750.00 TRUE
C2 13/03/2009 V 00261027 £97,750.00 FALSE
C3 11/06/2008 V 00145507 £100,703.38 FALSE
C4 17/08/2009 V 00285504 £101,914.92 FALSE
C5 25/07/2008 V 00222055 £128,492.97 FALSE
C6 30/01/2008 V 00192264 £163,001.17 TRUE
C7 30/01/2008 V 00236244 £163,001.17 FALSE
C8 10/01/2008 V 00194622 £167,822.38 FALSE
C9 30/07/2008 V 00222733 £316,372.39 FALSE
C10 30/07/2008 V 00152296 £386,725.54 FALSE

Regards

1plane

 

[joel]

I'm a little bit confused on what you are considering a duplicate. Yo
description if incosistent with the data you provided.

I can write a macro, if you crrect your error. first I would sort th
data by date (descending order) and any other columns that need to b
sorted to put duplicates in adjacent rows. I would do is to add
formula into the worksheet in an auxilary column that would look fo
duplicates. This is an example of the formula starting in row 2. Th
formula compares row 1 with row 2. then copy the formula down t
worksheet.

=IF(AND(B1=B2,E1=E2),TRUE,FALSE)

the duplicates would be the formulas that returned True. I would the
use autofilter to remove the TRUE rows. All this can be done using
macro. Let me know which columns need to be compared and if there is
header row that need to be skipped

 

[1plane]

I'm a little bit confused on what you are considering a duplicate.  You
description if incosistent with the data you provided.

I can write a macro, if you crrect your error.  first I would sort the
data by date (descending order) and any other columns that need to be
sorted to put duplicates in adjacent rows.  I would do is to add a
formula into the worksheet in an auxilary column that would look for
duplicates. This is an example of the formula starting in row 2. The
formula compares row 1 with row 2.  then copy the formula down to
worksheet.

=IF(AND(B1=B2,E1=E2),TRUE,FALSE)

the duplicates would be the formulas that returned True.  I would then
use autofilter to remove the TRUE rows.  All this can be done using a
macro.  Let me know which columns need to be compared and if there is a
header row that need to be skipped.

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=154326

Microsoft Office Help

Joel,

Thanks for your response.
What I am looking for a way to put TRUE on both C1 & C2 because they
have the same date and amount.
This will be the same for C6 & C7.
Your formula returns true on only one row and what I need to to is
extract only rows with same day and amount.

I hope this helps?

Regards

1plane

 

[OssieMac]

Before automating the process with code, please confirm the following.

When you refer to duplicates, am I correct in assuming that you mean
duplicate amounts of money; not full duplicate records because as full
records there are no duplicates in your example. If so, then you can do this
with the following without code. If it returns the correct result and you
still want the code to automate the process then let me know.

Ensure you backup your data first in case this method does not return the
expected result.

Assuming that the C1, C2 etc is part of your data in column A then the money
column will be column E.

In the first empty column out to the right (say column G) insert the
following formula in row 2 (I am assuming that you have column headers in row
1).

=COUNTIF($E:$E,E2)

Copy the formula down.

Apply AutoFilter to the range of data.

Set the filter for column G (the one with the above formula) to 1.

Select all of the data BELOW the column headers.

Select Edit -> Goto -> Special cells -> Visible Cells Only.

Right click over the selected area.

Select Delete rows. (At popup answer Yes to delete entire rows)

Reset filter on column G to all data.

 

[1plane]

Before automating the process with code, please confirm the following.

When you refer to duplicates, am I correct in assuming that you mean
duplicate amounts of money; not full duplicate records because as full
records there are no duplicates in your example. If so, then you can do this
with the following without code. If it returns the correct result and you
still want the code to automate the process then let me know.

Ensure you backup your data first in case this method does not return the
expected result.

Assuming that the C1, C2 etc is part of your data in column A then the money
column will be column E.

In the first empty column out to the right (say column G) insert the
following formula in row 2 (I am assuming that you have column headers inrow
1).

=COUNTIF($E:$E,E2)

Copy the formula down.

Apply AutoFilter to the range of data.

Set the filter for column G (the one with the above formula) to 1.

Select all of the data BELOW the column headers.

Select Edit -> Goto -> Special cells -> Visible Cells Only.

Right click over the selected area.

Select Delete rows. (At popup answer Yes to delete entire rows)

Reset filter on column G to all data.

OssieMac,

Yes it works, it works.

I can confirm that I am refering to duplicate money (you are right)

Can you pls help with automating the code?

Regards

1plane

 

[1plane]

Before automating the process with code, please confirm the following.

When you refer to duplicates, am I correct in assuming that you mean
duplicate amounts of money; not full duplicate records because as full
records there are no duplicates in your example. If so, then you can do this
with the following without code. If it returns the correct result and you
still want the code to automate the process then let me know.

Ensure you backup your data first in case this method does not return the
expected result.

Assuming that the C1, C2 etc is part of your data in column A then the money
column will be column E.

In the first empty column out to the right (say column G) insert the
following formula in row 2 (I am assuming that you have column headers inrow
1).

=COUNTIF($E:$E,E2)

Copy the formula down.

Apply AutoFilter to the range of data.

Set the filter for column G (the one with the above formula) to 1.

Select all of the data BELOW the column headers.

Select Edit -> Goto -> Special cells -> Visible Cells Only.

Right click over the selected area.

Select Delete rows. (At popup answer Yes to delete entire rows)

Reset filter on column G to all data.

Hi OssieMac,

Is it possible to have a formula that compares the dates as well. i.e.
Row C1 and C2 dates and amounts must be the same.

Regards

1plane

 

[OssieMac]

Now you have stipulated "extract only rows with same day and amount", the
answer I gave will not work. However, before doing any more, what version of
xl are you using?

 

[1plane]

Now you have stipulated "extract only rows with same day and amount", the
answer I gave will not work. However, before doing any more, what versionof
xl are you using?

I am using excel 2000.

Thanks once again.

Regards

1plane

 

[OssieMac]

Back to my previous question. What version of excel are you using? Makes a
difference as to how it can be done.

 

[1plane]

Back to my previous question. What version of excel are you using? Makes a
difference as to how it can be done.

Hi OssieMac,

It is 9.0.3821 SR1.

Regards

1plane

 

[OssieMac]

The following should do what you want. I have actually now added 2 columns of
temporary data. Column G now contains the date and money concatenated into
one field. The CountIf is then applied to that field in the next column H.
All of this is done within the code.

It handles a situation of no records meeting the delete criteria.

As before, backup your data before running the code in case it does not do
as expected.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code. When applied to lines with double quotes, the
double quotes are closed off, an ampersand inserted and the double quotes
opened again.


Sub DeleteAutoFilteredRows()
Dim lngRows As Long
Dim rngDelete As Range

'Edit 'Sheet1" to your sheet name
With Sheets("Sheet1")
lngRows = .Cells(.Rows.Count, "E").End(xlUp).Row
.Range("G1") = "Concat Date and Cost"
.Range("H1") = "Counts"

.Range("G2").Formula = _
"=TEXT(B2,""dd/mm/yyyy"") & " & _
""" "" & TEXT(E2,""0.00"")"

.Range("G2").Copy _
Destination:=.Range("G2:G" & lngRows)

.Range("H2").Formula = _
"=COUNTIF($G:$G,G2)"

.Range("H2").Copy _
Destination:=.Range("H2:H" & lngRows)

.Range("H2:H11").NumberFormat = "#,##0"
.Columns("G:H").Columns.AutoFit

'Turn off autofilter if already on
'and reset to on with all columns of data
.AutoFilterMode = False
.Range("A1:H" & lngRows).AutoFilter

.Range("$A$1:$H$" & lngRows).AutoFilter _
Field:=8, Criteria1:="1"


'Assign filtered data to range variable.
'Offset excludes column headers.
'Resize reduces by one row because offset _
includes an extra blank row at bottom.
With .AutoFilter.Range
On Error Resume Next 'In case no rows visible
Set rngDelete = .Offset(1, 0) _
.Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0 'Reset error trapping ASAP.
If rngDelete Is Nothing Then
MsgBox "No records with count 1." & _
vbCrLf & "Processing terminated."
GoTo SkipDelete
End If
End With

'Remove comment (') from following _
line if you want to view before _
data is deleted during testing
'Exit Sub

'Delete the filtered data
rngDelete.EntireRow.Delete

SkipDelete:
.ShowAllData

'Turn off autofilter
.AutoFilterMode = False

'Clear temporary columns of data
.Columns("G:H").Clear

End With

End Sub

 

[1plane]

The following should do what you want. I have actually now added 2 columns of
temporary data. Column G now contains the date and money concatenated into
one field. The CountIf is then applied to that field in the next column H..
All of this is done within the code.

It handles a situation of no records meeting the delete criteria.

As before, backup your data before running the code in case it does not do
as expected.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code. When applied to lines with double quotes, the
double quotes are closed off, an ampersand inserted and the double quotes
opened again.

Sub DeleteAutoFilteredRows()
Dim lngRows As Long
Dim rngDelete As Range

'Edit 'Sheet1" to your sheet name
With Sheets("Sheet1")
  lngRows = .Cells(.Rows.Count, "E").End(xlUp).Row
  .Range("G1") = "Concat Date and Cost"
  .Range("H1") = "Counts"

  .Range("G2").Formula = _
    "=TEXT(B2,""dd/mm/yyyy"") & " & _
    """ "" & TEXT(E2,""0.00"")"

  .Range("G2").Copy _
    Destination:=.Range("G2:G" & lngRows)

  .Range("H2").Formula = _
    "=COUNTIF($G:$G,G2)"

  .Range("H2").Copy _
    Destination:=.Range("H2:H" & lngRows)

  .Range("H2:H11").NumberFormat = "#,##0"
  .Columns("G:H").Columns.AutoFit

  'Turn off autofilter if already on
  'and reset to on with all columns of data
  .AutoFilterMode = False
  .Range("A1:H" & lngRows).AutoFilter

  .Range("$A$1:$H$" & lngRows).AutoFilter _
    Field:=8, Criteria1:="1"

  'Assign filtered data to range variable.
  'Offset excludes column headers.
  'Resize reduces by one row because offset _
   includes an extra blank row at bottom.
  With .AutoFilter.Range
    On Error Resume Next  'In case no rows visible
    Set rngDelete = .Offset(1, 0) _
      .Resize(.Rows.Count - 1) _
      .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0 'Reset error trapping ASAP.
    If rngDelete Is Nothing Then
      MsgBox "No records with count 1." & _
        vbCrLf & "Processing terminated."
      GoTo SkipDelete
    End If
  End With

  'Remove comment (') from following _
   line if you want to view before _
   data is deleted during testing
  'Exit Sub

  'Delete the filtered data
  rngDelete.EntireRow.Delete

SkipDelete:
  .ShowAllData

  'Turn off autofilter
  .AutoFilterMode = False

  'Clear temporary columns of data
  .Columns("G:H").Clear

End With

End Sub

Dear OssieMac,

Thanks a million for your assistance.

I hope some day I can help others to this degree.

Kind Regards

1plane