Uniqe list

[Guest]

I have a large problem that I cannot find a simple formula for. Been looking
and trying now for over a year.

As follows: A1:A100 is filled with data that is often repeated. There may
also be some blanks. In this data there may be anywhere from 2 to 100 unique
pieces of data that I would like to extract and put in column B1 thru
B....(however many unique pieces of data have been extracted).

Example:
A B
1 George George
2 Sam Mike
3 Mike Sam
4 Sam Zachary
5 George
6 Mike
7 Zachary
8 Sam
9 George
10 Zachary

It seems so simple and I cant believe Microsoft does not have a formula or
function for this. Keep in mind that this is an example and the real
worksheets have thousands of entries with 100s of unique pieces of data. This
appears to be a fundamental stopping point for me in creating spreadsheets
that automatically create reports for me without bogging down the computer.
I have gotten around it by using Rank, match, and index but it really bogs
me down and if it doesnt go into "not responding" then it could take up to 10
minutes to open. (Pent 4.., etc. state of the art up to date computers)

Would very very much appreciate a simple formula that can achieve this.
Thank you

 

[JE McGimpsey]

Check out Advanced Filter in Help, including the Unique Values only
option.

For more, see

http://contextures.com/xladvfilter01.html

 

[Guest]

A query would probably address your problem, but I am not sure how to do this
in Excel. An Access database would bot be that hard to setup if your of a
mind to go that route. You have a great deal of flexibility to screen data in
that environment and the performance is normally quite good unless your
working with a huge number of records or executing an extremely complex query
with a lot of conditions and calculations occuring.

Just a thought. I belive you can execute queries in Excel through VBA, but I
have not tried that yet.

One more thing, you can use Access to collect the data and then export to
Excel to do things like graphing.

 

[Frank Kabel]

Hi
use 'Data - Filter - Advanced Filter'< and check 'Unique entries'. or
use the following addin:
http://www.rondebruin.nl/easyfilter.htm

 

[Guest]

This macro will copy your range to another sheet, sort it,
then delete all the extra entires leaving you with a
unique list. it is case sensative.

Sub Macro1()
Sheets("Sheet1").Select
Range("A1:A100").Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Dim tc As Range
Dim ntc As Range
Set tc = Range("a1")
Do While Not IsEmpty(tc)
Set ntc = tc.Offset(1, 0)
If tc.Value = ntc.Value Then
tc.EntireRow.Delete
Set tc = ntc
Else
Set tc = ntc
End If
Loop
End Sub
make changes to the sheet names and range references.
good luck.

 

[Guest]

A query wouldnt work because automatically filtering this info is only one
step of many that needs to be automatic. Simply put, We are dumping loads of
data into a storage doc, then want to create automatic reports.
I know...........sounds like access, but I find access very limited in the
look of the final report. Also, I am looking to have 100% of the report to
be automatic. I do it all the time but with a price in computer performance.
This new formula, (if I fiind it) may get me around this problem.

 

[Guest]

Am looking for the unique list to be automatic with no action on the users
part. This is only one step of many on this type of doc.

 

[Guest]

Hi Frank
Still not the answer.
Please see my answers to the other posts. Thanks to all for their input and
possible continuing input until I get it solved.
Will let alll know if I find it.

 

[Guest]

Hi,
There has got to be an easier answer. If you look at it this is a very
simple problem. I see people asking here and on newsgroups this type of
question all the time and all the answers are like this one. You need a phd
to deal with that answer. (or maybe it is just me )

Boiled down the problem is this: from a column of 50, non sorted, entries
only 10 are unique, the rest are repetitions. Return in another column the 10
unique entries.

Is it me or does that not sound like an impressivley simple problem to solve?

Excel needs a formula for just this problem. Just like vlookup or sumif,
ETC.... Something like =NOBLANKSNOREPEATS(A1:A50) as an array formula.

Do my answers sound frustrated? Trying not to be. Been working on this along
time. Thanks to everyone for continuing to try to help.

This macro will copy your range to another sheet, sort it,
then delete all the extra entires leaving you with a
unique list. it is case sensative.

Sub Macro1()
Sheets("Sheet1").Select
Range("A1:A100").Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Dim tc As Range
Dim ntc As Range
Set tc = Range("a1")
Do While Not IsEmpty(tc)
Set ntc = tc.Offset(1, 0)
If tc.Value = ntc.Value Then
tc.EntireRow.Delete
Set tc = ntc
Else
Set tc = ntc
End If
Loop
End Sub
make changes to the sheet names and range references.
good luck.

 

[Alan Beban]

Hi Frank
Still not the answer.
Please see my answers to the other posts. Thanks to all for their input and
possible continuing input until I get it solved.
Will let alll know if I find it.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might use

MyArray = ArrayUniques("Range("A:A"))
This will be case sensitive, will include one blank if the range
contains one or more blanks, and will be a 1-based vertical array.

If you want to omit the blank, use
MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True)

And if you also don't want it case sensitive, use
MyArray = ArrayUniques("Range("A:A"),OmitBlanks:=True, MatchCase:=False)

There is also a Base_Orient parameter that can be set to "1horiz",
"0horiz" or "0vert" as desired.

Any of the above can be followed by

Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray

Alan Beban

 

[Frank Kabel]

Hi
have you tried using a pivot table for this?

 

[Guest]

No. I am not familiar with that. I will look into it to see if that is what
we are looking for.
Thank you.

 

[Guest]

Thankyou Alan.
I will try that and let everyone know how it works.

 

[Frank Kabel]

Hi
some starting points:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2004/11/26/creating-a-simple-pivot-t
able/trackback/

 

[Guest]

Thank you,
Will look into those sites.
I appreciate your help.

 

[Guest]

Alan,
Using the same example I provided in the initial question, I attempted to
use your simple formula in many different ways. Basically I inputed the array
formula in B1 like this:{=ARRAYUNIQUES(A1:A10($B$1:B1))} and, as I am sure
you know, came up with nothing no matter how I toyed with it.
Can you get a little more specific with the formula itself? FYI; I realize
you said it is case sensitive but when you hit CTRL+SHFT+ENTR it
automatically caps it. Oh, and, your site you sent me to is way over my
head. Sorry.

 

[Alan Beban]

Alan,. . .your site you sent me to is way over my
head. Sorry.

Ah, fear of the unknown. FWIW, Pivot Tables are way over my head

Before giving up, try the following:

Go back to the site.
Click on the Array Functions link in the lower left corner of the page
(this will download the file)
Save the file as a Microsoft Excel Add-In
In your working file go to the VBE (Visual Basic Editor)
Click on Tools|References, check the item named Array Functions, then OK

You should now be able to get results from coding in your working file
as my response indicated; e.g.,
MyArray = ArrayUniques(Range("A:A"))
Range("B1:B" & UBound(MyArray) - LBound(MyArray) + 1).Value = MyArray

Good luck,
Alan Beban

 

[Alan Beban]

Or, to use it as a formula, after checking the item named Array
Functions, then on the worksheet array enter into B1:B100

=ArrayUniques(A:A)

Alan Beban

 

[Guest]

Hi Jason:

Boiled down the problem is this: from a column of 50, non sorted, entries
only 10 are unique, the rest are repetitions. Return in another column the 10
unique entries.

Is it me or does that not sound like an iimpressively simple problem to solve?

obviously not

John Walkenbach the author of the Bible, no not
that Bible, the Excel Bible has written a procedure
and made it available for download on his site.

http://www.j-walk.com/ss

search for "nodupes" without the quotes

Good Luck
TK

 

[JulieD]

Hi

you might like to send microsoft an email outlining your ideas for this
function
(e-mail address removed)

in the subject line type EXCEL

Cheers

JulieD

Hi,
There has got to be an easier answer. If you look at it this is a very
simple problem. I see people asking here and on newsgroups this type of
question all the time and all the answers are like this one. You need a
phd
to deal with that answer. (or maybe it is just me )

Boiled down the problem is this: from a column of 50, non sorted, entries
only 10 are unique, the rest are repetitions. Return in another column the
10
unique entries.

Is it me or does that not sound like an impressivley simple problem to
solve?

Excel needs a formula for just this problem. Just like vlookup or sumif,
ETC.... Something like =NOBLANKSNOREPEATS(A1:A50) as an array formula.

Do my answers sound frustrated? Trying not to be. Been working on this
along
time. Thanks to everyone for continuing to try to help.