I have a huge Excel file with over 60 k lines and 20 columns (A:T).
I strive to delete all match numbers where column T (“2.Flag”) is “Y”.
But it is very important that the same match numbers in other lines are also deleted.
First it should identify all match numbers with a “Y”. In the second step all identified match numbers have to be deleted. There are sometimes duplicate match numbers. So it is very important that these duplicates will also be delted.
Read the excel data into a datatable variable name dt.
Create a array variable of type string.variable name arrStr
On assign activity, assign array variable on left side and the query on right side
(From dtRow in dt.asenumerable
Group dtRow by k= dtRow("2.Flag").tostring.trim into grp= Group
Where cstr(grp(0)("2.Flag").equals("Y")
Select cstr(grp(0)("Match Number"))).toarray
And in write range give datatable as
(From dtRow on dt.asenumerable
Let mchNumber = dtRow("Match Number").tostring
Let flag = dtRow("2.Flag").tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals("Y"))
Select dtRow).copytodatatable
(From dtRow in dt.asenumerable
Group dtRow by k= dtRow("2.Flag").tostring.trim into grp= Group
Where cstr(grp(0)("2.Flag")).equals("Y")
Select cstr(grp(0)("Match Number"))).toarray
Hope this will help, typing in phone that’s why some syntax errors.
(From dtRow on dt.asenumerable
Let mchNumber = dtRow(“Match Number”).tostring
Let flag = dtRow(“2.Flag”).tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals(“Y”))
Select dtRow).copytodatatable
(From dtRow in dt.asenumerable
Let mchNumber = dtRow("Match Number").tostring
Let flag = dtRow("2.Flag").tostring
Where not (arrStr.any(function (item) item.tostring.trim.equals(mchNumber)) or flag.equals("Y"))
Select dtRow).copytodatatable
Changed on with in in first line
make sure the arrStr is the array we have assigned earlier
There is just 1 problem in duplicate match numbers.
If the lines with the flag “N” are red before the lines “Y” then only lines with “Y” are deleted (see red lines).