+ Reply to Thread
Results 1 to 10 of 10

combining fields based of another

  1. 04-17-2023, 01:02 PM #1
    araza123
    araza123 is offline
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    combining fields based of another

    Hi all,

    Need to know if there is a way to do this. I have a contract number, and for some contract numbers there are multiple names tied to those contracts. What i need to have happen is for every contract I need to add in those names into one line item so i can vlookup all of them at once. I have attached a example book.

    For example for in line 6-9 they are all the same contract number but you can see there each tied to a unique company name. I need to have it so that one contract has all 4 of those company names in one line.

    Let me know if more clarification is needed. Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-17-2023, 01:31 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: combining fields based of another

    Would a presentation like the following work for you? This was created in Power Pivot

    Excel 2016 (Windows) 64 bit
    D
    E
    3
    		 Row Labels Grouped Reinsurance
    4
    		 100001 PRICE SIMMS AUTO GROUP WAREHOUSE
    5
    		 100002 PRICE SIMMS AUTO GROUP WAREHOUSE
    6
    		 S1E04157369MAX BIG COAT RE., LIMITED, Charles Hamilton Wayne Reinsurance Company, Ltd., Shelby Wayne 2 Reinsurance, Ltd., SRCT 3 Reinsurance, Ltd.
    7
    		 S1E04157930MAX BIG COAT RE., LIMITED, Charles Hamilton Wayne Reinsurance Company, Ltd., Shelby Wayne 2 Reinsurance, Ltd., SRCT 3 Reinsurance, Ltd.
    8
    		 S1E04160763MAX BIG COAT RE., LIMITED, Charles Hamilton Wayne Reinsurance Company, Ltd., Shelby Wayne 2 Reinsurance, Ltd., SRCT 3 Reinsurance, Ltd.
    9
    		 S1E04162050MAX BIG COAT RE., LIMITED, Charles Hamilton Wayne Reinsurance Company, Ltd., Shelby Wayne 2 Reinsurance, Ltd., SRCT 3 Reinsurance, Ltd.
    Sheet: Sheet2
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 04-17-2023, 01:33 PM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,743

    Re: combining fields based of another

    are you still using excel 2013

    can you put an example of the results you need

    if 365 version you could use textjoin/unique/filter
    =TEXTJOIN(" / ",,UNIQUE(FILTER(B4:B21,A4:A21=A4)))

    if not , i dont have a solution in older version
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 04-17-2023, 02:32 PM #4
    araza123
    araza123 is offline
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: combining fields based of another

    This seems like its right! Next question is how do i expand this to encompass more data, when i add more data to column A and B and change the data source to pull that data in , the group reinsurance fx you have goes away.

    Or if easier i can mimic your grouped reinsurance formula just not sure how to add it back in. Thanks!
    Reply With QuoteRegister To Reply
  5. 04-17-2023, 02:45 PM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: combining fields based of another

    Not sure which post you are responding to. Please advise so that we can answer correctly. If you are referring to my post then here is a link to explain what I did. If you are subsequently adding to your source table, you should be able to use the Refresh Icon in excel to update the PT.

    https://sfmagazine.com/articles/2018...ble/?psso=true

    Also, if you are still using XL2013, this may not work for you as it is Power Pivot and the DAX function Concatenatex was only introduce in 2017.
    Last edited by alansidman; 04-17-2023 at 02:48 PM.
    Reply With QuoteRegister To Reply
  6. 04-17-2023, 02:53 PM #6
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: combining fields based of another

    If you are still using XL2013, here is an alternative solution with Power Query

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    1
    		 Contract No 1 2 3 4
    2
    		 100001 PRICE SIMMS AUTO GROUP WAREHOUSE
    3
    		 100002 PRICE SIMMS AUTO GROUP WAREHOUSE
    4
    		 S1E04157369MAX BIG COAT RE., LIMITED Charles Hamilton Wayne Reinsurance Company, Ltd. Shelby Wayne 2 Reinsurance, Ltd. SRCT 3 Reinsurance, Ltd.
    5
    		 S1E04157930MAX BIG COAT RE., LIMITED Charles Hamilton Wayne Reinsurance Company, Ltd. Shelby Wayne 2 Reinsurance, Ltd. SRCT 3 Reinsurance, Ltd.
    6
    		 S1E04160763MAX BIG COAT RE., LIMITED Charles Hamilton Wayne Reinsurance Company, Ltd. Shelby Wayne 2 Reinsurance, Ltd. SRCT 3 Reinsurance, Ltd.
    7
    		 S1E04162050MAX BIG COAT RE., LIMITED Charles Hamilton Wayne Reinsurance Company, Ltd. Shelby Wayne 2 Reinsurance, Ltd. SRCT 3 Reinsurance, Ltd.
    Sheet: Table1

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 04-17-2023, 03:15 PM #7
    araza123
    araza123 is offline
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: combining fields based of another

    Sorry I was repsonding to you. I have excel plus 2016 and do have power pivot installed. When i goto add more data in column A and B and then expand the data source throught he analyze tab, your fx grouped reinsurance goes away.

    See attached. I added on more data but ensuring it pulls in with the refresh causes the data in column D and E to disappear
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-17-2023, 04:16 PM #8
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: combining fields based of another

    Have you tried my Power Query solution? The beauty of Power Query, it updates automatically when you click on Refresh.

    to refresh Pivot Tables

    https://trumpexcel.com/refresh-pivot...0are%20changes.
    Last edited by alansidman; 04-17-2023 at 04:24 PM.
    Reply With QuoteRegister To Reply
  9. 04-17-2023, 04:22 PM #9
    araza123
    araza123 is offline
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    73

    Re: combining fields based of another

    let me give it a whirl, cant say i have.
    Reply With QuoteRegister To Reply
  10. 04-17-2023, 04:25 PM #10
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: combining fields based of another

    Please update your XL version in your profile
    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. Combining and looking up unique fields
    By araza123 in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 04-17-2023, 04:31 PM
  2. [SOLVED] Retrieving certain fields from a table using date fields as references based on creterias
    By McKneezy in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 03-29-2019, 10:47 PM
  3. Combining cell data based on matching multiple fields.
    By aweaver1026 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2016, 12:19 PM
  4. Combining Partial Name Fields
    By OlneyFD in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2014, 11:17 PM
  5. Combining 2 queries with Distinct Fields
    By Kyle123 in forum Access Tables & Databases
    Replies: 4
    Last Post: 02-24-2012, 04:33 AM
  6. Combining two Fields in Pivot
    By leo73pk in forum Excel General
    Replies: 1
    Last Post: 12-21-2010, 04:18 AM
  7. HELP: Formula for combining fields
    By pollewops in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2006, 05:20 PM

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