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$10:$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$10:$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''.
0
Utf
3/27/2010 3:14:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
2600 Views

Similar Articles

[PageSpeed] 35

>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" <ryguy7272@discussions.microsoft.com> wrote in message 
news:8EB2ABE0-0210-4735-BC88-3F1660681CEC@microsoft.com...
>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$10:$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$10:$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''. 


0
T
3/27/2010 3:59:13 AM
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" <ryguy7272@discussions.microsoft.com> wrote in message 
> news:8EB2ABE0-0210-4735-BC88-3F1660681CEC@microsoft.com...
> >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$10:$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$10:$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''. 
> 
> 
> .
> 
0
Utf
3/27/2010 5:11:01 AM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"ryguy7272" <ryguy7272@discussions.microsoft.com> wrote in message 
news:DDC48C45-07ED-4CE4-B0D4-90E7EAA5E388@microsoft.com...
> 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" <ryguy7272@discussions.microsoft.com> wrote in message
>> news:8EB2ABE0-0210-4735-BC88-3F1660681CEC@microsoft.com...
>> >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$10:$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$10:$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''.
>>
>>
>> .
>> 


0
T
3/27/2010 4:44:28 PM
Reply:

Similar Artilces:

Inserting date where there is none and converting existing ones
Hello, I want to iterate through a column of dates and where the cell is blank I want to insert a preset date and where there is a date I want to convert it to a certain format. I've tried this formula for the first part: =IF("","20030101") but it doesn't work. So for example: A blank cell would get 20030101 inserted and a cell with 7/15/2003 would get 20030715. I'd like to do this with a formula if possible. Any help on this would be appreciated. Thanks in advance, --FB Frank, Bit of tangled solution here. If the cell contains "", it cannot ...

COMBIN and listing
I am looking for some assistance to achieve the following which I would prefer if possible to be able to carry out by using formula functions rather than macros which are not my forte. I have used the COMBIN formula to achieve the figures I require, eg: COMBIN(6,3) to give me 20 what I need is a formula that will actually list the 20 unique combinations, ie: 1 2 3 1 2 4 1 2 5 1 2 6 2 3 4 2 3 5 etc. etc. I have many of these of varying figures to carry out and to enter them by hand would be laborious and open to human error. I would appreciate any help or advise. Thank you in advance...

How to display multiple pages on one large monitor
I use Excel to create and print out the operations of school bus drivers. Every week we have to print out the sheets and display them for all to see, if something changes then we have to handwrite in the changes. What I want to do is display it all on a flat screen monitor so that when there are changes they are instantly udpated for everyone. THe proplem is that it would have to display multiple pages of the smae worksheet and at least on other worksheet as well. Is there a way to do this? Try Window>New Window then Window>Arrange. You can have multiple windows open side by ...

ADDING values from one listbox to another on same form
I want to be clear, because I know that BASING one listbox on another is a common question, but this is NOT basing one on the other; What I would like to do is ADD values from one to the other. Let's say I have 2 listboxes, one on the left, one on the right. The left one will have a source of a table. When the user double clicks one value in this litbox (or highlights it and clicks a button), I would like that value to appear in the right listbox. If the user doubleclicks another value in the left listbox, I'd like that value to be ADDED (or perhaps a better word wo...

Extracting data from multiple new workbooks via one master workboo
Every week several reports are generated from a vendor in pdf format. These reports are converted (successfully for the most part) using Able2ExtractPro. I have created a complex master spreadsheet that examins the converted pdf > xlsx and extracts the needed data. The problems is: Each new sheet has a different name. Therefore the reference to the new sheet need be changed in the master sheet to be able to locate the cells in that sheet. Short of search and replace in the master sheet changing the reference to the name of the new sheet, is there a way to reference the ...

Passing avalue from one form to another
I have a form (ItemBrowse_frm) that provides me a list of active Item numbers. When I click the Item number I want to pass the Item number to my other form (SingleBill_frm) and requery the form based on that part number. I am using the following OnClick Forms![SingleBill_frm]![ParentItembx].Value = Forms!ItemBrowse_frm.[ItemNumber] Forms![SingleBill_frm].Requery Somehow the SingleBill_frm does not requery and the field that I am passing the value to does not show the value passed until I highlight the field. Is there a better way to do this or what am I doing wrong? Thank you! ...

How to make Excel detect the "closest" date to the one I entered and return an value?
Hi all, I have some excel question and hope all the expert here ca help.. last time.. the SUMPRODUCT really helped me a lot and I am stil trying to really understand how it works.. anyways.... .. here is my problem... Col A is a list of date in ascending order, Col B is an numberic value Say in cell C1 , I will enter a random date.. and how can I make Exce to search through Col A and find the closest date to what I hav entered in C1 and return the corresponding Col B value ? Well. not really closest... as long as Col A is a date that is les then and yet the closest . imagine in Col ...

Reading one line at a time from text file, using CFile
When we use fstream due to char line[100]; int N=100; getline(line,N,'\n'); // or getline(line,N); we can read one line at a time. How can we do the same with the CFile object ? Alur Alur wrote: > When we use fstream due to > char line[100]; > int N=100; > getline(line,N,'\n'); // or getline(line,N); > we can read one line at a time. > How can we do the same with the > CFile object ? > Alur Use CStdioFile::ReadString. -- Scott McPhillips [VC++ MVP] "Alur" <Alur@discussions.microsoft.com> wrote in message news:46B...

Merging two spreadsheets
Hello, I think I have an easy one for you all. I have two tabs with different data, but same social security numbers. I want to match the corresponding data to one sheet. Problem is there are around 3200 rows on one sheet and about 4200 on the other so obviously I can't just sort and move it to where they match up. Is there a look up function that would make it simple. I know a little about vlookups. Thanks for the help. -- HCA ------------------------------------------------------------------------ HCA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

importing *.csv data into two columns
I have a data file that is delimited as comma separated values: escentially one long row of comma separated values. I need to import this long string into two columns: read two comma separated fields and start entering data into a new row and repeat. There are no empty cells as column 1 always has a matching value in column 2 for that particular row. I would appreciate any help. Thank you. Brian brian wrote: > I have a data file that is delimited as comma separated > values: ... I need to import this long string into two > columns: ... This looks like a job for Excel's ...

open two instances of outlook with seperate profiles
I have a few users who require sending email from two different mailboxes with different senders as in: jbloggs and ITsupport. When they compose the email, they must enter in the 'From' name. Is there any way to automatically set this up so that when they send from the 2nd profile/mailbox, the recipient sees that it came from the 'ITsupport' and same fro the 'jbloggs mail? Or does it always default to a the profile that was used to open Outlook? many thanks! ...

Outlook Synch errors on one desktop client
Hi, I have tried a bunch of things but I still have one Outlook client that has unknown errors when synching contacts. I have tried the usual uninstall/reinstall of both the desktop client and Outlook itself, deleting address book folders, setting the CRM options to not sync contacts, restart Outlook, delete contacts, then restore the setting to sync contacts, then attempt the sync again. No matter what I do the sync stops after 47 contacts come over to Outlook. The Local Data is set to - Full Name Has Data AND Status is Active. This should bring ALL CRM contacts over to Outlook (~7...

Migrating messages from one computer to another
I am having trouble getting my old express folders on my new computer. I copied the file onto a cd but can't copy them in to the store for my new outlokk express. Any ideas? This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplore...

How do I plot two columns of data as one data point/marker in a ch
I want to plot a series of results into a chart as one data point. To clarify, I have multiple rows with three colums of data. I would like each row (column one being the Series title column) to be one data point (i.e. Row One = Store 1, Column 1 = "Store 1", Column 2 = Sales $'s, Column 3 = Profit %). On the chart I would like the X-Axis to be Profit % and the Y-Axis to be Sales $'s. The example above, using typical charting functions, results in two data points for Store 1 (the Sales $ plotted as one point and the Profit % plotted as another point). I only want ...

Arrays data lost
Hi, I am trying to implement a Algorithm but when I run step by step my program I am finding out the value I have set previously into the array has been change. Does anyone come across the same problem before? Could you please give a hand tell me how can I prevent it to happen. Thank you for you help, here is what I am doing, really simple one dialog application, and in one button the following code //*********Step one**************** int DISTANCE [6]; int VISITED [6]; int current = 0; int vertexNum = 6; // number of nodes //****************step two****** int i =1; int j= 1; for(i=1; i<...

Combining data greater than 0
Hi, I have an order sheet where along the top I have the items and down the side I have the clients. Currently I enter quantities below the items at the top of the page to line up with each client rows. What I would like to do is have another column at the end of the sheet, which would summarize the order for me. E.g. If along the top I have apples, oranges, bananas, and down the side I have client A,B,C; I would like to have a summary column that would say 3-apples, 2-bananas. Any column with a 0 would not show up. I appreciate any help offered, Dave If all the headers don't...

MSR Credit Card Reader adds all the information on one line
Is there a way to get the CC reader to properly seperate the the acct # from the expiration date? When I swipe I currently get everything on the acct # line. Thanks in advance! What type of CC reader(MSR) to you have? Typically this is an issue with simply configuring the MSR. Rob "Steakum" wrote: > Is there a way to get the CC reader to properly seperate the the acct # from > the expiration date? When I swipe I currently get everything on the acct # > line. > > Thanks in advance! Its a IDTECH Minimag USB/Keyboard Interface Do I need to select the Wedge Key...

Auto-Increment Issue (couldn't find this one)
Windows XP Service Pack 3 Access 2007 (from Office 2007) Visual Basic 6.0 I have a table with an auto-increment column. This time of year, there is a lot of deleting and inserting going on in that table. Inserts are done through a Visual Basic program that does not touch the auto-increment field. Now and then, more so this time of year, I get a duplicate in the field and of course if fails. I found that, to fix it, I have too compact and repair the data base. Needless to say, that gets tiresome doing it multiple times in one day. Any solutions? thanx, nick Is it ...

Pasting into One Column
I have some text with tab characters that I would like to paste into one column. When I paste the text, it puts the text into multiple columns. How can I paste it all into one column? Thanks, Kevin Fosler Save it as a text document, open it with Excel, select Delimited, de-select the tab - set it to comma or click other and enter an % or some other character that doesn't appear in your document. It will open it with all you data in one, it will just require a little clean up. Just use the following formula in column B =CLEAN(A1) and copy it down to the end of your text. You can t...

One resource in multiple resource "groups"?
I'm trying to use the resource "group" field to further detail resource capabilities/competencies. As such, there are of course multi-talented people that fit into two or more competencies. Is there a way to assign a resource to multiple groups? Ideally, when looking at the resource sheet and grouping by "resource group" I would then see some resources twice. Thanks in advance for your help & advice. The only way to achieve what you want is to have two different resources representing different skill sets but the same person. Most people jus...

Save only values of one sheet
Hi, If I save a workbook as .CSV, Excel only saves the values of the current sheet in a *.CSV-file. Can I do EXACTLY the same - saving only values of the current sheet - in an *.XLS file (and of course in an XLS-format!). Eventually, formatting elements (like background and font colors) may also be saved. Thanks for any help. Anja. Just copy the sheet to a new workbook (right-click on the sheet tab, select Move or Copy from the menu, select (new book) from the To book dropdown, and check the Create a copy option, then OK), and save that new workbook. -- HTH RP (remove nothere from the...

Two side printing is offset
I still run MS Publisher 97 (love it). Every thing works fine. However, when I print on both sides of a paper, prints are offset by about 0.25". What causes this offset? How to correct this problem and make both prints to be printed exactly within the same space on both sides? -- Ilya Zeldes Fort Myers, Florida Are you using a duplex unit? I have a Publisher tutorial about printing back to back. If you send me your email address I will send it to you as a PDF. mary-sauer at columbus.rr.com -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msne...

how do I import a large amount of text into one cell?
I am trying to build a product database to upload to a new web site. I have to copy and paste descriptions fom various websites to begin with. I cannot seem to maintain orginal formats from the websites such as bullet points, etc. So can anyone tell me what the best method is for this? Also once I do have the product description, I have to paste into Excel so I can then convert to a "csv" file which is used by the web hosting service. When I paste a description into an excel cell, it covers up the entire worksheet! I know that excel can handle a few thousand characters, ...

Stock showing up under two symbols
I am using MSm 2004. When I download from Vanguard a stock comes in as BRK B. But, moneycentral.msn calls the stock BRK.B. How do I get it to recognize that these are one and the same instead of showing up twice in my portfolio? I have a similar problem with Nasdaq 100 Tr Unit Ser 1. The Vanguard download does not give it a symbol, but the symbol on moneycentral.msn is QQQQ. So it shows up twice in my portfolio. ...

Link VBA
Hi everyone, I am working on an VBA application that needs to send an array as an argument to an external libray written in C. The library uses the array as a vector of parameters, and then returns a certain value. The link is working well with a single argument (a double, an integer, etc), by reference or by value. But when I want to send an array (a vector of double for instance), it fails. Does anyone know how to do this? First, is it possible? Many thanks, Sylvain The only way I have succeeded in passing or returning arrays is abandoning the VBA declarations and writing an xll, ...