Macro to compare multiple test cases - example attached

[jaryszek]

Hi Guys,

I have to write difficult macro (because it has to be the fastest macro) to copy/paste data and compare it with expected results.
2 goals:
1. Populate data from table tblInput to yellow fields in "Sheet to Check 1"
2. Compare calculated fields in sheet "Result"

So I have 4 sheets:
1) Sheet to Check 1 - here i have to input data into yellow fields from table tblInput from sheet Input.

I started to doing this:

Please Login or Register  to view this content.

- I added one more artificial "Column1" to table tblInput to with easily transpose it to sheet "Sheet to Check 1"
- I am trying to resize my dictionary to "Sheet to Check 1" yellow fields but only 2 items are transposed, why ?
- maybe there is better way to do it? Use another objects than Dictionary? (and avoid copy and paste which is very slow).

Ok so this is for the first part of macro.

2) Input - Here we have table tblInput
3) Output - table tblOutput
4) Result - expected results when comparing green fields with table tblOutput.

And second part:
All green fields has to be compared with results from tblOutput in sheet "Result".
I created this kind of layout but i am open to your ideas.

How to create the best comparison solution here?
IF looping within arrays one by one and compare values will be ok here? Or maybe i should use other objects? (arrayList ?)

And how to get expected layout format?

Please help,
Best,
Jacek

[jaryszek]

Hi Guys,

i saw that anybody has not any ideas for this.

I did first part of macro, including comparison.

Please Login or Register  to view this content.

This is comparing string as hash codes and should be the fastest way for large files.
This is ok.
Feel free to ask your thoughts, ideas for it here.

Now second part,
Results should be like in sheet expected result:

Screenshot_5.png

So how to copy all rows from output sheet to result sheet and mark this with differences?

Best,
Jacek

[jaryszek]

Ok problem with the first function is how to find out which value is not equal and is different.

How can i catch it?

Best,
Jacek

[jaryszek]

Maybe you @jindon can help?

I changed code and now i am retrieving also arrays from RangeCompare function:

Please Login or Register  to view this content.

and now my idea is to find which row has differences and pass it to dictionary (which can be easily outputted later).

Please Login or Register  to view this content.

I am thinking because i am looping once again here (previously in function) , can i use dictioanry or other objects or something like this to avoid this?
Some unique key?

Best,
Jacek

[jindon]

I just don't understand what you are trying to do...

[jaryszek]

Hi jindon,

please ask.

In Simle way -
1) i want to copy and paste from each record (unique Order column name) from table from sheet tblInput into yellow fields into sheet "Sheet to Check1" and do not use copy and paste methods - they are slow. (each record tblInput --> copy and paste into yellow fields into "Sheet to Check 1").
2) And each green field (caculated based on yellow fields in "sheet to check 1") i want to compare with sheet and table Output and show results like in the screen.

So i am taking Order = 1 row in tblInput, copying it and pasting to yellow fields "sheet to check 1", based on that green fields are caclulated.
And now i have to compare green fields with tblOutput with the same Order = 1 and show differences...
Expected result is Output table but with differences shown.

Please help,
Best,
Jacek

[jindon]

Not really sure...

Please Login or Register  to view this content.

[jaryszek]

very nice solution, thank you very much!

I will test it for my purposes.

Best,
Jacek