Function to Filter Unique Records

G

Guest

I would like Excel to AUTOMATICALLY give me in column B unique records from
column A

A B
1 Andrew Andrew
2 Andrew Louis
3 Louis
4 Andrew

Advanced filter doesn't refresh automatically.
I remember a user once outlined a formula on this forum, but I can't find it
now.
 
G

Guest

Try something like this:

For a list entered into A2:A20, with A1 as the column heading

Then
B1: (Heading for the column....eg MyList)
B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0))

Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter)
B3:
=IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<>"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20<>"")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"")

Copy B3 into B4 and down through as many cells as you need to cover all
possible unique items

With that formula, each time you enter new text that is not already listed,
it will display in the list in B1:??.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP
 
G

Guest

Works great. I'm analyzing your formulas.

Ron Coderre said:
Try something like this:

For a list entered into A2:A20, with A1 as the column heading

Then
B1: (Heading for the column....eg MyList)
B2: =INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0))

Array Formula (Committed with Ctrl+Shift+Enter, instead of just Enter)
B3:
=IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<>"")),INDEX(A$2:A$20,MATCH(0,(A$2:A$20<>"")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"")

Copy B3 into B4 and down through as many cells as you need to cover all
possible unique items

With that formula, each time you enter new text that is not already listed,
it will display in the list in B1:??.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP
 
G

Guest

Thanks for the feedback.......
I'm glad that worked for you
(I'm pretty sure that formula has Harlan Grove's fingerprints on it)

***********
Regards,
Ron

XL2003, WinXP
 
G

Guest

Try this:

=IF(ISERR(SMALL(IF(MATCH(names,names,0)=ROW(INDIRECT("1:"&ROWS(names))),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(MATCH(names,names,0)=ROW(INDIRECT("1:"&ROWS(names))),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down
 
H

Harlan Grove

Teethless mama said:
Try this:

=IF(ISERR(SMALL(IF(MATCH(names,names,0)=ROW(INDIRECT("1:"&ROWS(names))),
ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,
SMALL(IF(MATCH(names,names,0)=ROW(INDIRECT("1:"&ROWS(names))),
ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))
....

Or use shorter, more efficient formulas.

B1:
=T(A1:A100)

B2 [array formula]:
=IF(OR(COUNTIF(B$1:B1,A$1:A$100)=0),
INDEX(A$1:A$100,MATCH(0,COUNTIF(B$1:B1,A$1:A$100),0)),"")

Fill B2 down as far as needed.
 
M

MPI Planner

is there a way to qualify this based on criteria? I have a list of 50,000+
records for 1100 different part numbers. some have 300 records per part, some
3. is there a way to use this logic inside an IF statement so that it would
display the unique records for a specific part number within that list?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top