+ Reply to Thread
Results 1 to 7 of 7

retrieving addresses of numbers in an array

  1. #1
    Registered User
    Join Date
    08-03-2013
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    3

    retrieving addresses of numbers in an array

    I am attempting to retrieve a list of the ten smallest numbers in a list of about 70 (in which there are duplicates, and i need the duplicates listed) and then use the INDEX function to copy the data beside these ten numbers and put it beside the list that i have created, beside it's corresponding number (even put the correct data beside each duplicate number, in order, as they have different data beside each of them). I have managed to get a list of the ten smallest numbers (Including duplicates) by using the following formula in a column of ten cells

    =SMALL(CHOOSE(1,A1:A70),1)
    =SMALL(CHOOSE(1,A1:A70),2)
    =SMALL(CHOOSE(1,A1:A70),3)
    etc...

    however when i attempt to use the above formula as a row number in an INDEX function, it will not work.
    Any help much appreciated!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: retrieving addresses of numbers in an array

    You can do it with one formula

    =SMALL(A1:A8,ROW())
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    08-03-2013
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: retrieving addresses of numbers in an array

    Quote Originally Posted by AlKey View Post
    You can do it with one formula

    =SMALL(A1:A8,ROW())
    this is very close to what i am looking for however i am unsure how to use it, i only
    started using Excel about 3 days ago and while i have used other logic systems before and
    can program LUA code at a basic level i still class myself as a beginner.

    the whole array looks like this:

    Data Data Number Data Data
    Data Data Number Data Data
    Data Data Number Data Data
    Data Data Number Data Data
    Data Data Number Data Data
    (there are lots more rows than this)

    i am attempting to bring up a list of the ten rows which have the smallest number (where
    there could be 0 or 100 duplicates) and these ten rows should look exactly like the ones
    in the array

    by using the code i provided in my first post the list of numbers has come up in the
    correct place and it looks like the following (Bearing in mind that the numbers will
    change)

    0
    0
    0
    0
    0
    2.1333333
    2.1333333
    2.4
    3.2
    3.2

    but i am unsure how to reference data beside the cells which my column of ten numbers
    refers to (as there are duplicates i cannot use
    INDEX(array,MATCH(the column of ten numbers),required column number)
    since the MATCH would just bring up the top duplicate for every duplicate however if the
    INDEX function can be used the column number could be COLUMN() as the list that i am
    trying to retrieve is underneath the array and in line with it.

    i expect that it is more simple than i am making it however not having been on a training
    course i am not that proficient yet.

    Thanks

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: retrieving addresses of numbers in an array

    You could use ROW() function against each cell. This will give you a unique value for each record.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: retrieving addresses of numbers in an array

    Alkey, the row() function can be used to increase the count, as you suggested. However, in a list with duplicates, it will still only pull the 1st instance of a match, using index/match (or vlookup)

    Hyperpathetical , I used your sample data, extened to 20 rows and added info in the column next to it. So with your sample data in A1:A20, and the other info in B1:B20, try this.

    1st, add a helper column in C, and copy this down to the end of your data...
    =A1+(COUNTIF($A$1:A1,A1)/100)
    This is a little trick to find and "fix" tie-breaks/duplicates...it counts the incidence of an entry and progressively adds 1/100 to each duplicate it finds.

    You can then use this to pull out the corresponding values from B...
    =INDEX($B$1:$B$20,MATCH(SMALL($C$1:$C$20,ROW(A1)),$C$1:$C$20,0),1)

    the SMALL($C$1:$C$20,ROW(A1)) row(A1) part is used to increment the "count" by 1 for each row down you copy.

    Hope that helps?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    08-03-2013
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: retrieving addresses of numbers in an array

    Thank You So Much FDibbins, that solved my problem in one and is actually quite clever, never thought of that so Well Done!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: retrieving addresses of numbers in an array

    Happy to help and thanks for the feedback and kind words

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using an array to create an array of addresses
    By jase250 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 11:28 PM
  2. Run time error when retrieving data from array
    By ZahraShuaib in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2010, 05:12 AM
  3. Replies: 1
    Last Post: 07-19-2010, 12:03 PM
  4. Retrieving multiple rows of data in an array
    By Dadx6 in forum Excel General
    Replies: 3
    Last Post: 06-11-2009, 01:01 PM
  5. retrieving values which are not 0 from an array
    By adeina in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-25-2007, 08:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1