Compare Values in Excel instead of using Collections

[LorenzoCapoccia]

Hi
I am currently using Collection stage to compare data ( already filtered from other collections) but when i do the final compare of the 2 collections, it removes the empty lines, and this causes to details to loose 1 row, and at the end there shows mismatch of values, even do it is not real....
I am trying to do this match directly from excel, basically in column B I have the name of the value A to compare against Value B ( already filtered from collections A to Z)... but  adding to excel it possible allow to keep the empty cell in the existing row location, so if the value from different cell has a value it will just say false ( no match), would you have an example of actions from RPA and code, as I am new to this and any help with be grateful... thank you so much in advance,, attached is the example

[ewilson]

Hi @LorenzoCapocci1,

How are you comparing the Collections now? Are you just going through, in a loop, and comparing row by row? If you do this in Excel you'll need to write each Collection to a column and then write the comparison function to the third column. Do you intend to continue on with the Excel spreadsheet afterwards, or are you going to do something else with the results? If you're intention is to do something else with the data (in other words the Excel spreadsheet is not the final resting place of the data) then using Excel might be overkill.

Cheers,
​

[LorenzoCapoccia]

Hello @ewilson , thank you for your time... ​I have uploaded the current view of this part of work currently filtered in collections, in the word document  with some details

below is the result I get after I do the final compare.. basically on the left I have 16 rows ( because the empty row was removed ) and since this was filtered a to z, the column onm the right has 17 rows, but after the id  in red, the 2 rows mismatch , not because they are not the same products but because HAQN230, is not in the left column


thank you for your time

If there is a way for this not to happen I would rather use collections than Excel ( of course if this can be done in excel)

[ewilson]

Hi @LorenzoCapocci1,

Apologies for the delayed response. If you wanted to do this in Excel you would have to write a comparison function to the 4th column. So you'd use the Set Cell Value action of the Excel VBO to write something like this to the cell:

=IF($B2=$C2,"TRUE", "FALSE")​

​
You'd have to be careful though as the row values would need to increment each time you write the formula to the next row.

Now, coming back around to the Collections. I assume from the screenshot that you're using the Utility - Collection Manipulation VBO from Blue Prism to perform your sorts and that's the point at which the empty rows are removed? If that's the case, what you could do is iterate over the collection before sorting it and insert a default value for empty cells (ex. <NULL>). That way there will be a value in the column and it should not be removed. Does that make sense?

Cheers,
Eric

[LorenzoCapoccia]

Hi @ewilson, thank you for the suggestion, I will work on the collection as per your note, as I don't want to make extra work:) , thank you so much for the time you spent checking my notes! I really appreciate it...​