+ Reply to Thread
Results 1 to 6 of 6

Creating a unique list from source data with multiple items

  1. 05-13-2019, 03:28 AM #1
    27POP27
    27POP27 is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Creating a unique list from source data with multiple items

    Hi,

    I'm using a program that feeds by this raw data that looks like this:

    2019-05-13 09_22_55-Unique List from Source Data.xlsx - Saved.png

    I wish to make a list where all information for one NCR number (11727 for instance), contains all supplier and part information linked to that column.

    This would look something like this:
    1.png

    Thus far, I've been able to list one supplier and one part to each NCR no. What I'm having issues with is listing multiple parts to one NCR no. These are highlighted in blue.

    See attached excel sheet for this problem sample, and with my attempt so far.
    Attached Files Attached Files
    Last edited by 27POP27; 05-13-2019 at 07:31 AM.
    Reply With QuoteRegister To Reply
  2. 05-13-2019, 03:32 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,269

    Re: Creating a unique list from source data with multiple items

    Power Query would be one way to go - can you confirm that you have it (Get & Transform on the Data ribbon)?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-13-2019, 03:40 AM #3
    27POP27
    27POP27 is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Creating a unique list from source data with multiple items

    @AliGW
    Hi,
    Yes I do have the power query function. How would I use this to accomplish this?
    Reply With QuoteRegister To Reply
  4. 05-13-2019, 06:31 AM #4
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Creating a unique list from source data with multiple items

    Or, try this formula solution

    In J17, copied across to M17, and all copied down :

    =IFERROR(INDEX($D$2:$D$21,AGGREGATE(15,6,ROW($D$2:$D$21)-ROW($D$1)/($B$2:$B$21=$H17)/($C$2:$C$21="Part catalog"),-RIGHT(J$16,4))),"")

    Regards
    Bosco
    Reply With QuoteRegister To Reply
  5. 05-13-2019, 07:31 AM #5
    27POP27
    27POP27 is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Creating a unique list from source data with multiple items

    Amazing. Thanks. That worked perfectly.

    Might I ask a little of how this functions? There are some parts of it I don't get.
    1. Is there any reason to use ROW($D$1) instead of just 1? I tried it and it worked in this example.
    2. What does this do: -RIGHT(J$16,4) ? I can see it returns (1), (2), (3), (4) in the four columns.
    Last edited by AliGW; 05-13-2019 at 07:53 AM.
    Reply With QuoteRegister To Reply
  6. 05-13-2019, 08:10 AM #6
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Creating a unique list from source data with multiple items

    Hi,

    Q1. Is there any reason to use ROW($D$1) instead of just 1? I tried it and it worked in this example.

    Ans.1 =ROW($D$2:$D$21)-ROW($D$1) is equal to =ROW($D$2:$D$21)-ROW($D$2)+1 ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}


    Q2. What does this do: -RIGHT(J$16,4) ? I can see it returns (1), (2), (3), (4) in the four columns.

    Ans.2 =-RIGHT(J$16,4) give - (1),- (2),- (3), - (4) >> --1, --2, --3, --4 and final return 1, 2, 3, 4

    Regards
    Bosco
    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. Creating a unique list of all items meeting the criteria using an array formula
    By dsrt16 in forum Excel General
    Replies: 8
    Last Post: 08-21-2018, 04:59 PM
  2. Creating multiple excel files based on list of items
    By AlexeyY in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-15-2016, 06:22 PM
  3. Creating a unique list of values from column of data across multiple sheets, no blanks
    By polyrhythm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2014, 03:10 PM
  4. Creating Summary Sheet on Unique items (multiple columns) using VBA
    By EXCEL0429 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-22-2013, 09:55 PM
  5. Collate a list of unique items only from multiple sheets into specific categories
    By Boyler_Room in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2013, 08:05 PM
  6. [SOLVED] Counting # of Unique Items In List & Listing Unique Items In A List
    By unpluggedmusic in forum Excel General
    Replies: 3
    Last Post: 12-05-2012, 11:44 AM
  7. Creating a drop-down list & selecting multiple items
    By Jamie6521 in forum Excel General
    Replies: 1
    Last Post: 02-08-2009, 02:53 AM

Tags for this Thread

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