Copy unique records to new sheet

[VegasNath]

Hi,
I am trying to write a macro without success. I would like to:

Filter "Master" sheet, unique records only, also filtering column C to exclude "BOBXI", to a new worksheet after "Master" named ddmmyy (using date from H2 <27/10/10>) as special values.

Hepl please.

[HansV]

You don't provide much concrete information, so the following is mostly air code.

I'll assume that your table starts in cell A1 and forms a contiguous table.
We're going to use Advanced Filter, so I'll also assume that row 1 contains column headings (field names).
Copy the column header of column C to another cell, say K1.
Enter the condition <>BOBXI in the cell below (K2 in this example).

Macro:

Code: Select all

Sub CopyMaster()
  Dim wshSrc As Worksheet
  Dim wshTrg As Worksheet
  Set wshSrc = Worksheets("Master")
  Set wshTrg = Worksheets.Add(After:=wshSrc)
  wshTrg.Name = Format(wshSrc.Range("H2"), "ddmmyy")
  wshSrc.Range(wshSrc.Range("A1"), wshSrc.Range("A1").End(xlToRight)).Copy _
    Destination:=wshTrg.Range("A1")
  wshSrc.Range("A1").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=wshTrg.Range(wshTrg.Range("A1"), wshTrg.Range("A1").End(xlToRight)), _
    CriteriaRange:=wshSrc.Range("K1:K2"), _
    Unique:=True
End Sub

[VegasNath]

Apologies for the brief information. You read between the lines so well, perfect, Thankyou very much.

[PJ_in_FL]

... so the following is mostly air code...
We're going to use Advanced Filter...

Hans,

Your "air code" is better than most people's real code!

Thanks for the advanced filter example. It helped me get past a 1004 error I was having, apparently from using a dynamic range for "DATABASE". Copying your use of .CurrentRegion made all the difference!

Now if I can just determine how to code a criteria that will exclude results if they contain a substring ...

I've tried using the =ISNA(MATCH ... technique with this criteria range (Other is not in the headers of DATABASE):

Code: Select all

Description	Other
*aluminum*	=ISNA(MATCH(Data!B2,Results!$C$3:$C$4,0))

with "can" and "plate" in C3 and C4. I still get results with "aluminum" and "can" in the results. If I move the =ISNA to another row, then I get all lines in DATABASE returned. Any suggestions???

[HansV]

Assuming that the "database" in the Data sheet has column headers (field names) in row 1, the formula condition should be

=ISNA(MATCH(Data!B1,Results!$C$3:$C$4,0))

[PJ_in_FL]

Assuming that the "database" in the Data sheet has column headers (field names) in row 1, the formula condition should be

=ISNA(MATCH(Data!B1,Results!$C$3:$C$4,0))

Hans,

Thank you for taking time to review and reply to my question.

I'm afraid I haven't had any success implementing your suggestion. I've attached a sample database file that may show my problem better than my descriptions.

Test_Database.xlsb

I suppose an alternative would be to implement Autofilter within the results to exclude the unwanted records. I haven't tried that yet, but will probably use that as a fall-back position if I can't get the exclusions to work.

I greatly appreciate any help or direction you can offer!

[HansV]

You also include a row with "aluminum" as condition. In advanced filter, "alumimum" is the same as "*aluminum*", so this condition will add ALL rows where column B contains "aluminum" but not necessarily as the entire cell contents. To select only cells with "aluminum" as the entire cell contents, use

'=aluminum

And now that I see how you're using the formula, it becomes clear that you cannot use MATCH. MATCH looks at entire cells, not at part, and you have to look the other way round: does none of the keywords occur in the cells in column B? This can be done with SEARCH and an array formula or SUMPRODUCT. For example

=SUMPRODUCT(--ISNUMBER(SEARCH($C$3:$C$4,Data!B2)))=0

See the attached version (I removed the dubious hyperlinks)

Test_Database.xlsb

[PJ_in_FL]

Hans,

You are amazing! I would have never worked out that formula.

Thank you very much for being so generous with your time and expertise!