Convert Multiple Text entries...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

....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 said:
...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.
Click to expand...
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, 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!
 
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.
 
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.
 
Mark said:
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!
Click to expand...

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
 
Mark said:
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.
Click to expand...

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
 
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 didn’t include
…/beban. There were several hits, I don’t 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
 
Mark said:
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 didn’t include
…/beban. There were several hits, I don’t 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
Click to expand...
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
 
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(ArrayUniques('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!
 
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.
 
Mark said:
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.
Click to expand...

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
 
Back
Top