+ Reply to Thread
Results 1 to 12 of 12

Create Unique List from Data based on Critera

  1. 09-09-2015, 11:22 AM #1
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Create Unique List from Data based on Critera

    Hi All,

    I've run in to a sticky situation with a formula. I'm trying to generate a list of materials, which a certain customer buys. My data is currently in a separate worksheet in a table named 'Table 2'.

    I'm looking for a formula that will look at the customer number entered in A21, and bring up a list of the materials that are on the same row as the customer number in 'Table 2'. This is the formula that I've tried, which works perfectly okay for the first entry, but results in #NUM! when I drag the formula down.

    Please Login or Register  to view this content.
    Any help much appreciated!

    Dom
    Reply With QuoteRegister To Reply
  2. 09-09-2015, 11:23 AM #2
    FDibbins
    FDibbins is offline
    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,946

    Re: Create Unique List from Data based on Critera

    That is an ARRAY formula...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.
    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
    Reply With QuoteRegister To Reply
  3. 09-09-2015, 11:28 AM #3
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Re: Create Unique List from Data based on Critera

    Hi Ford,

    Thanks for the quick response. I understand that this is an array formula, I just hadn't included the curly bracers in the code above. Please see below screenshot for details.

    FormulaHelp.PNG

    Thanks,
    Dom
    Reply With QuoteRegister To Reply
  4. 09-09-2015, 11:42 AM #4
    FDibbins
    FDibbins is offline
    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,946

    Re: Create Unique List from Data based on Critera

    Hard to tell from a pic, can yopu upload a (clean) sample file please?
    Reply With QuoteRegister To Reply
  5. 09-09-2015, 11:50 AM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Create Unique List from Data based on Critera

    =IF(COUNTIF(Table2[Customer],$A$21) < ROWS($B$20:B20), “”, INDEX(Table2[Material],SMALL(IF(Table2[Customer]=$A$21,ROW(Table2[Customer])), ROW('RAW CA'!B1))))
    One possible problem is that unless your data starts on row 1 this expression could cause incorrect results:

    ROW(Table2[Customer])

    It should be:

    ROW(Table2[Customer])-MIN(ROW(Table2[Customer]))+1

    Even better, use A1 style referencing and index the entire column then you can eliminate:

    -MIN(ROW(Table2[Customer]))+1

    I call this the "offset correction".
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 09-10-2015, 07:36 AM #6
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Re: Create Unique List from Data based on Critera

    Hi All,

    Thanks for your replies. I've created a sample file to help describe it better - hope this helps!

    Test Sales Figures Gen COPY.xlsx

    Kind regards,
    Dom
    Reply With QuoteRegister To Reply
  7. 09-10-2015, 07:42 AM #7
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Create Unique List from Data based on Critera

    You locked

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  8. 09-10-2015, 07:46 AM #8
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Create Unique List from Data based on Critera

    you locked the DATA sheet's A2 cell with $ sign column & row. That is why it was not working.
    Reply With QuoteRegister To Reply
  9. 09-10-2015, 07:52 AM #9
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Re: Create Unique List from Data based on Critera

    That's intended, each row is supposed to point to the same cell so that I don't need to repeat the customer number down column A.

    Even if I remove my $ signs and enter the customer number on every line in the attachment below, I still get #NUM values.

    Test Sales Figures Gen COPY.xlsx
    Reply With QuoteRegister To Reply
  10. 09-10-2015, 08:14 AM #10
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Create Unique List from Data based on Critera

    Dear Brother 700044 feeding in raw CA data need to once copy to all as 700044 there must be some junk.

    Please check the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 09-10-2015, 08:50 AM #11
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Re: Create Unique List from Data based on Critera

    Thanks for this, seems to work fine. My only problem is that it cannot show duplicate values, I only need the list to show unique values and the rest of the cells to be blank.

    I'm using this as the base of another formula, so I need it to show only the unique values. For example if I used the formula in cells B2:B200, but the customer only had 6 unique entries, the bottom 194 rows would be blank.

    Is this possible?

    Please Login or Register  to view this content.
    In the attachment below, rows 57 onwards should be blank.

    Test Sales Figures Gen COPY.xlsx
    Reply With QuoteRegister To Reply
  12. 09-10-2015, 10:52 AM #12
    DomSza
    DomSza is offline
    Registered User
    Join Date
    04-09-2015
    Location
    Manchester, England
    MS-Off Ver
    2010
    Posts
    39

    Re: Create Unique List from Data based on Critera

    Hi All,

    I've worked this out, I was getting errors due to the formatting on the customer code column. I highlighted the column, went to Data, Text to Columns.

    Thanks for your help everyone.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create new data set based on critera
    By afpPaul in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2015, 05:14 PM
  2. Extracting Data from a list based on critera
    By skate1991 in forum Excel General
    Replies: 2
    Last Post: 12-29-2014, 12:51 PM
  3. [SOLVED] Create unique list based on date
    By solmyr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-22-2014, 07:31 AM
  4. [SOLVED] Create list based on criteria and is unique
    By joannelittell in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-13-2013, 02:33 PM
  5. [SOLVED] Create unique list based on specific criteria
    By Joynesy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-21-2013, 05:54 AM
  6. Create list of Unique values based on Worksheet Data
    By todmac in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 08:17 PM
  7. VBA: Create unique list based on latest entry in list
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-08-2011, 09:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1