I have a function that alphabetizes an array of text:

=INDEX($A$1:$A$10,MATCH(ROW(A1),COUNTIF($A$1:$A$10 ,"<="&$A$1:$A$10),))
CSE-entered

I also have a function that eliminates dupes:

=IF(ISERR(SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))),"",INDEX($A $10:$A$16,SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))))
CSE-entered

Now, Im wondering if I can combine the two. That will take some hack work.
Also, Im wondering how much this will slow down the workbook. There are
about 8-9 sheets in there and an array formula like this will probably make
the thing crawl, right. Anyway, my question is how do I combine there two
arrays?
Thanks,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hi Ryan,

Yes, it is possible to combine the two arrays. Here's how you can do it:

1. First, you need to combine the two arrays into one. You can do this by using the CONCATENATE function. For example, if your first array is in cells A1:A10 and your second array is in cells A11:A20, you can combine them into one array in cell B1 by entering the following formula:
   
   
   Formula:

   =CONCATENATE(A1:A10,A11:A20) 

2. Once you have combined the two arrays, you can use the same formula you used to eliminate duplicates:
   
   
   Formula:

   =IF(ISERR(SMALL(IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW(INDIRECT("1:"&ROWS($B$1:$B$20))),MATCH($B$1:$B$20,$B$1:$B$20,0)),ROWS($1:1))),"",INDEX($B$1:$B$20,SMALL(IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW(INDIRECT("1:"&ROWS($B$1:$B$20))),MATCH($B$1:$B$20,$B$1:$B$20,0)),ROWS($1:1)))) 

3. This formula will eliminate duplicates from the combined array.
   

Regarding your concern about the workbook slowing down, using array formulas can sometimes slow down a workbook, especially if you have a large amount of data. However, if you have a relatively small amount of data, it should not be a problem. If you do notice that the workbook is slowing down, you can try using other methods to eliminate duplicates, such as using the Remove Duplicates feature in Excel.

I have a function that alphabetizes an array of text:
I also have a function that eliminates dupes
I'm wondering if I can combine the two

Try these...

Does not work if there are empty cells within the range.

Data in the range A1:A10.

Enter this array formula** in C1:

=INDEX(A1:A10,MATCH(0,COUNTIF(A1:A10,"<"&A1:A10),0 ))

Enter this array formula** in C2 and copy down to C10:

=IF(COUNTIF(A$1:A$10,""&C1),INDEX(A$1:A$10,MATCH( COUNTIF(A$1:A$10,"<="&C1),COUNTIF(A$1:A$10,"<"&A$1 :A$10),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have a function that alphabetizes an array of text:

=INDEX($A$1:$A$10,MATCH(ROW(A1),COUNTIF($A$1:$A$10 ,"<="&$A$1:$A$10),))
CSE-entered

I also have a function that eliminates dupes:

=IF(ISERR(SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))),"",INDEX($A $10:$A$16,SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))))
CSE-entered

Now, I'm wondering if I can combine the two. That will take some hack
work.
Also, I'm wondering how much this will slow down the workbook. There are
about 8-9 sheets in there and an array formula like this will probably
make
the thing crawl, right. Anyway, my question is how do I combine there two
arrays?
Thanks,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

That is soooooo sweet! Thanks so much!!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

I have a function that alphabetizes an array of text:
I also have a function that eliminates dupes
I'm wondering if I can combine the two

Try these...

Does not work if there are empty cells within the range.

Data in the range A1:A10.

Enter this array formula** in C1:

=INDEX(A1:A10,MATCH(0,COUNTIF(A1:A10,"<"&A1:A10),0 ))

Enter this array formula** in C2 and copy down to C10:

=IF(COUNTIF(A$1:A$10,""&C1),INDEX(A$1:A$10,MATCH( COUNTIF(A$1:A$10,"<="&C1),COUNTIF(A$1:A$10,"<"&A$1 :A$10),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have a function that alphabetizes an array of text:

=INDEX($A$1:$A$10,MATCH(ROW(A1),COUNTIF($A$1:$A$10 ,"<="&$A$1:$A$10),))
CSE-entered

I also have a function that eliminates dupes:

=IF(ISERR(SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))),"",INDEX($A $10:$A$16,SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))))
CSE-entered

Now, I'm wondering if I can combine the two. That will take some hack
work.
Also, I'm wondering how much this will slow down the workbook. There are
about 8-9 sheets in there and an array formula like this will probably
make
the thing crawl, right. Anyway, my question is how do I combine there two
arrays?
Thanks,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
That is soooooo sweet! Thanks so much!!
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

I have a function that alphabetizes an array of text:
I also have a function that eliminates dupes
I'm wondering if I can combine the two

Try these...

Does not work if there are empty cells within the range.

Data in the range A1:A10.

Enter this array formula** in C1:

=INDEX(A1:A10,MATCH(0,COUNTIF(A1:A10,"<"&A1:A10),0 ))

Enter this array formula** in C2 and copy down to C10:

=IF(COUNTIF(A$1:A$10,""&C1),INDEX(A$1:A$10,MATCH( COUNTIF(A$1:A$10,"<="&C1),COUNTIF(A$1:A$10,"<"&A$1 :A$10),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
I have a function that alphabetizes an array of text:

=INDEX($A$1:$A$10,MATCH(ROW(A1),COUNTIF($A$1:$A$10 ,"<="&$A$1:$A$10),))
CSE-entered

I also have a function that eliminates dupes:

=IF(ISERR(SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))),"",INDEX($A $10:$A$16,SMALL(IF(MATCH($A$10:$A$16,$A$10:$A$16,0 )=ROW(INDIRECT("1:"&ROWS($A$10:$A$16))),MATCH($A$1 0:$A$16,$A$10:$A$16,0)),ROWS($10:10))))
CSE-entered

Now, I'm wondering if I can combine the two. That will take some hack
work.
Also, I'm wondering how much this will slow down the workbook. There
are
about 8-9 sheets in there and an array formula like this will probably
make
the thing crawl, right. Anyway, my question is how do I combine there
two
arrays?
Thanks,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


.