Creating Unique List based on specific criteria

**Dan79** · 10-09-2017, 09:11 AM

Hi All, first time poster long time user of excel. I am building a large spreadhseet that manipulates data and i'm struggling to extract the relevant data. can anyone offer help and insight into how it works.

In this example I have a jumbled list of Animals with numbers

Column A;

BIRD 10
BIRD 1
DOG 6
DOG 5
DOG 4
DOG 3
DOG 2
CAT 2
CAT 1
BIRD 9
BIRD 8
DOG 10

The idea is to create a list in column b that only has the word "dog" in it

DOG 6
DOG 5
DOG 4
DOG 3
DOG 2
DOG 10

I know it will be an array of sorts and I have tried to understand it but I can not see or understand google results to make a list based on partial cell criteria.

Any help would be great

Thanks
Dan

**63falcondude** · 10-09-2017, 09:18 AM

In B1, try this:

=IFERROR(INDEX($A$1:$A$29,SMALL(IF(LEFT($A$1:$A$29,3)="DOG",ROW($A$1:$A$29)-(ROW($A$1)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

**ranman256** · 10-09-2017, 09:37 AM

this may be a good time to learn about COLLECTIONS and classes.

A collection is an advanced array because you can index it. Instead of searching thru every item to find yours,
just ask for it directly. colZoo("DOG").NAME

Shown is a class cANIMAL, that has a NAME, and QTY.
the code picks up an animal, its species name and counts it
it adds the Class to the collection. If already there, ignore the error, then update the Qty of the count.

Please Login or Register  to view this content.

paste the following code into Notepad and save the file....say: clsAnimal.txt
then in VBE where your modules are,
menu: FILE, IMPORT FILE, clsAnimal.txt
this new class will show in the Project thats used above.

Please Login or Register  to view this content.

Creating Unique List based on specific criteria

LinkBack

Thread Tools

Rate This Thread

Display

Creating Unique List based on specific criteria

Re: Creating Unique List based on specific criteria

Re: Creating Unique List based on specific criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Creating a list of unique values from one column based on criteria from another column

[SOLVED] create a unique list based on a specific phrase

[SOLVED] Create a list of unique entries that did not meet a specific criteria

[SOLVED] Create list based on criteria and is unique

[SOLVED] Create unique list based on specific criteria

counting unique values from list with specific criteria

Reassign unique ids based on specific criteria

Bookmarks

Bookmarks

Posting Permissions