Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
look for multiple entries | Excel Worksheet Functions | |||
UserForms - Viewing multiple entries in a text box. | Excel Discussion (Misc queries) | |||
Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) |