+ 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 2007
    Posts
    18

    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,563

    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 2007
    Posts
    18

    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,563

    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)

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