CODEWITHSUNDEEP
c#excellinq
caraLinq
caraLinq

Reputation: 71

Get the row in an excel.range where the Value is the same

I am on a project in C # for excel and I try to recover the rows where the values ​​are identical :

so i get the row 1 and the row 2 I already tried that :

Excel.Range xl = (Excel.Range)worksheet.get_Range("Q2:Q" + lastRowOfTheSheet);
 var all = xl.Rows.Cast<Excel.Range>().Select(x => x.Value2.ToString()).ToList();
 var theRowsSameValue = xl.Rows.Cast<Excel.Range>().Where(y => y.Value2 != ?????????).Select(x => x.Row).ToList();

but i don't know what to put in the "Where". Someone have an idea ? Thanks

Upvotes: 1

Views: 1171

Answers (1)

Bijan Rafraf
Bijan Rafraf

Reputation: 383

The answer to this is going to be to group the rows, then check the count of each group then to 'ungroup' the rows again using the .SelectMany method. See below:

Excel.Range xl = (Excel.Range)worksheet.get_Range("Q2:Q" + lastRowOfTheSheet);
 var theRowsSameValue = xl.Rows.Cast<Excel.Range>().GroupBy(y => y.Value2).Where(y => y.Count()>1).SelectMany(x => x).ToList();

Reading the method calls from left to right we Group by the value, then we filter for groups with more than 1 record (values that appear more than once), then we flatten the list of groups back into a list of cells.

Upvotes: 2

Related Questions