How to merge the rows and merge columns

[vinjam_likitha]

Hi,

I am facing issue while merging the rows, as column A B C D ARE SAME then merge needs to be performed.

Below is the input file

The output should be in the below format

Thanks in advance

Likitha

[THIRU_NANI (NaNi)]

Hey!

Use Join DataTable and choose the schema to Full Join…

This will works for sure

Try this and let me know

Regards,
NaNi

[vinjam_likitha]

Hi @THIRU_NANI

But we have only one datatable which is an input

In join datatable we have to give two datatables as input

How can we use that please let us know

Thanks in advance

likitha

[vinjam_likitha]

Hi @THIRU_NANI ,

Here i am attaching the excel file where Sheet 1 contains input and ouput is in “output” sheetname

Example.xlsx (11.9 KB)

Please go through the above file for reference

Thanks in Advance

Likitha

[THIRU_NANI (NaNi)]

Hey!

Try this:

DtNew = Dt.DefaultView.ToTable(True)

Regards,
NaNi

[vinjam_likitha]

Hi @THIRU_NANI ,

I used this DtNew = Dt.DefaultView.ToTable(True)

after implementing this it is deleting the rows which are similar and below is the output file.

Regards,
Likitha

[kumar.varun2 (Varun Kumar)]

Hi @vinjam_likitha

Try this

(
	From row In dt_Data
	Group row By
	k1=row("region"),
	k2=row("code"),
	k3=row("target"),
	k4=row("YY-MM")
	Into grp=Group
	Let d1 = grp.Where(Function(r1) Not r1("date1").ToString.Trim.Equals("")).Select(Function(rw1) rw1("date1")).ToArray
	Let d2 = grp.Where(Function(r2) Not r2("date2").ToString.Trim.Equals("")).Select(Function(rw2) rw2("date2")).ToArray
	Let d3 = grp.Where(Function(r3) Not r3("date3").ToString.Trim.Equals("")).Select(Function(rw3) rw3("date3")).ToArray
	Let d4 = grp.Where(Function(r4) Not r4("date4").ToString.Trim.Equals("")).Select(Function(rw4) rw4("date4")).ToArray
	Let mc = {d1.Count, d2.Count, d3.Count, d4.Count}.Max()
	From i In Enumerable.Range(0, mc)
	Let d_1 = If(d1.Count > i, d1(i), String.Empty)
	Let d_2 = If(d2.Count > i, d2(i), String.Empty)
	Let d_3 = If(d3.Count > i, d3(i), String.Empty)
	Let d_4 = If(d4.Count > i, d4(i), String.Empty)
	Select dt_Data.LoadDataRow({k1,k2,k3,k4, d_1, d_2, d_3, d_4}, True)
	).CopyToDataTable

XAML for reference

MergeRowsMergeColumns.xaml (6.2 KB)

[vinjam_likitha]

Hi kumar.varun2 ,

We tried this xaml but still we are not getting the expected output. The output of the provided xaml is shown below.

The Input excel file is given below:

The excel output file is given below:

Thanks
Likitha

[kumar.varun2 (Varun Kumar)]

Hi @vinjam_likitha

Running on the input file provided by you, I am getting this output

Could you share the Ireland file

[vinjam_likitha]

Hi THIRU_NANI,

Thanks got the expected output.

Likitha