....into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.

I use Jim Cone's fine commercial Add-in called XLCompanion for tasks like
this. It's available at

www.realezsites.com/bus/primitivesoftware/

vaya con Dios,
Chuck, CABGx3

"Mark" wrote:

...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Did you download the file from the Web site?

Alan Beban

Mark wrote:
Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:


Mark wrote:

...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.

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

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Allen, I couldn't find it on the website you indicated, (Found it on another
site)

I got it to work, had to select all the cells with the formula and hit
CTRL/SHIFT/ENTER. But, my destination has the potential for 50 unique entries
from a column of 320, I'm getting #N/A in all the cells (45) except the 5
with unique entries.


"Alan Beban" wrote:

Did you download the file from the Web site?

Alan Beban

Mark wrote:
Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:


Mark wrote:

...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.

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

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Mark wrote:
Allen, I couldn't find it on the website you indicated, (Found it on another
site)

That's because I screwed up. The link is

http://home.pacbell.net/beban

Sorry,

Alan Beban
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

Mark wrote:
Allen, I couldn't find it on the website you indicated, (Found it on another
site)

I got it to work, had to select all the cells with the formula and hit
CTRL/SHIFT/ENTER. But, my destination has the potential for 50 unique entries
from a column of 320, I'm getting #N/A in all the cells (45) except the 5
with unique entries.


I'm not sure that this should cause you any problem, but if you can't
stand it you might use

=IF(ISERROR(INDEX(ArrayUniques(A$1:A$320),ROW(A1), 1)),"",INDEX(ArrayUniques(A$1:A$320),ROW(A1),1))

entered (NOT array entered) into the first cell and fill down to the
fiftieth cell. Substitute, of course, the appropriate range for A$1:A$320.

Alan Beban

Allen, found another post by you, I copied the "Function ArrayUniquesLtd"
module, verified "Microsoft Scripting Runtime" was checked. Now I get "
Select a range of at least 5 cells" which is exactly how many unique entries
I have in a column of 320, with some blanks.
I appear to be real close, just can't seal the deal. I am using Excel 2003.
Pls help, this is awesome!


"Mark" wrote:

Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Allen, got it to work, had to select all the cells with the formula and hit
CTRL/SHIFT/ENTER. But, my destination has the potential for 50 unique entries
from a column of 320, I'm getting #N/A in all the cells (45) except the 5
with unique entries.

"Mark" wrote:

Allen, found another post by you, I copied the "Function ArrayUniquesLtd"
module, verified "Microsoft Scripting Runtime" was checked. Now I get "
Select a range of at least 5 cells" which is exactly how many unique entries
I have in a column of 320, with some blanks.
I appear to be real close, just can't seal the deal. I am using Excel 2003.
Pls help, this is awesome!


"Mark" wrote:

Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Mark wrote:
Allen, found another post by you, I copied the "Function ArrayUniquesLtd"
module, verified "Microsoft Scripting Runtime" was checked. Now I get "
Select a range of at least 5 cells" which is exactly how many unique entries
I have in a column of 320, with some blanks.
I appear to be real close, just can't seal the deal. I am using Excel 2003.
Pls help, this is awesome!

Array enter the formula into a 5-cell column; i.e., enter with
Ctrl-Shft-Enter instead of just enter.

Post back if that doesn't get you there.

BTW, what was the subject and date of the other post by me; I'd like to
see what you got.

Thanks,
Alan Beban

Did you download and install the workbook Alan pointed you to?

Another method to get all uniques to a sparate sheet is to use Advanced Filter
an check "Unique records only".

For more on this see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfi....html#FilterUR

Note section on extracting uniques to another sheet.


Gord Dibben MS Excel MVP

On Fri, 19 Oct 2007 13:41:03 -0700, Mark wrote:

Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban

Gord, I tried advance filters before, but missed the boat on gettting the
information to another sheet, I'll give this a try too. Thx.

"Gord Dibben" wrote:

Did you download and install the workbook Alan pointed you to?

Another method to get all uniques to a sparate sheet is to use Advanced Filter
an check "Unique records only".

For more on this see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfi....html#FilterUR

Note section on extracting uniques to another sheet.


Gord Dibben MS Excel MVP

On Fri, 19 Oct 2007 13:41:03 -0700, Mark wrote:

Thanx Alan, but I got #NAME?. I did hit CTRL+SHIFT+ENTER

"Alan Beban" wrote:

Mark wrote:
...into a single occurance. I have 1 sheet that can have multiple text
(Client) entries, duplicates permited. On another sheet I would like to see
each text entry only once. I'll use this "field/result" with a sumproduct to
determine total items by Client.
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook

=ArrayUniques(rangeName)

array entered into a column of cells sufficient to accommodate the
number of unique entries will return a list of unique values.

Alan Beban