Results 1 to 4 of 4

Thread: Using Table Arrays to extract unique data from table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using Table Arrays to extract unique data from table

    Good afternoon,
    I'm trying to create an automatic table using an array function I found online... However when I tried to apply the formula to my table it finds an error. What I am trying to do is type in a date in cell C10, then the array function returns unique overall grades from that date.

    The array formula I entered into E10 (and dragged down..) is..
    =INDEX(Item, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))

    I did hold down Shift+CTRL when I hit enter when I modified the formula. I named B3:B8 "Date", as well as F3:F8 "Grade" for name references. I also made sure that the formula is linking the names and not the date function or whatnot.

    If anyone can help me figure out what is wrong with the formula that would be extremely helpful. Thank you

    Chris


    Below is the table that I am referring to..


    A B C D E F G
    1
    2 Date Comments Yes Total No Total Overall Grade
    3 7/24/2014 18 11 62.07%
    4 7/24/2014 25 16 60.98%
    5 7/24/2014 15 32 31.91%
    6 7/24/2014 17 33 34%
    7 7/24/2014 25 23 52.08%
    8 7/29/2014 31 14 68.89%
    9
    10 Search Search Results
    11
    12
    13

  2. #2
    New Member
    Join Date
    Jul 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Table Arrays to extract unique data from table


  3. #3
    Board Regular skywriter's Avatar
    Join Date
    Feb 2014
    Posts
    1,642
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Table Arrays to extract unique data from table

    Let me know if this is what you are after.

    Excel 2010
    BCDEFG
    2DateCommentsYes TotalNo TotalOverall Grade
    37/24/2014181162.07%
    47/24/2014251660.98%
    57/24/2014153231.91%
    67/24/2014173334%
    77/24/2014252352.08%
    87/29/2014311468.89%
    9
    10Search7/24/2014Search Results0.6207
    110.6098
    120.3191
    130.34
    140.5208
    15#NUM!

    Sheet35



    Array Formulas
    CellFormula
    E10{=INDEX(Grade, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Workbook Defined Names
    NameRefers To
    Date=Sheet35!$B$3:$B$8
    Grade=Sheet35!$F$3:$F$8

    Bruce
    ______________________________________________________
    There is no better way to learn than by doing.

    - Windows 7 -- Excel, Access & Word 2010
    - Post your spreadsheet online: Dropbox
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Jul 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Table Arrays to extract unique data from table

    Quote Originally Posted by skywriter View Post
    Let me know if this is what you are after.

    Excel 2010
    B C D E F G
    2 Date Comments Yes Total No Total Overall Grade
    3 7/24/2014 18 11 62.07%
    4 7/24/2014 25 16 60.98%
    5 7/24/2014 15 32 31.91%
    6 7/24/2014 17 33 34%
    7 7/24/2014 25 23 52.08%
    8 7/29/2014 31 14 68.89%
    9
    10 Search 7/24/2014 Search Results 0.6207
    11 0.6098
    12 0.3191
    13 0.34
    14 0.5208
    15 #NUM!
    Sheet35

    Array Formulas
    Cell Formula
    E10 {=INDEX(Grade, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Workbook Defined Names
    Name Refers To
    Date =Sheet35!$B$3:$B$8
    Grade =Sheet35!$F$3:$F$8

    Yes! Worked great thank you!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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