+ Reply to Thread
Results 1 to 4 of 4

Formula based Concatenated Value looking at multiple row values from two different columns

  1. #1
    Registered User
    Join Date
    01-04-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    24

    Formula based Concatenated Value looking at multiple row values from two different columns

    Hi,

    I have two columns ID & ID-2 (column D & E in attached sample data). I need the formula in column G which gives a concatenated value from col D & E screening multiple row values from the same columns.

    Please refer the attached sample data to understand the requirement better.

    The formula should consider all the below scenarios and populate the desired result based on the scenario it identified in the data.

    Scenario 1:
    All the values in column D & E of that customer are the same. No need to bring in the customer column into the formula as no two customers will not have the same ID numbers.
    Desired result: Don’t require concatenation as the ID did not get changed in all the 4 row items as shown in the sample data

    Scenario 2:
    All the values in column D & E of that customer gets changed in all the four row items.
    Desired result: Concatenated ID should include all the distinct values from those two columns (D & E) of that customer in all the row items of that customer.

    Scenario 3:
    Only few values in column D & E of that customer gets changed.
    Desired result: Concatenated ID should include all the distinct values from those two columns (D & E) of that customer in all the row items of that customer.

    Regards,
    N
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Formula based Concatenated Value looking at multiple row values from two different col

    You will likely need to go to VBA. Concatenating strings of unknown length would just be a horrendous grouping of if() statements and ampersands. For example, this equation would work for your example (in cell G6):
    =D6&IF(E6<>D6,"-"&E6,"")&IF(COUNTIF(D6:E6,D7)=0,"-"&D7,"")&IF(COUNTIF(D6:E7,E7)=1,"-"&E7,"")&IF(COUNTIF(D6:E7,D8)=0,"-"&D8,"")&IF(COUNTIF(D6:E8,E8)=1,"-"&E8,"")&IF(COUNTIF(D6:E8,D9)=0,"-"&D9,"")&IF(COUNTIF(D6:E9,E9)=1,"-"&E9,"")

    You would then have to make it more dynamic to consider instances where the values appear across more or less than 4 rows. It would get ugly, fast.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    01-04-2012
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    24

    Re: Formula based Concatenated Value looking at multiple row values from two different col

    Thank you Pauleyb

    Yes, the values in the actual data appear more or less than 4 rows. Can we apply the logic to the entire range of cells in both the columns (column D & E in attached sample data) especially from the row from where it is identified first till the end of data and get the desired result?

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Formula based Concatenated Value looking at multiple row values from two different col

    I'm not sure what you are proposing. Are you saying the logic of the equation I supplied? If so, then I don't think so, or it would be very complex in determining which rows are applicable. There may be some alternative method using a helper sheet to better organize the data, but if I had this problem I would move to VBA.

+ 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. Multiple values concatenated into one cell
    By brendanlowrywork in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-22-2017, 09:28 AM
  2. return multiple header cell values into one cell concatenated based on a cell value
    By anchuri_chaitanya in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-13-2016, 10:27 PM
  3. [SOLVED] Ranking formula based on values in multiple columns taken from a different sheet
    By m_t in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-15-2016, 03:52 PM
  4. [SOLVED] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  5. [SOLVED] Value of cell based on concatenated values
    By Michael Bass in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-19-2014, 11:10 AM
  6. Replies: 2
    Last Post: 12-04-2013, 04:21 PM
  7. vba to Match concatenated values in concatenated columns
    By bjurick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2012, 03:45 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