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

@nibir08
thanks for detailed case description.

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

Variables:
grafik

Flow:
Preperation result DataTable and grouping the data by firtst Column values
grafik

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:
grafik

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.