excel - Delete duplicates where the value of another column in the same row = 0 -


i have worksheet ~30k rows of data , 20 columns. lot of values in column b duplicates, , delete duplicates. problem have though column b may duplicate, row's data in rest of columns not duplicate, it's important delete row if value in column b duplicate , value in column m = 0.

is there way do each row in worksheet (the worksheet name test)? imagine involves "for each...", don't have lot of experience , haven't been able find other macros can complete task. let me know if need me elaborate on trying accomplish.

thanks help!

here's simple way in vba, queries spreadsheet table. you'll have adapt runquery() method particular case, because don't know column names.

this assumes following:

  1. your worksheet laid out table, column names in row 1, , data below it
  2. you've saved workbook before running code

in particular workbook, have column labeled "category", 1 labeled "types", , i've added column called "deleteme".

'adapt runquery particular needs sub runquery()     'change sql statement reflect particular need     sql = "select [category], [types] %table(0)% group [category], [types] having count(*) > 1"      'change "sheet2" sheet name     set rs = querysheet(sql, sheet2)      while not rs.eof         sql = "update %table(0)% set [deleteme]='x' [category]='" & rs!category & "' , [types]='" & rs!types & "'"         'again, change sheet2 sheet name         querysheet sql, sheet2         rs.movenext     loop end sub  'you don't need change function function querysheet(ssql, paramarray worksheets())     dim osh     dim sconn, n      on error goto querysheet_err     set querysheet = createobject("adodb.recordset")     sconn = "provider=microsoft.ace.oledb.12.0;data source=" & worksheets(0).parent.fullname & ";extended properties=""excel 8.0;hdr=yes"";"      n = 0     each osh in worksheets         ssql = replace(ssql, "%table(" & n & ")%", "[" & osh.name & "$]")         n = n + 1     next     querysheet.open ssql, sconn     exit function  querysheet_err:     msgbox "error in querysheet(): " & err.description     end end function

source

the code updates "deleteme" column x. now, can sort "deleteme", , delete rows (or write simple vba statement that).

your runquery() method need more this, replacing "column b" , "column m" names of fields in sheet's header row:

    ...      'change sql statement reflect particular need     sql = "select [column b] %table(0)% [column m] = 0 group [column b] having count(*) > 1"      'change "sheet2" sheet name     set rs = querysheet(sql, sheet2)      while not rs.eof         sql = "update %table(0)% set [deleteme]='x' " & _            " " & _            iif(isnull(rs![column b]), _                " [column b] null", _                " [column b]='" & rs![column b] & "' ") & _            " , [column m]=0"          'again, change sheet2 sheet name         querysheet sql, sheet2      ...

note: debug errors when change names of worksheets "sheet1" else... haven't worked issue out yet. works "quick n' dirty" queries.


Comments

Post a Comment

Popular posts from this blog

1111. appearing after print sequence - php -

morning, i have code takes string of shoe sizes, explodes , puts in table. code works , info displayed correctly on page after table there few 1's after table. i'm confused this. so table looks (in columns of 3 on webpage) available in following sizes: 3 4 5 6 9 10 111 here's codes, purpose of example $x = 3|4|6|10|9 function get_sizes_pipe($x) { $counter = 0; $splits = explode("|",$x); asort($splits); $x = print "<table class='greentable'>"; $x .= print "<thead><tr><th scope='col' colspan='3' abbr='starter'>available in following sizes:</th></tr></thead><tbody><tr>"; foreach ($splits $split) { $entry = print "<td>".$split."</td>"; if($counter == 2){ $entry .= print "</tr><tr>"; $counter =0; } else { $counter++; ...
Read more

java - WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/board/] in DispatcherServlet with name 'appServlet' -

this question has answer here: why spring mvc respond 404 , report “no mapping found http request uri […] in dispatcherservlet”? 3 answers i trying making simple bbs i'm encountering following error, warn : org.springframework.web.servlet.pagenotfound - no mapping found http request uri [/board/] in dispatcherservlet name 'appservlet' i have tried solve error many times, have failed. how can solve error? here web.xml, controllerand , servlet-context. my web.xml <?xml version="1.0" encoding="utf-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <filter> <filter-nam...
Read more

Ruby on Rails, ActiveRecord, Postgres, UTF-8 and ASCII-8BIT encodings -

weirdest error, who’s challenge try , me? spent hours yesterday on , it’s magic. can't believe happening , it's driving me crazy. btw, using: rails 4.2.3 ruby 2.2.3 pg 0.18 encoding.default_internal = encoding.default_external = utf-8 config.encoding = 'utf-8' it has postgres, activerecord , encodings! turns out we’ve been getting these errors whenever create new users on database special characters: encoding::undefinedconversionerror: "\xc3" ascii-8bit utf-8 \xc3 may vary depending on characters. weird part we’ve set since beginning utf-8. makes no sense , digged it, , used script: user.all.each |user| user.attributes.each |name, value| if value.is_a? string puts user.email + name.encoding.to_s + value.encoding.to_s end end end and output of 1 of users, users same: dr_lottahelp@blahblah.comutf-8utf-8 dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8asci...
Read more