Copy Specific Rows of Data Based On two Values

**timbo1957** · 09-13-2012, 05:04 AM

Hi,

I have a worksheet called Download with 55000 rows of data in it.

In column H I have a 13 digit number.

In column I have a Customer Name.

I need to do two things.

On the Dowload Sheet where the Customer Name In Column I is Customer 1 for example, I want to copy all those rows to a sheet called De Dupe.

When the data is on the De Dupe Sheet I need to remove any Duplicated numbers in Column H so that I am left with a clean list for Customer 1 of unique numbers in column H.

Could someone provide a macro that could do this please?

Many thanks

**patel45** · 09-13-2012, 05:46 AM

help us attaching a samplefile (not 55.000 rows)

**HaHoBe** · 09-13-2012, 10:55 AM

Hi, timbo1957,

if it´s just customer you coud make good use of the Autofilter in Column I in Sheet "Download" and only copy the visible cells to Sheet "De Dupe". On the latter you could apply the Advanced Filter to show Uniques.

Or use the Advanced Filter with Headings, a criteria range and output range on Sheet De Dupe and copy only those records.

If you go by VBA I would apply either of the two methods as basic. Depending on the number of records you may as well use a loop for transporting data and checking the 13 digit number from the start of the area to the actual row (if WorksheetFunction.CountIf(Range/"I":I" & lngCounter, "Range("I" & lngCounter).Value) = 1 copy, else skip).

Ciao,
Holger

**timbo1957** · 09-13-2012, 03:00 PM

Please find a cut down copy attached the Download is usually 55,000 rows long.

I have tried Advanced Filter but I can't seem to get it to work.

Perhaps someone could give some step by step instructions of a macro won't do it.

**HaHoBe** · 09-14-2012, 12:03 AM

Hi, timbo1957,

have a look at the workbook. I added a new sheet (Criteria) as well as some code (the range for the criteria is limited to 1 row/creditor in these codes) and added Customer to the headings:

Sub CopyUnique()
  Dim lngLast As Long
  With Sheets("Download")
    lngLast = .Cells(.Rows.Count, "I").End(xlUp).Row
  End With
  Sheets("Download").Range("A3:I" & lngLast).AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("A1:I2"), _
      CopyToRange:=Range("A12:I12"), _
      Unique:=True
End Sub
Sub CopyAll()
  Dim lngLast As Long
  With Sheets("Download")
    lngLast = .Cells(.Rows.Count, "I").End(xlUp).Row
  End With
  Sheets("Download").Range("A3:I" & lngLast).AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("A1:I2"), _
      CopyToRange:=Range("A12:I12"), _
      Unique:=False
End Sub

To me Customer1 and Customer 1 are not identical due to the bank.

Ciao,
Holger