Separate Grouped and Ungrouped rows in excel

Hello experts, I have array of rows which I want to separate the grouped rows from the ungrouped rows in Debit/Credit column, here is the screenshot and from here I want to separate the single Credits from the grouped rows. image


Share your required output

If you want to only keep the C records only, then use Filter Datatable

Hope this may help you


I want the single C’s to separed from the grouped as seen from the excel there must single C’s not grouped together.So all those single C’s will be separeted from the grouped, here is the required output - image

Has a potential to do it with LINQ or
we can do within a for each and using the index output for checking the row before and after

Let us know if you need additional guidance

Please can you share the Linq code

Please assist with the Linq logic to enable me close the topic


Find Quick Prototype


Flow with defensive handling empty filter result:

(From t In dtData.AsEnumerable.Select(Function (x,i) Tuple.Create(i,x))
Let d = t.Item2
Where d("Debit/Credit").toString.Trim.Equals("C")
Let fchk = (t.Item1 < 0)
Let lchk =  (t.Item1 < dtData.Rows.Count - 1 )
Let brc = If(fchk, String.IsNullOrWhiteSpace(dtData.Rows(t.Item1 - 1)(2).ToString.Trim), True)
Let arc = If(lchk, String.IsNullOrWhiteSpace(dtData.Rows(t.Item1 + 1)(2).ToString.Trim), True)
Where {brc,arc}.All(Function (x) x)
Select d).toList

Tested with data stressing also on the borders:

Find starter help here:
FindRows_ByBlankBeforeAfter_WhereIsKeyWord.xaml (9.4 KB)

Yes, that’s the result ppr. Further, I would like to remove the rows i.e the Result variable from dtData and then append it to the end of the excel sheet. How do I convert List(dataRows) to datarows so that I use it in the dtData dataTable and achieve that. Thanks


not sure if the question is understood.

CopyToDataMethod throws an exception if the List is empty. Therefore we check the count. The implementation is described above in the screenshot

Oh, sure, I want to remove those rows from result, so I added remove data row but how do I convert resullt list of data row to data row so I use it in remove data rowimage

Check the XAML as well, for the code statements

In case of from origin dtData the isolated C Rows (dtResult rows) should be removed then keep in mind the existing empty seperator rows maybe also are to handle. So its better to define the clear and complete requirements in advance. It looks like a case changer, but should be possible to get it implemented.

Thanks @ppr, that’s great

Please hint on this as well so we learn, cause I will also require to remove the isolated C from dtData as well

Also mentioned the requirements / handling seperator rows were asked. Please avoid ping pongs by letting answer unanswered. Thanks for Support.

Also have alook here:

Thanks @ppr

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