Hi All,
I will try my best to explain both the problems I am currently facing. I have tried various options, have searched different forum as well. Not usre if this has been ask earlier.. appologies for repeat.. can someone please help..
Data Set: I am looking to transform my data from Column A, Column B and Column C to Column G and Column H
Key Points:
- ID is my unique key, there multiple rows with same ID value, I need to pick a row that has value in Column D and copy it in another sheet. This has been done.
Problem1: For those rows with same ID value I need to pick distinct values from Column A and copy it in Column , basically concatenate. I am getting value as --> ,11,12,
Here i am using below formula.. not sure what else i need to do to remove the extra comma's.
=IF(D2="NA","NA",IF(Sheet1!L2="Unique",SUBSTITUTE(SUBSTITUTE(CONCAT(IF(Sheet1!$U$2:$U$5000=Q2,","&Sheet1!$AY$2:$AY$5000,"")),",","",1),",,",""),"NA"))
--- > copying exact formula above, column in the data set shared and the one in the formula is not exact.
Problem2: in Column H, I need to pic value from Column A, concatenate with Column B (if not blank) with "/" in between and copy in the row where I have non blank value in Column D. Actually I am copying in separate sheet but that will be easy once I have the formula
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
I will try my best to explain both the problems I am currently facing. I have tried various options, have searched different forum as well. Not usre if this has been ask earlier.. appologies for repeat.. can someone please help..
Data Set: I am looking to transform my data from Column A, Column B and Column C to Column G and Column H
Key Points:
- ID is my unique key, there multiple rows with same ID value, I need to pick a row that has value in Column D and copy it in another sheet. This has been done.
Problem1: For those rows with same ID value I need to pick distinct values from Column A and copy it in Column , basically concatenate. I am getting value as --> ,11,12,
Here i am using below formula.. not sure what else i need to do to remove the extra comma's.
=IF(D2="NA","NA",IF(Sheet1!L2="Unique",SUBSTITUTE(SUBSTITUTE(CONCAT(IF(Sheet1!$U$2:$U$5000=Q2,","&Sheet1!$AY$2:$AY$5000,"")),",","",1),",,",""),"NA"))
--- > copying exact formula above, column in the data set shared and the one in the formula is not exact.
Problem2: in Column H, I need to pic value from Column A, concatenate with Column B (if not blank) with "/" in between and copy in the row where I have non blank value in Column D. Actually I am copying in separate sheet but that will be easy once I have the formula
| ID | Column A | Column B | Column C | Column D | Column G | Column H | |
| 1 | 60 | 61 | 10 | D1 | 60 | 60/61, 60/62 | |
| 1 | 60 | 62 | NA | NA | NA | ||
| 1 | 9 | NA | NA | NA | |||
| 2 | 8 | D2 | 10 | 10/100 | |||
| 2 | 10 | 100 | 8 | NA | NA | NA | |
| 3 | 11 | 0 | 7 | NA | NA | NA | |
| 3 | 12 | 90 | D3 | 11, 12 | 11/0, 12/90 | ||
| 3 | NA | NA | NA | ||||
| 4 | 6 | D4 | NA | NA | |||
| 4 | 5 | NA | NA | NA |
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
