Creating an automated excel list with condition

Help
1

Hello all,

I have a excel file with 4 columns which are a result of vlookups. Below you can find some data which I am seperating with a semi-column.

Abc;finance;highstreet;#N/A
Der;N/A;highway;3000$
Poz;r&d;parklane;4000€

As you can see, the first two lines contain a cell which is ‘#N/A’. I would like to automate that all rows with an N/A record are listed in a seperate ‘issue file’ which can track data quality.

How should I approach this?

Thanks in advance!

2

In the for each row activity place an if condition and check if row(0).toString.Contains(“N/A”) or row(1).toString.Contains(“N/A”) or …for every row Item…
and in then place that row in another datatable and you can use it where ever you want!

1 Like
3

Hi @Niket_Ghai

Thank you for your reply.
However, can you be a little more precise? I don’t know how to :-). And how can I automate it to check all rows (as this file might have 30 rows but the next file 450 rows). would you care to share a xaml file?

Thanks!

4

Hi! @yannip here is a workflow:
DataTables.xaml (8.9 KB)

5

Hey @Niket_Ghai,

Thanks for the workflow. It’s getting in the good direction, but I still have 2 issues here:

1/ It is a good solution when you know the number of rows in advance. The bot should run several times per month and the number of rows can fluctuate.
2/ The message box in the 'then" part displays everything when 1 row contains “N/A”. I would like to copy only the N/A’s in a new excel file.

Do you have any suggestions?

Thank you!

6

This workflow will work for any number of rows…you will have to add condition only if number of columns changes!