vba - How to compare a list of rows to another list of rows in Excel? -

-

i trying figure out if there differences between list of data another. in order row of data "match" row, row must have same values in corresponding column. rows not have in particular order. in particular, dealing parts list, there part numbers, descriptions, etc. trying figure out if rows of data different rows of data list.

i found compare 2 sheets using arrays, may have answer problem, having trouble figuring out how adapt code due inexperience in visual basic.

i able work single column of data, comparing 1 column of data 1 sheet another, cannot compare entire rows of data.

here example of want work:

                   sheet 1                                       sheet 2             column 1       column 2                      column 1        column 2   row 1         22a            33                            11              11   row 2         22a            33a                           22a             33   row 3         55            22b                            55              23b

the code in link tell not in sheet 1 in sheet 2 , vice versa. in example, code tell me sheet 1 row 2 , sheet 1 row 3 not in sheet 2, , sheet 2 row 1 , sheet 2 row 3 not in sheet 1 (sheet 1 row 1 , sheet 2 row 2 match).

if ok you, can without vba using following formula:

={if(iferror(match(a1&"|"&b1;sheet7!$a$1:$a$3&"|"&sheet7!$b$1:$b$3;0);-1)=-1;"unique";"")}

assuming each of tables start in a1 (so tables 3 entries span a1:b3), , entering formula c1 (and copying down), press ctrl+shift+enter when entering formula create array formula, show word "unique" in column c if pair in row on sheet not in of row-pairs on sheet 2.

you can use conditional formatting highlight unique rows, filter on tables include unique rows, or other way of doing need.

note 1: have entered numbers in sheet6 , sheet7 instead of 1 , 2. formula written above goes sheet6.

note 2: language use ; instead of , function separator, if yours use , need change that.

note 3: need expand ranges sheet7!$a$1:$a$3 , sheet7!$b$1:$b$3 if set grows (this happen automatically if new rows inserted in between old ones). best still create named ranges each of 4 columns, exchange references those, , manage named ranges instead of formulas.

note 4: if data set contains character "|", need change well, match character sure not have there.

alternatively in column c on each cheet enter (assuming first entry in c1)

=a1&"|"&b1"

and copy down, run solution copied example using c column instead of on a1 , b1.


Comments

Post a Comment

Popular posts from this blog

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops', ...
Read more

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null ...
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens ...
Read more