How to get data from chile sheet basis on main sheet

[suraj_gaikwad]

Hello Everyone,

As above image that is Main sheet “MASTER”

Some data from child sheet as shown in below
1:-

2:-

So now i have to get that data basis on " MASTER " Sheet
Check the columns Sebi report csv = Yes then go to that specific header sheet name and get that data only which match with client code from master sheet.

As per above image 2 and 3 for copy data from specific sheet headers.

Thanks

[SorenB]

Hello @suraj_gaikwad

I hope that I have understood your requirement correctly.

1. Loop rows in MASTER sheet
2. If column has value “Yes”, then read the subsheet with column name
3. Count (or extract) the rows in the subsheet where Client ID matches the one from MASTER sheet

Download here:
Excel.xaml (13.2 KB)

Regards
Soren

[csathys (Dairy Land)]

Have you attempted using LINQ to solve this? Will be much faster.

[suraj_gaikwad]

Hi,

Few sheets of final output how its look

In first photo only 2 because of in master sheet there status is no that specific columns Sebi Report Csv and sebi report Xml

@csathys
@SorenB

thanks

[csathys (Dairy Land)]

Main.xaml (20.3 KB)

Run this workflow “as-is”. it will produce an excel file called output.xlsx.

This demo focuses only on Master tab and Sebi Report CSV tab.

Data has been mimicked to closely match our requirement.

The 2 Data tables have been defined with some test data. See Build Data Table.

Output.xlsx file will contain following tabs as a result of processing the data in the 2 predefined tables.

1st tab is “master”. This tab will contain the data stored in master data table. This is created to show what data we are working with.

2nd tab is “SebiReportCSV”. This tab will contain the data stored in SebiReportCSV data table. Again, this table will show data sample that we created.

3rd tab is “Filtered Master”. This will contain unique clientCode found in the master. This is done by getting unique clientcode from master.

Remaining tabs are created dynamically depending on unique clientcode found in “Filtered Master” tab.

Logic:

for each unique client code in master table

master.AsEnumerable.Where(Function(x) x(“SebiReportCSV”).ToString.ToUpper.StartsWith(“Y”)).CopyToDataTable.DefaultView.ToTable(true, {“ClientCode”})

1. Filter sebiReportCSV data table for client code = current client code from master and status beginning with “P” (for pending)
2. Copy the results of SebiReportCSV to a temporary data table defined with structure “ClientCode”, “Report Format” and “Status”
3. Store results into dt_output
4. Write dt_output into a tab with name formatted as “Sebi_” + clientCode

The LINQ query used here is:

dt_output = sebiReportCSV.AsEnumerable.where(Function(x) x(“Status”).ToString.ToUpper.StartsWith(“P”) And x(“ClientCode”).ToString= CurrentRow(“ClientCode”).ToString).CopyToDataTable.DefaultView.ToTable(False, {“ClientCode”, “Report Format”,“Status”})

Things you’d have to do to fine tune :

A) Populate data table in step 2 with data from excel tab to be read.
B) Check for no rows found situation during the filter and handle it.
C) Add any columns to the structure defined in step 2 to be part of your output

Hope this helps.