Excel 97 - unique formula

[scrappe7]

I have an excel worksheet with about 10,000 rows of data and 10 or so columns. i would like to grab a list of unique vendor names from column C, many of the names reappear in each row. is there a function that can do this for me. i just want a list of unique values in a row. i've tried using access for this but my query seems to freeze everytime. any ideas.

 

[xlhelp]

Data, Filter, Advanced Filter, check the "Unique Records only" checkbox.

No idea if Excel has a limit to how many unique records it can find.

HTH

Indu

 

[SkipVought]

Hi, scrappe7,

Another strategy might be to use the PivotTable Wizard to gen a PivotTable with the Vendor names (and occurrences).

hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[DaleWatson123321]

If you would prefer to create a SEPARATE list (separate sheet), this can be done.

However, because of a couple of BUGS (one of which tells you that you CANNOT extract to a separate sheet), it might be best if you would email me your file, with just the headings, and a couple of rows of data (fictitious data if necessary).

I'll insert the necessary VBA code and get it back to you right away.

If you like, I can also email other example files that demonstrate Excel's powerful "database functionality".

Dealing with the actual file will make it easier for BOTH of us.

Hope this can work.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[scrappe7]

Thanks everyone for your help. Dale you're right extracting to another sheet won't work, all i did was create a 2nd copy of my file, performed the filter, and then appended that to the original file. thanks a lot for your assistance though. It's great to know 4 ways to perform a task, it really helps in problem solving for the future, thanks again.

 

[DaleWatson123321]

Hi again, scrappe7,

My apologies. In pointing out Excel's BUGS (that you confirmed DO exist), I neglected to point out that these BUGS are "SPECIAL BUGS" - in that they provide "FALSE" ERROR MESSAGES.

Specifically, when one uses the "manual" method of extracting data to a SEPARATE sheet - using Data - Filter - Advanced Filter, you get the ERROR message.

HOWEVER, if you used VBA, then you do NOT. Here's an example of code that works without problem.

Sub Extract_Data()
Range("Data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("out&quot, _
Unique:=True
End Sub

This routine requires that you assign 3 range names:

1) "Data" - to your database, with the top row including your field names.

2) "crit" - to your criteria. Criteria consists of a field name, and below it, either a formula or a label. If using a formula, Excel requires that you do NOT use the field name, but you must STILL reference the two cells in your criteria name (in this case "crit&quot. You naturally can have different criteria, named differently, for different purposes.

Two cells is the "smallest" size of criteria. You can include as many fields as is necessary in order to "isolate" the data you require.

By including extra fields, you are creating an "AND" condition. You can also include an "OR" condition by including extra ROWS in your criteria.

3) "out" - This refers to the range where you want the data extracted. This name should be assigned to the one row that contains the field names you want to include in your extracted data. This range can be on ANY sheet.

You do NOT have to include ALL the fields in your extraction, and you can change the order of the field names if you prefer to re-arrange them.

Be aware that by using a "1-row" extraction range, Excel will automatically delete ALL data below this range.

Because this area of Excel is NOT very well explained either by Microsoft or by third parties, and because it REQUIRES some explanation and guidance, I thought it would be preferable for you to send me your file. However, if you can "run with the above information", then "all the power to you".

But please start by appreciating that this "extraction" to a SEPARATE sheet is INDEED possible.

Another BUG says you cannot place your criteria on a SEPARATE sheet. Again, this is FALSE - i.e. it IS possible.

I hope the above helps.

If you run into difficulty, please feel free to email me.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca