Extract rows from Datatable where Column has same values

[dcharlton]

Hi,

I’m looking to extract rows from a datatable where a specific column has the same value.

I have a datatable where i want to extract all rows were the value in “Column 1” is the same. So for the below example, there are 3 unique values in “Column 1”, meaning there will be 3 different extracts. These different extracts are to be populated into seperate excel sheets.

What is the best way to achieve this?

Thanks

[1996 (Neelima G)]

Hi @dcharlton,

As per my understanding you want to extract 3 different values in Column 1 and write into the separate excel sheet. so,try this below code:

1. First use Read Range activity to read the data from excel file and will give you output as DataTable and say ‘inputDT’.

2. And then try below expression using Assign activity

     newDT = inputDT.DefaultView.ToTable(True,"column1")

Or

Another method,Using an Assign activity by using linq.

(From roww In DT.AsEnumerable() Group roww By id=roww.Item("Column1") into gg = Group Select gg(0)).copytodatatable

3. Finally use Write Range activity to write into Excel file and pass that newDT to it.

Hope this helps,If it works marks as Solution.

Thanks,
Neelima.

[ppr (Peter Preuss)]

find starter help here showcasing some different retrieval cases:
FindDupsUniquesFirstFromGroup_By1Col.xaml (8.0 KB)

[dcharlton]

Hi,

Thanks both for your help. Unfortunately this isn’t quite what I’m looking for.

What i need is to read in an excel table like below.

From this then, it will loop through Column 1 where values are the same and extract to a new excel.
So in this example, 3 separate excel spreadsheets will be created that will look something like this.

Appreciate the help.

[ppr (Peter Preuss)]

@dcharlton
it is better to mention all requirments and a clear described output on the beginning. This allows us to adress more individually the case and a solution can be faster provided.

Have a look here as it is close to your case:

Filtering based the list and after filtering copy the result to a new sheet using the filtered condition

@prasath17 find starter help here: MainV2.xaml (9.1 KB) [grafik] After readin the excel we can iterate over a on the fly constructed datatabel with the distinct state names inside the for each: log the state name which is processed filter the data for the current state which is looped write the result to a worksheet with the worksheet name same as the state for the distincts: InputDT.DefaultView.ToTable(True,“State”) true - remove duplicates, State - column to keep Let us know your fe…

[ppr (Peter Preuss)]

@dcharlton
also have a look here on this starter help:
SplitDupGroups_1Col.xaml (8.4 KB)

[dcharlton]

Thanks for your help @ppr, will be able to get what i need from this.
Appreciate the help.