Sort and remove lines in Excel

Help
1

I need to sort and remove duplicates and emtpy lines. How would i do that best?
I want to remove the blank lines, and dublicates, well all i want left is
“8842AGNCMVWZ6I”
“8581AGSMVZ6T”
nothing else.

image
image1637×723 39.9 KB

1 Like
2

Dim distinctDT As DataTable = myDT.DefaultView.ToTable(True, “Product”)
and after you can use sort data table

3

Hi
once after getting the datatable named
—we can first use REMOVE DUPLICATE ROWS activity and get the output named dt to remove the duplicate rows

—then use a assign activity like this
To remove blank rows and order them

dt = dt.Select(“[Product] <> ‘’ “).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

This dt will
Have the records we require

Cheers @Doktorgud

1 Like
4

Hi, thanks for great support.
I cant get it to work tho, im doing something wrong/missing something.

image
image1416×586 39.6 KB

1 Like
5

Hi
If possible can I see the expression mentioned and error you got

Cheers @Doktorgud

6

image
image1635×477 44.8 KB

could that be the problem? (friday i could be to tierd)

7

image
image842×442 16.5 KB

1 Like
8

Yah that’s the issue it’s actually single quotes with no value (represents null Value)
[Product] <> ‘’
Like this
dt.Select(“[Product] <> ‘’ “).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

Cheers @Doktorgud

9

this will help you to remove duplicates from your datatable dataTable new_dt =old_dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“columnWithDuplic”)).Select(Function(g) g.First).CopyToDataTable

to remove empty cells you can do this
array_dataRow = DataTableVar.Select("Prouducts = “” ")
for each loop inside loop use invoke method to Remove to delete rows
cheers! @Doktorgud

10

So what would the best way to get arround the “NULL” value?

11

Better mention mike this
dt.Select(“[Product] <> string.Empty“).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

Cheers @Doktorgud

12

Hello,
Im getting error…“Disallows implicit conversion of Boolean to string”…
Please guide

13

Its upset again :wink: maybe i should try something else, liked this tho, seemed like a good soulotion.

“Assign: Cannot find column [string.Empty]”

14

did you try my solution! @Doktorgud

15

On my way now, hold on :smiley:

17

Questions!

So assign? but under read range? and remove the rest?

new_dt =old_dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“columnWithDuplic”)).Select(Function(g) g.First).CopyToDataTable

image
image745×458 12 KB

18

why are you using duplicate rows Activity?
do this

  1. Read Range
  2. then remove duplicates use assign activity thats it
  3. and use remove rows with empty cell value cheers

@Doktorgud

19

I think i tried to many things and made a mess, trying this now, ill be back! (BIG THANK YOU to ALL of YOU)

20

i get this:
image
or
image

21

you need to change the column name bro!
instead of columnWithDuplic
go through the code