How can I concatenate the rows in excel

Hi All,

I want to concatenate the number of rows which is dynamic i.e sometimes I need to concatenate rows
Input :
Column A
item 1
item 2


item n

in columnA some time the rows will be 2 ,sometime 3 rows , 4 row s etc

I want to concatenate like item1+ “" + item2 if the number of rows is 2
I want to concatenate like item1+ "
” + item2+ “" + item3 if the number of rows is 3
I want to concatenate like item1+ "
” + item2+ “" + item3+ "” + item4 if the number of rows is 4
etc
but it should concatenate between 3 astrik symbol “***”.

Can someone please do needful to do this??

@ushu
@Nithinkrishna
@Palaniyappan
@ppr
@Yoichi
@Rahul_Unnikrishnan
@lakshman
@THIRU_NANI

Hi @HeartCatcher

Could you clarify more. Please provide the sample input and output.

Modd.xlsx (8.1 KB)

Hi @HeartCatcher

You can proceed like this

image

image

can you check:

strFlatten = String.Join(“|”, yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))

Concatenate

from the excel I want merger 2+A3 like
“FULL 14 POSITION BCBSNC MEMBER ID REQUIRED.”+***+“RRQ Valid Referral Format Required”


the final output

strFlatten = String.Join("***" yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))

it is dynamic some time I need to merge A2+A3, sometime A2+A3+A4 etc and so on

do one thing: respecify your requirements in complete version. Share with us samples and its output descriptions. Use the </> format button from editor, when sharing snippets influencing MD Code from editor e.g. * making italic

code is concatenating dynamic the row/col values maybe you can adapt by following:

strFlatten =

String.Join("***" & Environment.NewLine, yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))

Hi @HeartCatcher

you can try this

image

dt_Output =

(
	From i In Enumerable.Range(0, dt_Data.Rows.Count)
	Let s = String.Join(" *** ", dt_Data.AsEnumerable.Take(i+1).Select(Function(r) r("Column1").ToString))
	Select dt_Data.LoadDataRow({s}, True)
).CopyToDataTable

Output

image

Demo.xaml (8.7 KB)

1 Like



Please refer different scenario

If A coulmn contains A,B,C,D(row1,row2,row3,row4) then output should be
ABCD
in the same if the input is x,y,z then output should be x
y***Z etc
instead of multiple rowsI want to make single row

Modd (1).xlsx (8.0 KB)
Can you please implement on the excel please and send the code

@HeartCatcher

try this

Demo.xaml (7.0 KB)

Hi @HeartCatcher ,
New folder.zip (8.8 KB)

This will give your desired output in logs with high accuracy.

Thanks & Regards,
Shubham Dutta

this is correct can you please the final output in excel,
I mean write into an excel instead of showing log message

Hi @HeartCatcher ,
New folder.zip (15.0 KB)

Your output will be in output.xlsx.

Thanks & Regards,
Shubham Dutta

@HeartCatcher
case can be solved as following
grafik

we do not recommend to reimplement a String.Join. When a non-Linq approach is used, then we would loop over the datatable and construct a list from the column values

Its not about what is recommended, the other person who posted question should get a detailed solution with understanding.

I absolutely agree on this. Regardless what technically is possible a solution approach needs the understanding from the other side.

So we also mentioned the List approach which would look like this:
grafik

vs:

Lasly the requestor will map his Implementation standards on the different approaches and will find the matching one.

1 Like