Dynamic column data in one sheet

[Aarthy1 (Aarthy)]

Hi
There is 2 sheet and some columns for example 2 column Aa &Bb
In first sheet Aa data in 1st column Bb data in 2 column
In 2nd sheet Bb data in 1st column Aa datain 2 column
This way the columns are change in every updated file, but it has same header

In output sheet we need to merge all data in same header and remove the duplication also

Book.xlsx (423.6 KB)

[mkankatala (Mahesh Kankatala)]

Hi @Aarthy1

Follow the below steps,
→ Use Read range workbook activity to read the Excel Sheet1 and store it in a datatable called DT1.
→ Then use the assign activity if the columns are reverse like Bb and Aa to make it as Aa and Bb use the below expression,

- Assign -> DT1 = If(DT1.Columns(0).ColumnName = "Bb" And DT1.Columns(1).ColumnName = "Aa", DT1.DefaultView.ToTable(False, "Aa", "Bb"), DT1)

→ Then use the another read range workbook activity to read the Excel Sheet2 and store in another datatable variable called DT2.
→ Then use the assign activity to reverse the columns if it was in Bb and Aa,

- Assign -> DT2 = If(DT2.Columns(0).ColumnName = "Bb" And DT2.Columns(1).ColumnName = "Aa", DT2.DefaultView.ToTable(False, "Aa", "Bb"), DT2)

→ Then use the Merge Datatable activity to merge the two datatables. Give the DT2 as source and DT1 as Destination. All data will be merged to DT1.
→ Then use the Remove Duplicate rows activity to remove the duplicates rows in DT1.
→ Then use the Write range workbook activity to write the DT1 final data to the Ouput Excel sheet.

Check the below workflow for better understanding,
Sequence7.xaml (10.3 KB)

Hope it helps!!