Compare two data sets in different sheet and print difference in other sheet using excel VBA? -


i have 2 data sets in 2 different sheet sheet1 orginal ref , sheet2 comparison. sheet2 data should compared sheet1 , print entire mismatched row of sheet2 , highlight cells has mismatch data , difference should printed column header in other specified sheet , in specified range

also mismatch cell count should updated in sheet3 specified range cell

below tried code. appreciated.

sub comparedataset() call comparesheets("sheet1", "sheet2") end sub sub comparesheets(sheet1 string, sheet2 string) dim cell range dim cellmismatch integer each cell in activeworkbook.worksheets(sheet1).usedrange if not cell.value = activeworkbook.worksheets(sheet2).cells(cell.row, cell.column).value let worksheets("sheet3").cells(cell.row, cell.column) = cell cell.interior.color = vbyellow cellmismatch = cellmismatch + 1 end if next thisworkbook.sheets("sheet3").cells(1, 1).value = cellmismatch end sub

here code compare sheet1 , sheet2(corresponding cells ) , according wite correct value or mismatch based upon result sheet3. sheet1 , sheet2 wil have same number of rows , columns , headers same can keep them in sheet3. hope helps.

sub compare()  'clearing contents of third sheet fresh comparison  usedcoulms = sheets("sheet3").usedrange.columns.count usedrows = sheets("sheet3").usedrange.rows.count = 2 usedrows j = 1 usedcoulms    sheets("sheet3").cells(i, j).value = ""    sheets("sheet3").cells(i, j).interior.color = rgb(255, 255, 255) next next  'coulmn count of first sheet columncount = sheets("sheet1").usedrange.columns.count 'row count of first sheet rowcount = sheets("sheet1").usedrange.rows.count  = 2 rowcount j = 1 columncount     if sheets("sheet1").cells(i, j).value <> sheets("sheet2").cells(i, j).value    'comparing if values not equal         sheets("sheet3").cells(1, j).value = sheets("sheet1").cells(1, j).value 'copying header of mismatched cell         sheets("sheet3").cells(i, j).value = cstr("mismatch")   'if mismatch setting set value mismatch         sheets("sheet3").cells(i, j).interior.color = 65535     'highlighting yellow color      else         sheets("sheet3").cells(i, j).value = sheets("sheet1").cells(i, j).value         'if values same copy first sheets value if dont want copy can skip     end if next next  end sub

Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

c# - Exception when attempting to modify Dictionary -

i creating dictionary manage threads , ran exception when attempt clear dead threads after adding threads dictionary. using system; using system.collections.generic; using system.threading; namespace syscat { public class threadmanager { public static readonly threadmanager globalthreadmanager = new threadmanager( ); /// <summary> /// initializes new instance of <see cref="syscat.threadmanager"/> class. /// </summary> public threadmanager( ) { } /// <summary> /// create new threadmanager threads within basetm , deinitializes basetm /// </summary> public threadmanager( threadmanager basetm ) { this.threads = basetm.threads; basetm.threads.clear( ); } private dictionary<guid,thread> threads = new dictionary<guid, thread>( ); /// <summary> /// attempts obtain unused threadid within ...
Read more