I have an input table as above. Does anyone know who to concatenate all the columns (OUD, stimulant, depressant, hallucinogen, dissociative, inhalant) into the column “OUD_SUD”? However, i only want to concatenate when there is a value in the cell. When the cell is blank, don’t do anything. I tried to do it before when when the cell is blank it showed up as (,) or (opiates,). Thank you so much!
Input:
output:
Hi @ndtran85
→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Use for each row in datatable activity to iterate the each row in the Input_dt.
→ Inside for each insert assign activity and write the expression,
- Assign -> CurrentRow("OUD_SUD") = String.Join(", ", {CurrentRow("OUD").ToString, CurrentRow("STIMULANT").ToString, CurrentRow("DEPRESSANT").ToString, CurrentRow("HALLUCINOGEN").ToString, CurrentRow("DISSOCIATIVE").ToString, CurrentRow("INHALANT").ToString}.Where(Function(x) Not String.IsNullOrWhiteSpace(x)))
→ After that use the write range workbook activity to write the Input_dt to same excel.
Check the below workflow for better understanding,
2024.xaml (18.9 KB)
Input in Sheet1 and output in sheet2 in the below excel file -
Demo Data.xlsx (9.8 KB)
Output -
Hope it helps!!
Concatenate_example.xlsx (9.8 KB)
It works. Thank you so much! this is awesome!
It’s my pleasure… @ndtran85
Happy Automation!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
