Remove number of row based on cell value

[mashy2]

Hi, for example Quantity of Order number from Excel A is 5 and Quantity of same Order number but blank row of excel B is 8.
How to remove only 3 row from excel B based on matched order no from Excel A and B.

Thanks so much!
ExampleReomoverow.xlsx (10.7 KB)

[mashy2]

@supermanPunch hi, can help on this? tq very much!!

[Yoichi]

Hi,

If I understand your requirement correctly, the following will work. Can you check this?

dict = dtInputName.AsEnumerable.ToDictionary(Function(r) r("OrderNo").ToString,Function(r) Int32.Parse(r("Quanitity").ToString))
dtResult = dtInput.AsEnumerable.GroupBy(Function(r) r("OrderNo").ToString).SelectMany(Function(g) g.Take(dict(g.Key))).CopyToDataTable

Sample20220401-7.zip (10.3 KB)

Regards,

[mashy2]

hi, sorry for late reply,
Thank your for the help,
Unfortunately the solution doesnt cover this type of input:

Assume:ET466245 quantity is 2.
My expected output should be like this:

Only the blank SID that need to be remove for matched order no

[Yoichi]

Hi,

Can you elaborate regarding difference between ET466244 case and ET466245case?

Regards,

[mashy2]

hi,

for ET466244, i only need to remove 3 blank rows cause total quantity is 5 but blanks rows is 8.
for ET466245, i need to remove 1 blank row cause total quantity is 2 but blanks rows is 3

[Yoichi]

Hi,

Thanks for sharing. Probably the following will work. Can you try this?

dtResult = dtInput.AsEnumerable.GroupBy(Function(r) r("OrderNo").ToString).SelectMany(Function(g) g.GroupBy(Function(r) r("SID").ToString).SelectMany(Function(g2) if(String.IsNullOrEmpty(g2.Key),g2.Take(dict(g.Key)),g2))).CopyToDataTable()

Sample20220401-7v2.zip (10.7 KB)

Regards,

[mashy2]

tq so much!