Remove "," from macro output and concatenate values from two column with "/" only when both the cell have same uniqque key in the row

[exlearner]

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

[TABLE="class: grid, width: 512, align: left"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Column A[/TD]
[TD="width: 64"]Column B[/TD]
[TD="width: 64"]Column C[/TD]
[TD="width: 64"]Column D[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Column G[/TD]
[TD="width: 64"]Column H[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]60[/TD]
[TD="width: 64, align: right"]61[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64"]D1[/TD]
[TD][/TD]
[TD="width: 64, align: right"]60[/TD]
[TD="width: 64"]60/61, 60/62[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]60[/TD]
[TD="width: 64, align: right"]62[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]9[/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64"]D2[/TD]
[TD][/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64"]10/100[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]90[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]D3[/TD]
[TD][/TD]
[TD="width: 64"]11, 12[/TD]
[TD="width: 64"]11/0, 12/90[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"]D4[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64"]NA[/TD]
[TD][/TD]
[TD="width: 64"]NA[/TD]
[TD="width: 64"]NA[/TD]
[/TR]
</tbody>[/TABLE]