excel - How to compare 2 complex spreadsheets running in parallel for consistency with each other? -

-

i working on converting large number of spreadsheets use new 3rd party data access library (converting third party library #1 third party library #2). fyi: call udf (user defined function) placed in cell, , when refreshed, pulls data pivot table below formula. both libraries behave same , produce same output, except, small irregularites can arise, such additional field being shown in output pivot table using library #2, can affect formulas on sheet if data being read pivot table without using getpivotdata.

so have ~100 of these complicated (20+ worksheets per workbook) spreadsheets have convert, , run in parallel period of time, see if output using new data access library matches old library.

is there clever approach this, don't have spend large amount of time analyzing each sheet determine specific elements compare?

two rough ideas come mind:
1. create validator workbook has same # of worksheets, , worbook1!worksheet1!a1 - worbook2!worksheet3!a1 every possible cell on each sheet
2. equivalent of #1, traverse cells in 2 books using vba, , log cells not match.

i don't particularly either idea, can think of better this, maybe 3rd party utility buy?

sounds time serious fundamental redesign rather swapping data access libraries.
but, address question:
- don't think 3rd party utility exists.
- vba approach using variant arrays used-range each sheet reasonably easy & efficient long don't try traverse sheets cell-by-cell.


Comments

Post a Comment

Popular posts from this blog

linux - Mailx and Gmail nss config dir -

-
i trying send mail linux command line using mailx command. can send local domain no problem want set mail send gmail account receive mail sent gmail account. after configuring mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore } i error: resolving host smtp.gmail.com . . . done. connecting 74.125.25.109 . . . connected. missing "nss-config-dir" variable. "/home/username/dead.letter" 11/354 . . . message not sent. after looking "nss-config-dir" here , located certn.db , keyn.db files , added mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore set nss-config-dir=/home/user/.mozilla/firefox/location.default } now when try send mail using command: echo "sent gmail account" | mailx -v -a
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more