Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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.

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text 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!

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

Alan, Its working.

In conjunction with the formula below and the Module from
http://home.pacbell.net/beban
I'm showing no anomalies or errors. EXCELent!

I goggled €œarrayuniqes€� from your other post when you didnt include
€¦/beban. There were several hits, I dont remember which one I used, but I
over wrote with the above.

I am using this to track
OCn/DS3/Circuits/Carrier/Customer/PBX/SignalGroups/CCIDs/etc.. Added this to
the abilities of =sumproduct this greatly improves the reliability of the
report. What used to take days of manual research, with the risk of human
error, is now at my finger tips with high reliability.

Many, many thanx Mark



"Alan Beban" wrote:

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

Convert Multiple Text entries...
 

Mark wrote:
Alan, Its working.

In conjunction with the formula below and the Module from
http://home.pacbell.net/beban
I'm showing no anomalies or errors. EXCELent!

I goggled €œarrayuniqes€� from your other post when you didnt include
€¦/beban. There were several hits, I dont remember which one I used, but I
over wrote with the above.

I am using this to track
OCn/DS3/Circuits/Carrier/Customer/PBX/SignalGroups/CCIDs/etc.. Added this to
the abilities of =sumproduct this greatly improves the reliability of the
report. What used to take days of manual research, with the risk of human
error, is now at my finger tips with high reliability.

Many, many thanx Mark
That's what it's all about; tools to make things easier. Thanks for the
feedback.

BTW, I never could figure out exactly what you included in your
workbook, but if you use the ArrayUniques function on large arrays
(greater than 65536 elements) you need to also have available the
ArrayTranspose function.

Alan Beban

Convert Multiple Text entries...
 

Alan, I went to http://home.pacbell.net/beban, clicked on "Array Functions",
saved to my PC. Opened ArrayFunctions.xls, Tools, Macros, Visual Basic
Editor. Right Click Module 1, export file (saved as AlanBeban.bas). Opened
MyFile, Tools, Macros, Visual Basic Editor. Right Click Modules, Import
Alanbeban.bas. Copied the formula you sent to note pad, edited for my
application, pasted into MyFile.

=IF(ISERROR(INDEX(ArrayUniques('T1
Assignments'!D$3:D$322),ROW(A1),1)),"",INDEX(Array Uniques('T1
Assignments'!D$3:D$322),ROW(A1),1))

Autofilter works in both the destination sheet (formula above) and if I
filter in the sheet the information retreive the data from (T1 Assignements).

This is way cool!

"Alan Beban" wrote:

Mark wrote:
Alan, Its working.

In conjunction with the formula below and the Module from
http://home.pacbell.net/beban
I'm showing no anomalies or errors. EXCELent!

I goggled €œarrayuniqes€� from your other post when you didnt include
€¦/beban. There were several hits, I dont remember which one I used, but I
over wrote with the above.

I am using this to track
OCn/DS3/Circuits/Carrier/Customer/PBX/SignalGroups/CCIDs/etc.. Added this to
the abilities of =sumproduct this greatly improves the reliability of the
report. What used to take days of manual research, with the risk of human
error, is now at my finger tips with high reliability.

Many, many thanx Mark
That's what it's all about; tools to make things easier. Thanks for the
feedback.

BTW, I never could figure out exactly what you included in your
workbook, but if you use the ArrayUniques function on large arrays
(greater than 65536 elements) you need to also have available the
ArrayTranspose function.

Alan Beban

Convert Multiple Text entries...
 

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

Convert Multiple Text entries...
 

Mark wrote:
Alan, I went to http://home.pacbell.net/beban, clicked on "Array Functions",
saved to my PC. Opened ArrayFunctions.xls, Tools, Macros, Visual Basic
Editor. Right Click Module 1, export file (saved as AlanBeban.bas). Opened
MyFile, Tools, Macros, Visual Basic Editor. Right Click Modules, Import
Alanbeban.bas.

That sounds like the functions are available to the one workbook,
MyFile. If you use arrays regularly you might want to consider putting
the file in your Personal.xls folder so that it opens with all your
Excel files.

Alan Beban