Compare both sheet in excel and filter the sheet

Hi,

I need this solution urgently please.

I have 2 sheets in excel.
In the first sheet,there is a Sales order and attribute name & value
image

In the second sheet, there are multiple attributes name & value but only one attributes is matching with the attributes of 1st sheet. That is row number 3.

I have enter for each row and if activity and filtered option but its not working.

"Override is datetable of 1st Sheet and GCSR1 is datatable of 2nd sheet.
In the filter wizard I keep only Type column with “GCSR” and in the write range activity BOT will write after filter in the 3rd sheet.
it will be like this:
image

In the Filter wizard I have written like this:
image

  • Note: After run in Debug mode it is giving me all Green Tick but data not filtering and not written in Sheet 3.

**

But it is not working. Please help me.

BR/Deb

Hi @Asitabha_Deb_asitabhad ,

Based on the screenshot you have attached.

Can you please drop the write range outside the loop and try it?

Thanks,
Ashwin

Instead of using the write range activity in the for each row. Switch that to an Append Range. This will add the new values to the end of the 3rd sheet. The write range activity is rewriting over the previous data each time the loop is executed.

Hope this helps.

Hi Thomas,

After add Append Range activity nothing is written in Sheet 3.
No luck.

BR/Deb

@Asitabha_Deb_asitabhad

Perhaps it might be best to start with some light debugging. Could you add breakpoints to your filter data table, and then another one on your write range? Then run in debug and, using the watch panel, see what GCSR4 is as it goes through your workflow, specifically at these spots. You may also want to watch both of your conditional values (row.item(“column3”).tostring). I’m slightly curious if those are simply false and going to else, thus no data in dt.

HI BenZee,

You can see I have put BreakPoint and checked.
Everything seems perfect but no luck in Sheet3.

So, when it breaks at the filter activity, you can see the correct values for GCSR4? What about when it reaches the append range? If you break there, does GCSR4 still have the same values?

I have declared GCSR4 as a output filter in Filter Wizard as you can see.
I have added Write Range Activity now and able to see only Header no more filtered data.

Did I miss anything in Filter Wizard?
image

Hi @Asitabha_Deb_asitabhad

The issue is at Filter data table inside your 2nd for loop. The thing is you’re trying to filter the datatable each and every time when the if condition is getting satisfied. and Each time when the IF condition is getting satisfied you’re filtering the fresh GCR1 table and storing it from scratch into GCR4 because of of which are getting nothing.
Solution : instead of filtering the GCR1 you create a datatable on top of 1st for loop with the schema you want and then use ADD DATA ROW activity to the created datatable inside the IF block.
Refer the below picture for the error pointing.


let me know if you are still unable to resolve your issue.
:champagne:

HI Satish,

Can you please tell me in details how to do that.
I am bit new in Uipath.
As per your last comment , what exactly needs to do.
Please help.

@Asitabha_Deb_asitabhad

When debugging, do you ever see it actually execute the “then” in both of those “ifs”? Or does it go to the else? Its possible that is constantly false, and therefor no data is being filtered and added to the GCSR4 DT. Could you verify that it actually does show true for both of those ifs?

Hi,

Thats also I am thinking.
In this case, if its not working what is the solution.
How I would compare both the sheet.?

We can try to debug this and figure out what needs to be done, if you’d like. To start, I notice that you are using a nested for each loop. Both loops are using “row” as the variable to iterate through. Lets try renaming one of those to currentRow. That will allow us to more easily see what may be happening with each loop. I’ll attach a screenshot to illustrate. Also, if you can add a few breakpoints, as shown in screenshot, and debug. Once in debug, open “Watch” panel and add “row.item(“Column3”).ToString” and “currentRow.item(“Column3”).ToString”. Once you hit the breakpoint on the if, see what the values are for each of those in the watch panel. If those values never match, its likely not comparing what you intend.

HI,

its returning nothing I guess.

Alright! If row is iterating through your Override DT, can we double check that dt to make sure the right column is being referenced and all data exists? You can simply add Override to that watch list. Then, once it gets to the same break point, click on the magnifying glass icon next to override. This should show you the Datatable and all rows in it. Are the headers as you expect? Is the data all present as expected?

Yes,

Both the date table giving me the correct data. Below is from Override dt:
image

Below is from GCSR1 dt.
image

Now, I guess both the below expression are not giving me correct result.
row.Item(“Column3”).ToString = row.Item(“Column3”).ToString
row.Item(“Column4”).ToString = row.Item(“Column4”).ToString

Okay. So from what I can see, Column3 has no value in it. If you look at the output for the OverrideDT, you can see that Sales_Document has a value of “607975297”, Sales_Document_Item has a value of “110”, Name has a value of “Disposition Level”, and Value has a value of “Tier 2”. This all makes sense from your prior screenshot. However, if you notice, Column1, Column2, and Column3 are all empty values (that is what the “,” is showing, between each , is separating a value for a column). Which value are you attempting to reference? “Disposition Level”?

Remember that one of your iterators is likely now currentRow. So, to test if that is going to be true, it should be currentRow.Item(“Column3”).ToString = row.Item(“Column3”).ToString and currentRow.Item(“Column4”).ToString = row.Item(“Column4”).ToString.

Yes,

Based on the value in Override Dt, BOT will filter GCSR1 dt.
In this case it will be like this:
image

Let me try the iterators you have mentioned.

This is going to be a bit of a guess, but I’m hoping I get this closer at least.

Lets try this for your if statements:

row.Item(“Name”).ToString = currentRow.Item(“Column2”).ToString
row.Item(“Value”).ToString = currentRow.Item(“Column5”).ToString

This still likely won’t work correctly as Disposition Level does not equal DISPOSITION_LEVEL and Tier 2 does not equal TIER 2 (this latter issue could be solved with something like tolower). However, this should help to narrow down the correct columns to reference… hopefully.

Date in Override Dt will be enter by user and data in GCSR1 dt will downloaded from SAP.
So, its wont be a problem to write exact type as SAP.

Below iterators giving me this result:
image

Now, I am trying these above one.