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

1. 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  Register To Reply

2. 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.  Register To Reply

3. 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?  Register To Reply

4. 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.  Register To Reply