Combine Two Arrays Into One. Tough.
 

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

Answer: Combine Two Arrays Into One. Tough.
 

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.

Combine Two Arrays Into One. Tough.
 

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

Combine Two Arrays Into One. Tough.
 

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


.

Combine Two Arrays Into One. Tough.
 

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


.