Excel to remove duplicates one column at a time for many columns -

-

i have excel workbook many sheets(40+) have many columns in each(30+).

my goal remove duplicates in each column not based on other columns. repeat columns in sheets.

i tried create macro upon execution macro select column had selected when created macro.

this code remove duplicates each column in workbook - treating each column separate entity.

sub removedups()      dim wrksht worksheet     dim llastcol long     dim llastrow long     dim long      'work through each sheet in workbook.     each wrksht in thisworkbook.worksheets          'find last column on sheet.         llastcol = lastcell(wrksht).column          'work through each column on sheet.         = 1 llastcol              'find last row each column.             llastrow = lastcell(wrksht, i).row              'remove duplicates.             wrksht                 .range(.cells(1, i), .cells(llastrow, i)).removeduplicates columns:=1, header:=xlno             end         next      next wrksht  end sub  'this function return reference last cell in either sheet, or specified column on sheet. public function lastcell(wrksht worksheet, optional col long = 0) range      dim llastcol long, llastrow long      on error resume next      wrksht         if col = 0             llastcol = .cells.find("*", , , , xlbycolumns, xlprevious).column             llastrow = .cells.find("*", , , , xlbyrows, xlprevious).row         else             llastcol = .cells.find("*", , , , xlbycolumns, xlprevious).column             llastrow = .columns(col).find("*", , , , xlbycolumns, xlprevious).row         end if          if llastcol = 0 llastcol = 1         if llastrow = 0 llastrow = 1          set lastcell = wrksht.cells(llastrow, llastcol)     end     on error goto 0  end function

as joshua has said - removeduplicates won't work in earlier version. providing have 2 spare columns @ end of each sheet, version work on excel 2003. takes advantage of advanced filter copy unique values end column, clears original column , pastes data again.

sub removedups()      dim wrksht worksheet     dim llastcol long     dim llastrow long     dim long      'work through each sheet in workbook.     each wrksht in thisworkbook.worksheets              'find last column on sheet.             llastcol = lastcell(wrksht).column              'work through each column on sheet.             = 1 llastcol                  'find last row each column.                 llastrow = lastcell(wrksht, i).row                  'only continue if there's more 1 row of data.                 if llastrow > 1                     wrksht                         filtertounique .range(.cells(1, i), .cells(llastrow, i)), .cells(1, i)                     end                 end if             next     next wrksht  end sub  'this function return reference last cell in either sheet, or specified column on sheet. public function lastcell(wrksht worksheet, optional col long = 0) range      dim llastcol long, llastrow long      on error resume next      wrksht         if col = 0             llastcol = .cells.find("*", , , , xlbycolumns, xlprevious).column             llastrow = .cells.find("*", , , , xlbyrows, xlprevious).row         else             llastcol = .cells.find("*", , , , xlbycolumns, xlprevious).column             llastrow = .columns(col).find("*", , , , xlbycolumns, xlprevious).row         end if          if llastcol = 0 llastcol = 1         if llastrow = 0 llastrow = 1          set lastcell = wrksht.cells(llastrow, llastcol)     end     on error goto 0  end function  public sub filtertounique(rsourcerange range, rsourcetarget range)      dim rlastcell range     dim rnewrange range      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''     'find last cell , copy unique values last column + 2 '     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''     set rlastcell = lastcell(rsourcerange.parent)     rsourcerange.advancedfilter action:=xlfiltercopy, copytorange:=rlastcell.parent.cells(rsourcerange.row, rlastcell.column + 2), unique:=true      ''''''''''''''''''''''''''''''''''''''''     'get reference filtered data. '     ''''''''''''''''''''''''''''''''''''''''     set rlastcell = lastcell(rsourcerange.parent, rlastcell.column + 2)     rsourcerange.parent         set rnewrange = .range(.cells(rsourcerange.row, rlastcell.column), rlastcell)     end      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''     'clear column data going moved to. '     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''     rsourcerange.clearcontents      ''''''''''''''''''''''''''''''''''''''''''''''     'move filtered data new location. '     ''''''''''''''''''''''''''''''''''''''''''''''     rnewrange.cut destination:=rsourcetarget  end sub

Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more