+ Reply to Thread
Results 1 to 10 of 10

Index, Aggregate, Row - Removing Duplicate W/ Index Help

  1. #1
    Registered User
    Join Date
    01-03-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    O365
    Posts
    5

    Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Hi team,

    I'll try to keep this short, concise and simple. I've got two sheets of information, and I'm trying to leverage INDEX as a lookup function.

    My goal is: When I enter a dollar amount into cell B, return the service related to that value dependent on the facility. If there are any duplicates, enter 2, 3, 4 etc to retrieve the next available service under that criteria.

    Reason: I want to enter a dollar value because this is how our employees bill us their commissions - and they don't know how to use excel and write everything down. Also, using data validation is slow, even with combobox, for data entry.

    My problem is: Facilities may have different services that are priced at the amount, at each facility.

    A tiny sample of the issue is attached. I will need to extrapolate a solution over thousands of rows.

    Problem Breakdown:

    =INDEX(Pricebook!$B$2:$B$12,AGGREGATE(15,6,(ROW(Pricebook!$A$2:$A$12)-ROW(Pricebook!A2)+1)/(Pricebook!$A$2:$A$12=E2),C2))

    In cell C2, if I change the value between 1 and 2, it works perfectly according to expectations. However, when I drop the formula down, the other returned values in column D are all wrong and do not meet expectations.

    Any help is greatly appreciated,

    Thanks,
    Aaron
    Attached Files Attached Files

  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: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    It would help if you could show the desired results. Try this formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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
    02-28-2015
    Location
    Brazil
    MS-Off Ver
    2010
    Posts
    21

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Dear,

    Look again to the number.....are different.... 18,5 <> 18.5....try change....

  4. #4
    Registered User
    Join Date
    01-03-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    O365
    Posts
    5

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    *I hope I don't double post, I keep replying but nothing happens. Apologies if it does.

    Thanks for the quick reply AlKey. I did as suggested but it did not quite work as planned. Taking in your feedback, here are my desired results in relative column D for the same row number as column C:

    C2 = 1, returns 'Styling'
    C3 = 2, returns 'Styling and Blow Dry'
    C4 = 1, returns 'Cut & Blow Dry (no curl)'
    C5 = 2, returns 'Cut & Shampoo'
    C6 = 1, returns 'Haircut (both)'

    Thanks again!
    Aaron

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Try

    =INDEX(Pricebook!$B$2:$B$12,MATCH(A2&B2,Pricebook!$C$2:$C$12&Pricebook!$D$2:$D$12,0)+COUNTIFS($A$2:$A2,$A$2,$B$2:$B2,$B2)-1)

    Enter with Ctrl+Shift+Enter

  6. #6
    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: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Here it is

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    After thought ..

    put this in C2 and copy down


    =COUNTIFS($A$2:$A2,$A$2,$B$2:$B2,$B2)

    Formula in D2


    =INDEX(Pricebook!$B$2:$B$12,MATCH(A2&B2,Pricebook!$C$2:$C$12&Pricebook!$D$2:$D$12,0)+C2-1)

    Enter with Ctrl+Shift+Enter

  8. #8
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-03-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    O365
    Posts
    5

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Nice! I am super happy, this helps big time. Either AlKey's or John's solutions work for anyone who is referencing in the future.

    Real big thanks to the two of you, appreciate it.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

    Alkey's is better as it is not an array-formula. Similarly for Czeslaw.

+ 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. Removing blanks from data validation - using INDEX(MATCH) though...
    By graeme27uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2015, 08:42 AM
  2. [SOLVED] Excel 2007 : Removing the #n/a from this index match formula
    By Icehockey44 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-14-2015, 12:47 PM
  3. Replies: 2
    Last Post: 02-12-2015, 01:33 PM
  4. Removing #N/A from Index Match
    By missyboox22 in forum Excel General
    Replies: 1
    Last Post: 01-28-2015, 11:35 AM
  5. Removing item from collection without its index
    By Galven in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2014, 06:41 AM
  6. INDIRECT, INDEX, AGGREGATE?? Help!
    By bbr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2013, 08:03 PM
  7. Removing #REF! indirect index function
    By dyerdyerdyer in forum Excel General
    Replies: 5
    Last Post: 04-08-2011, 04:04 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