How can I delete rows of negative and positive cells that cancel each other out?

I’ve an excel sheet containing 2 columns. I want to delete those values which are same but contains negative values. I’m adding sample sheet and output.

Data1 and Data2 columns must be taken as single input hereproblem

thanks for detailed case description.

Group Values are the same Value and only different on positive or negative


Preperation result DataTable and grouping the data by firtst Column values

Processing the groups and adding remaining rows to Result Datatable

e.g. getting the posivite values:
(From g In grp
Let x = CInt(g(1).toString.Trim)
Where x > 0
Select r=g).toList

getting the common count:
{PosRows.Count, NegRows.Count }.Min()

Adding remaining rows to result datatable:
(From x In PosRows.Skip(CommonCount).Concat(NegRows.Skip(CommonCount))
Let ra = x.ItemArray
Select dtResult.Rows.Add(ra)).toList

Input / output:

find starter help here:
GroupBy_1Col_OffsetGrpMembers.xaml (11.9 KB)

For getting more familiar with LINQ have a look here:

1 Like

Thank you so much. It worked perfectly :smiley:

Thank you for your fast response and kind help :smiley:

One more thing. When I was conducting some tests on it, I found out that it’s not working properly. like for example if Data1: A,A,A Data2: 2,-1,1 the output should be A 2. But it’s giving me A 1. Can you please help me here?

with a change of the grouping Statement to

(From d In dtData.AsEnumerable
Group d By k1=d(0).toString.Trim, k2=Math.Abs(CInt(d(1).toString.Trim)) Into grp=Group
Select grp.toList).toList

input / output:

find starter help here
GroupBy_OffsetGrpMembersWithSameVal.xaml (12.4 KB)

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.