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



--
Jason

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

For more, see

http://contextures.com/xladvfilter01.html


In article ,
Jason@Simcon wrote:

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

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.

"JE McGimpsey" wrote:

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

For more, see

http://contextures.com/xladvfilter01.html


In article ,
Jason@Simcon wrote:

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag ...
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



--
Jason

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.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag ...
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



--
Jason

Jason@Simcon wrote:
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

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



--
Jason

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

"Alan Beban" wrote:

Jason@Simcon wrote:
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

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



--
Jason

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" wrote:

Jason@Simcon wrote:
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

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



--
Jason

Jason@Simcon wrote:
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" wrote:


Jason@Simcon wrote:

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

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



--
Jason

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.

"Jason@Simcon" wrote:

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



--
Jason

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.

"Kevin" wrote:

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.

"Jason@Simcon" wrote:

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



--
Jason

Hi
have you tried using a pivot table for this?

--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag ...
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.

"Kevin" wrote:

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.

"Jason@Simcon" wrote:

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



--
Jason

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

"Frank Kabel" wrote:

Hi
have you tried using a pivot table for this?

--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag ...
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.

"Kevin" wrote:

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.

"Jason@Simcon" wrote:

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



--
Jason

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/2...simple-pivot-t
able/trackback/


--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag ...
No. I am not familiar with that. I will look into it to see if that
is what
we are looking for.
Thank you.

"Frank Kabel" wrote:

Hi
have you tried using a pivot table for this?

--
Regards
Frank Kabel
Frankfurt, Germany

"Jason@Simcon" schrieb im
Newsbeitrag
...
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.

"Kevin" wrote:

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.

"Jason@Simcon" wrote:

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



--
Jason

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.
-----Original Message-----
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



--
Jason
.

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.

" wrote:

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.
-----Original Message-----
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



--
Jason
.

Hi Jason:

"Jason@Simcon" wrote:
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

TK wrote:
Hi Jason:

"Jason@Simcon" wrote:

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

The procedure at the linked site, which uses a Collection to load a
ListBox, is about 8 or 9 times slower than using a Dictionary instead
for that purpose, and about 7 times slower than using the ArrayUniques
function, which I referred to earlier in this thread, for that purpose.

Alan Beban

Alan Wrote
The procedure at the linked site, which uses a Collection to load a
ListBox, is about 8 or 9 times slower than using a Dictionary instead
for that purpose, and about 7 times slower than using the ArrayUniques
function, which I referred to earlier in this thread, for that purpose

Alan out of 21 and still counting suggestions you take exception
to mine. I didnt reject your suggestion the O.P. did.

I was not evaluating these procedures I was suggesting
an alternative that he may have a better understanding
of.

What dont you understand about his reply to you?

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

TK

"Alan Beban" wrote:

TK wrote:
Hi Jason:

"Jason@Simcon" wrote:

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

The procedure at the linked site, which uses a Collection to load a
ListBox, is about 8 or 9 times slower than using a Dictionary instead
for that purpose, and about 7 times slower than using the ArrayUniques
function, which I referred to earlier in this thread, for that purpose.

Alan Beban

Hi

you might like to send microsoft an email outlining your ideas for this
function


in the subject line type EXCEL

Cheers

JulieD



"Jason@Simcon" wrote in message
...

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.

" wrote:

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.
-----Original Message-----
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



--
Jason
.

Jason@Simcon wrote:
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.

The following is about as simple as can be provided. Array enter it into
B1:B50 as =NOBLANKSNOREPEATS(A1:A50).
If that is not "impressively simple", then it's you, naively looking for
a magic wand.

By the way, if you've been working on it unsuccessfully for over a year,
and all the answers you see all the time in the newsgroups seem to you
to require a PhD. to understand, why in the world would you conclude
that it's an impressively simple problem???

Function NOBLANKSNOREPEATS(InputArray)

Dim arr, arr2()
Dim i As Long
Dim Elem, Coll As Collection

arr = InputArray

Set Coll = New Collection
On Error Resume Next
For Each Elem In arr
Coll.Add Elem, CStr(Elem)
If Elem = "" Then Coll.Remove (Elem)
Next
On Error GoTo 0
ReDim arr2(1 To Coll.Count, 1 To 1)
i = 1
For Each Elem In Coll
arr2(i, 1) = Elem
i = i + 1
Next

NOBLANKSNOREPEATS = arr2

End Function

Alan Beban

Alan,
Just tried that simple answer. Yes. That is the formula I am looking for.
The only problem is that I cant get it to work. Here is what I did
In B1 I typed =NOBLANKSNOREPEATS(A1:A50) then I hit CTR+SHFT+ENTR. I am sure
by now you already can see I had a problem. It comes up with NAME?
What am I doing wrong with this simple formula?

"Alan Beban" wrote:

Jason@Simcon wrote:
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.

The following is about as simple as can be provided. Array enter it into
B1:B50 as =NOBLANKSNOREPEATS(A1:A50).
If that is not "impressively simple", then it's you, naively looking for
a magic wand.

By the way, if you've been working on it unsuccessfully for over a year,
and all the answers you see all the time in the newsgroups seem to you
to require a PhD. to understand, why in the world would you conclude
that it's an impressively simple problem???

Function NOBLANKSNOREPEATS(InputArray)

Dim arr, arr2()
Dim i As Long
Dim Elem, Coll As Collection

arr = InputArray

Set Coll = New Collection
On Error Resume Next
For Each Elem In arr
Coll.Add Elem, CStr(Elem)
If Elem = "" Then Coll.Remove (Elem)
Next
On Error GoTo 0
ReDim arr2(1 To Coll.Count, 1 To 1)
i = 1
For Each Elem In Coll
arr2(i, 1) = Elem
i = i + 1
Next

NOBLANKSNOREPEATS = arr2

End Function

Alan Beban

Did you first paste that formula into a general module in your workbook?

By the way, although this doesn't account for your #NAME! error, you
need to select not just B1 but B1:B50 (or B1:B[a number large enough to
accommodate all occurrences of unique values]) before hitting CTR+SHFT+ENTR.

Post back if you don't get it to work.

Alan Beban

Jason@Simcon wrote:
Alan,
Just tried that simple answer. Yes. That is the formula I am looking for.
The only problem is that I cant get it to work. Here is what I did
In B1 I typed =NOBLANKSNOREPEATS(A1:A50) then I hit CTR+SHFT+ENTR. I am sure
by now you already can see I had a problem. It comes up with NAME?
What am I doing wrong with this simple formula?


A further "by the way" for any interested followers of this thread.

In the interests of "simplicity" I've worked a bit on the ArrayUniques()
function to make the process independent of my web site. If you paste
the first of the following functions into a general module in your
workbook, click on Tools|References and check Microsoft Scripting
Runtime, the function can be used to produce an array of unique items
from a range or array with more versatility than the simpler
NOBLANKSNOREPEATS() function referred to above. The ArrayUniquesLtd()
function will work if the number of unique items is less than 5462 or if
you are using xl2002 or later. Otherwise, again without resort to my web
site, if you paste instead the 2nd and 3rd functions below (again
checking the reference to Microsoft Scripting Runtime), the
ArrayUniques() function will extract the unique values.

Watch for word wrap, particularly in the ArrayTranspose() function:

Function ArrayUniquesLtd(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True)
'THIS PROCEDURE REQUIRES A PROJECT REFERENCE
'TO "MICROSCOPIC SCRIPTING RUNTIME".
'The function returns an array of unique values
'from an array or range, by default omitting
'blanks and empty strings; to include an empty
'string (or a zero for a blank), use False as
'the 4th parameter. By default the function
'returns a 1-based vertical array; for other
'results enter "0horiz", "1horiz" or "0vert" as
'the 3rd parameter. By default, the function is
'case-sensitive; i.e., e.g., "red" and "RED" are
'treated as two separate unique values; to
'avoid case-sensitivity, enter False as the
'2nd parameter.

'Declare the variables
Dim arr, arr2
Dim i As Long, p As Object, q As String
Dim Elem, x As Dictionary
Dim CalledDirectFromWorksheet As Boolean

'For later use in selecting cells for worksheet output
CalledDirectFromWorksheet = False
If TypeOf Application.Caller Is Range Then
Set p = Application.Caller
q = p.Address
iRows = Range(q).Rows.Count
iCols = Range(q).Columns.Count
If InStr(1, p.FormulaArray, "ArrayUniques") = 2 _
Or InStr(1, p.FormulaArray, "arrayuniques") = 2 _
Or InStr(1, p.FormulaArray, "ARRAYUNIQUES") = 2 Then
CalledDirectFromWorksheet = True
End If
End If

'Convert an input range to a VBA array
arr = InputArray

'Load the unique elements into a Dictionary Object
Set x = New Dictionary
x.CompareMode = Abs(Not MatchCase) '<--Case-sensitivity
On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

'Load a 0-based horizontal array with the unique
'elements from the Dictionary Object
arr2 = x.Items

'This provides appropriate base and orientation
'of the output array
Select Case Base_Orient
Case "0horiz"
arr2 = arr2
Case "1horiz"
ReDim Preserve arr2(1 To UBound(arr2) + 1)
Case "0vert"
arr2 = Application.Transpose(arr2)
Case "1vert"
ReDim Preserve arr2(1 To UBound(arr2) + 1)
arr2 = Application.Transpose(arr2)
End Select

'Assure that enough cells are selected to accommodate output
If CalledDirectFromWorksheet Then
If Range(Application.Caller.Address).Count < x.Count Then
ArrayUniquesLtd = "Select a range of at least " & x.Count & " cells"
Exit Function
End If
End If

ArrayUniquesLtd = arr2

End Function

Function ArrayUniques(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True)
'THIS PROCEDURE REQUIRES A PROJECT REFERENCE
'TO "MICROSCOPIC SCRIPTING RUNTIME".
'The function returns an array of unique
'values from an array or range. By default
'it returns a 1-based vertical array; for
'other results enter "0horiz", "1horiz" or
'"0vert" as the third argument. By default,
'the function is case-sensitive; i.e., e.g.,
'"red" and "Red" are treated as two separate
'unique values; to avoid case-sensitivity,
'enter False as the second argument.

'Declare the variables
Dim arr, arr2
Dim i As Long, p As Object, q As String
Dim Elem, x As Dictionary
Dim CalledDirectFromWorksheet As Boolean

'For later use in selecting cells for worksheet output
CalledDirectFromWorksheet = False
If TypeOf Application.Caller Is Range Then
Set p = Application.Caller
q = p.Address
iRows = Range(q).Rows.Count
iCols = Range(q).Columns.Count
If InStr(1, p.FormulaArray, "ArrayUniques") = 2 _
Or InStr(1, p.FormulaArray, "arrayuniques") = 2 _
Or InStr(1, p.FormulaArray, "ARRAYUNIQUES") = 2 Then
CalledDirectFromWorksheet = True
End If
End If

'Convert an input range to a VBA array
arr = InputArray

'Load the unique elements into a Dictionary Object
Set x = New Dictionary
x.CompareMode = Abs(Not MatchCase) '<--Case-sensitivity
On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

'Load a 0-based horizontal array with the unique
'elements from the Dictionary Object
arr2 = x.Items

'This provides appropriate base and orientation
'of the output array
Select Case Base_Orient
Case "0horiz"
arr2 = arr2
Case "1horiz"
ReDim Preserve arr2(1 To UBound(arr2) + 1)
Case "0vert"
If x.Count < 5461 Or Application.Version 9 Then
arr2 = Application.Transpose(arr2)
Else
arr2 = ArrayTranspose(arr2)
End If
Case "1vert"
ReDim Preserve arr2(1 To UBound(arr2) + 1)
If x.Count < 5461 Or Application.Version 9 Then
arr2 = Application.Transpose(arr2)
Else
arr2 = ArrayTranspose(arr2)
End If
End Select

'Assure that enough cells are selected to accommodate output
If CalledDirectFromWorksheet Then
If Range(Application.Caller.Address).Count < x.Count Then
ArrayUniques = "Select a range of at least " & x.Count & " cells"
Exit Function
End If
End If

ArrayUniques = arr2

End Function

Function ArrayTranspose(InputArray)
'This function returns the transpose of
'the input array or range; it is designed
'to avoid the limitation on the number of
'array elements and type of array that the
'worksheet TRANSPOSE Function has.

'Declare the variables
Dim outputArrayTranspose As Variant, arr As Variant, p As Integer
Dim i As Long, j As Long

'Check to confirm that the input array
'is an array or multicell range
If IsArray(InputArray) Then

'If so, convert an input range to a
'true array
arr = InputArray

'Load the number of dimensions of
'the input array to a variable
On Error Resume Next

'Loop until an error occurs
i = 1
Do
z = UBound(arr, i)
i = i + 1
Loop While Err = 0

'Reset the error value for use with other procedures
Err = 0

'Return the number of dimensions
p = i - 2
End If

If Not IsArray(InputArray) Or p 2 Then
Msg = "#ERROR! The function accepts only multi-cell ranges and
1D or 2D arrays."
If TypeOf Application.Caller Is Range Then
ArrayTranspose = Msg
Else
MsgBox Msg, 16
End If
Exit Function
End If

'Load the output array from a one-
'dimensional input array
If p = 1 Then

Select Case TypeName(arr)
Case "Object()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Object
For i = LBound(outputArrayTranspose) To
UBound(outputArrayTranspose)
Set outputArrayTranspose(i,
LBound(outputArrayTranspose)) = arr(i)
Next
Case "Boolean()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Boolean
Case "Byte()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Byte
Case "Currency()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Currency
Case "Date()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Date
Case "Double()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Double
Case "Integer()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Integer
Case "Long()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Long
Case "Single()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Single
Case "String()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1)) As String
Case "Variant()"
ReDim outputArrayTranspose(LBound(arr) To UBound(arr),
LBound(arr) To LBound(arr)) As Variant
Case Else
Msg = "#ERROR! Only built-in types of arrays are
supported."
If TypeOf Application.Caller Is Range Then
ArrayTranspose = Msg
Else
MsgBox Msg, 16
End If
Exit Function
End Select
If TypeName(arr) < "Object()" Then
For i = LBound(outputArrayTranspose) To
UBound(outputArrayTranspose)
outputArrayTranspose(i, LBound(outputArrayTranspose)) =
arr(i)
Next
End If

'Or load the output array from a two-
'dimensional input array or range
ElseIf p = 2 Then
Select Case TypeName(arr)
Case "Object()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Object
For i = LBound(outputArrayTranspose) To _
UBound(outputArrayTranspose)
For j = LBound(outputArrayTranspose, 2) To _
UBound(outputArrayTranspose, 2)
Set outputArrayTranspose(i, j) = arr(j, i)
Next
Next
Case "Boolean()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Boolean
Case "Byte()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Byte
Case "Currency()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Currency
Case "Date()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Date
Case "Double()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Double
Case "Integer()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Integer
Case "Long()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Long
Case "Single()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Single
Case "String()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As String
Case "Variant()"
ReDim outputArrayTranspose(LBound(arr, 2) To
UBound(arr, 2), _
LBound(arr) To UBound(arr)) As Variant
Case Else
Msg = "#ERROR! Only built-in types of arrays are
supported."
If TypeOf Application.Caller Is Range Then
ArrayTranspose = Msg
Else
MsgBox Msg, 16
End If
Exit Function
End Select
If TypeName(arr) < "Object()" Then
For i = LBound(outputArrayTranspose) To _
UBound(outputArrayTranspose)
For j = LBound(outputArrayTranspose, 2) To _
UBound(outputArrayTranspose, 2)
outputArrayTranspose(i, j) = arr(j, i)
Next
Next
End If
End If

'Return the transposed array
ArrayTranspose = outputArrayTranspose
End Function

Alan Beban

Dunno if Chip Pearson's material will help. See
Duplicate And Unique Items In Lists
http://www.cpearson.com/excel/duplicat.htm
(the stuff dealing with unique items is towards the bottom)
and
Eliminating Blank Cells From Lists
http://www.cpearson.com/excel/noblanks.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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