How can I concatenate the rows in excel

Help Studio
, ,
1

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

2

Hi @HeartCatcher

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

3

Modd.xlsx (8.1 KB)

4

Hi @HeartCatcher

You can proceed like this

image

image

5

can you check:

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

6

Concatenate

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

7

Con final
Con final1885×965 64.3 KB

the final output

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

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

10

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))
11

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
12

Merge data1
Merge data11558×709 22.4 KB

Mergedata2
Mergedata21808×866 49.8 KB

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 xy***Z etc
instead of multiple rowsI want to make single row

13

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

14

@HeartCatcher

try this

Demo.xaml (7.0 KB)

15

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

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

Thanks & Regards,
Shubham Dutta

16

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

17

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

Your output will be in output.xlsx.

Thanks & Regards,
Shubham Dutta

18

@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

19

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

20

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:

grafik
grafik623×668 32.1 KB

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

1 Like