+ Reply to Thread
Results 1 to 15 of 15

Formula needed - line item of unique item matching criteria

  1. #1
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Formula needed - line item of unique item matching criteria

    Formula - line item of unique item matching criteria.xlsx

    The attached file has 2 sheets: Formula and Data.
    The formula sheet is the one where I need the formula. The yellow cells show the result the formula should return.
    The data sheet has 2 columns. The first shows the row number of each item. The second column shows the cost type.
    The formula needs to return the row number of the first "Accrual" in the data tab. The next result of the formula needs to show the row number of the second "Accrual". The next result of the formula needs to show the row number of the third "Accrual". etc.
    Thanks.

  2. #2
    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,917

    Re: Formula needed - line item of unique item matching criteria

    Try this...
    =INDEX(Data!B:B,MATCH(Formula!A2,Data!A:A,0))
    copied down
    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

  3. #3
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Formula needed - line item of unique item matching criteria

    This will return the word "Accrual" so I changed the INDEX(Data!B:B,... to INDEX(Data!a:a,...
    Unfortunately when copied down it returns:
    2
    3
    6
    #N/A
    #N/A
    2
    3
    6
    #N/A
    #N/A

    I want it to return:
    2
    3
    6

  4. #4
    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,917

    Re: Formula needed - line item of unique item matching criteria

    So you just want to return what is in Data A2...A3...A4 etc?

    Is so, then just use...
    =IF(Data!A2="","",Data!A2)

    Or maybe Im not undetstanding what you want?How did you pick 2, 3 & 6 on the Formula sheet?

    hmm perhaps this ARRAY formula...
    =IFERROR(INDEX(Data!$A$2:$A$6,SMALL(IF(Data!$B$2:$B$6="Accrual",ROW(Data!$A$2:$A$6)-1),ROWS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  5. #5
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Formula needed - line item of unique item matching criteria

    The array formula worked perfectly. Thank you!

  6. #6
    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,917

    Re: Formula needed - line item of unique item matching criteria

    Great, glad it worked for you

  7. #7
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Formula needed - line item of unique item matching criteria

    testing.xlsm

    Can you please look at this attached file and let me know what's wrong with the formula that's causing it to return the incorrect result? The formula in cell B20 should be returning 6 not 10. Thanks.

  8. #8
    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,917

    Re: Formula needed - line item of unique item matching criteria

    =IFERROR(INDEX($B$6:$B$18,SMALL(IF($E$6:$E$18="Accrual",ROW($B$6:$B$18)-5),ROWS($B$19:B19))),"")

    Your data starts on row 6, not row row 2, as in your 1st sample, you need to "remove" the rows above where your sample data starts

    (Again, CSE)

  9. #9
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Formula needed - line item of unique item matching criteria

    So you're saying that I can't have my data start where this new file starts? If not, do you have another solution as my file has headers and subtotals above the data so I need to have my formula allow that.

  10. #10
    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,917

    Re: Formula needed - line item of unique item matching criteria

    No, thats not what Im saying

    In your 1st sample WB, your headings were in row 1 and your data started in row 2, so my formula too that into account by using -1...
    =IFERROR(INDEX(Data!$A$2:$A$6,SMALL(IF(Data!$B$2:$B$6="Accrual",ROW(Data!$A$2:$A$6)-1),ROWS($A$1:A1))),"")

    In your 2nd sample, your data started in row 6, so I adjusted the formula to cater for that by using -5 instead...
    =IFERROR(INDEX($B$6:$B$18,SMALL(IF($E$6:$E$18="Accrual",ROW($B$6:$B$18)-5),ROWS($B$19:B19))),"")
    (see the bolded parts)

    You just need to adjust that value according to what row your data starts in

  11. #11
    Forum Contributor
    Join Date
    10-11-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Formula needed - line item of unique item matching criteria

    Thank you so much for explaining this to me. I had just taken your formula and used it without understanding the formula.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula needed - line item of unique item matching criteria

    If you index the entire column then you don't have to use an "offset correction".

    =IFERROR(INDEX(B:B,SMALL(IF(E$6:E$18="Accrual",ROW(E$6:E$18)),ROWS(B$19:B19))),"")

    The ROWS() function should refer to the first cell the formula is entered into. If the first cell the formula is being entered into is B19 then use:

    ROWS(B$19:B19)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  13. #13
    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,917

    Re: Formula needed - line item of unique item matching criteria

    Tony, doesnt referencing entire columns in an array formula, slow things down? (Just asking, for my own benifit)

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula needed - line item of unique item matching criteria

    Not in this application.

    You're not actually doing anything to the data in column B. You're just "telling" Excel that's where the data is that you want to find.

  15. #15
    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,917

    Re: Formula needed - line item of unique item matching criteria

    OK thanks, good to know

+ 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] Vlook up Max date only for an item with multiple entries with two unique criteria and ret
    By jcicero57 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-04-2013, 10:16 PM
  2. Line Item Formula
    By bbarry404 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 10:40 AM
  3. Replies: 5
    Last Post: 07-26-2011, 10:35 PM
  4. Replies: 9
    Last Post: 05-21-2011, 12:14 AM
  5. Replies: 5
    Last Post: 06-13-2006, 01:10 PM

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