How to remove row when cell is empty or specific cell value in excel

Help
,
1

I have to remove entire row based on some condition (below):
1.Cell in Credit column is empty
2.Transaction Description 1 column containing “SWEEP TRANSACTION”
3.Transaction Description 2 column containing “Amazing” as partial cell value

i have attached the excel file .“Sheet1” - input
and “Sheet2” - output.

Can someone please help me … I have tried different ways but didn’t get success in last 3-4 days.

Regards,
AhteshamSample data.xlsx (11.1 KB)

2

Hi @md.ahtesham

Please refer this link

Process rows in excel and write output

With bit modification you can achive your goal

Regards
Aditya

3 
1. Read range activity for excel Sheet1 and save as datatable named InputTable
2. In Assign activity: OutputTable = InputTable.clone
3. For each row in InputTable:
  a. row.Item("Credit").ToString.IsNullOrWhiteSpace AndAlso row.Item("Transaction Description 
      1").ToString.ToUpper like "*SWEEP TRANSACTION*" AndAlso row.Item("Transaction Description 
      2").ToString.ToUpper like "*AMAZING*"
     1.  Leave the true portion blank
     2. In the false section: Invoke Method (TargetObject = OutputTable; MethodName = ImportRow; Parameters are: Direction = In, Type = DataRow, Value = row)
4. Write range activity: Use OutputTable saving to Sheet2

Note that the above code in the IF statement assumes that you want all 3 conditions to be true to save in the output file. If ANY of the conditions can be true, then change the 2 AndAlso words to be OrElse instead.

1 Like
4

Thanks @Dave This one looks good let me try …

5

i went ahead and ran it myself, and it looks like you want to change the if statement to be “OrElse” instead of “AndAlso”. This way the output from the robot matches what you have in your uploaded example on Sheet2

6

@Dave I am getting below error.
Am i doing it correctly… ?
image

Thanks

7

@Dave If possible can you please share me xaml file.

Thanks
Ahtesham

8

Here you go.
md.ahtesham.zip (10.6 KB)

1 Like
9

You can fix that error by changing the first portion of the if statement to be System.String.IsNullOrWhiteSpace(row.item("Credit").ToString)

I believe the error is being encountered due to a namespace that needs to be imported - I’m not 100% sure which one though, so the above code can be used without any imported namespaces required

1 Like
10

Thanks a lot . It worked … I was struggling with this from last 3 days. Once again thanks a lot … And thank you for helping me solve the error too :slight_smile:

Regards,
Ahtesham

1 Like
11

Hey @md.ahtesham

You can also get the desire output by using Datatable select :slight_smile:

Datatable FilteredResult = YourDatatable.Select("[Transaction Description 1] = 'SWEEP TRANSACTION' and Convert([Credit],System.String)<>' ' and [Transaction Description 2] Like '%Amazing%'").CopyToDataTable

Note- Just would be better to Take Filtered Result in an DataRow[ ] then put and if condition to check Filtered count is greater then 0 then use CopyToDatatable() otherwise No record exists as per your condition

Regards…!!
Aksh

1 Like
12

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