Jump to content
Sign in to follow this  
nooneclose

[SOLVED] How to search through a column and delete certain names.

Recommended Posts

My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.

This is what I have so far.

Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill")
_ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)

 

Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

Just to make sure I understand your problem:

You have an Excel Workbook where you want to delete all rows where the value in column D doe not match the value in column C of the same row?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@water I was actually able to figure this one out on my own. But if you have a better way please share. 

As of now, this is what I use:

Local $NameToDelete1  = _Excel_RangeFind($OpenWorkbook, "Smith, Bob")
Local $Name           = $NameToDelete1[0][3]
Local $NameCell       = $NameToDelete1[0][2]
Local $CellNumber     = StringSplit($NameCell, "A, B, C, D, E")
Local $CellRange      = $NameCell & ":E" & $CellNumber[2]
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $CellRange, $xlShiftUp)

I have to manually make the code for every name, (that I know of) that should not be on the roster. I would greatly appreciate it if the program would search for me for any name in column D that is not found in column C and then delete that cell (upwards) which holds the name in column D and their time in column E. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

@IAMK Yes, it has to be done in AutoIt. That is the whole point of creating an automation script, to make the program do something that I do not want to.  Like I said earlier though I have already figured out how to accomplish this task plus I have refined the process since then. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Jahar
      I have an AutoIT script It monitors 2 websites for content that applys to me and the services that I provide. One site is : www.Freelancer.com The other: www.PeoplePerHour.com Both sites publish new jobs on their site hourly or so. My AutoIT app, will view those sites and present new jobs to me in a grid that pops up on my screen. Lately, the app has stopped showing me any jobs from PeoplePerHour.
       
      For freelancer.com,  Inetget is giving full html but for peopleperhour, now its not coming.
      Func _CheckPPH()
          Local Static $hTimer = 0
          Local Static $hDownload = 0
          Local $aTitlesandUrls = 0
          Local Static $sTempFile = ""
          If $hTimer = 0 Then $hTimer = TimerInit()
          If $hDownload = 0 Then
              $sTempFile = _WinAPI_GetTempFileName(@TempDir)
              ConsoleWrite("Checking PPH..." & @CRLF)
              ConsoleWrite(">Downloading..." & @CRLF)
      ;~         $hDownload = InetGet("http://www.peopleperhour.com/freelance-jobs", $sTempFile, $INET_FORCERELOAD, $INET_DOWNLOADBACKGROUND)
              $hDownload = InetGet("http://www.peopleperhour.com/freelance-jobs", $sTempFile, $INET_FORCERELOAD)
      ;~         Return 0
          EndIf
      ;~     Sleep(30)
      ;~     Local $isCompleted = InetGetInfo($hDownload, $INET_DOWNLOADCOMPLETE)
      ;~     Local $isError = InetGetInfo($hDownload, $INET_DOWNLOADERROR)
      ;~     Sleep(30)
      ;~     If TimerDiff($hTimer) > 3000 And $isError Then
      ;~         ConsoleWrite("!PPH Fail" & @CRLF)
      ;~         InetClose($hDownload)
      ;~         $hDownload = 0
      ;~         Return 0
      ;~     EndIf
      ;~     Sleep(30)
          Local $Show = 0
      ;~     If TimerDiff($hTimer) > 3000 And $isCompleted Then
          If $hDownload > 0 Then
              ConsoleWrite("+Downloaded..." & @CRLF)
              Local $sPPHHtml = FileRead($sTempFile)
              $aTitlesandUrls = _StringBetween($sPPHHtml, '"title">' & @LF, 'time>')
      ;~         _ArrayDisplay($aTitlesandUrls)
              Local $aPPH[0][4]
              Local $sTitle = ""
              Local $sUrl = ""
              Local $sID = ""
              Local $sDate = ""
              Local $iRet=0
              Sleep(30)
              For $i = 0 To UBound($aTitlesandUrls) - 1
                  $sTitle = _StringBetween($aTitlesandUrls[$i], '<a title="', '" class')
                  $sUrl = _StringBetween($aTitlesandUrls[$i], 'href="', '">')
                  $sDate = _GetDate($aTitlesandUrls[$i])
                  If IsArray($sTitle) And IsArray($sUrl) Then
                      $sID = _GetID($sUrl[0])
      ;~                 _ArrayAdd($aPPH, $sDate & "|" & $sTitle[0] & "|" & $sUrl[0] & "|" & $sID)
                      $iRet = _BuildPopupsPPH($sID, $sDate, "PPH: " & $sTitle[0], $sUrl[0])
                      If $iRet Then $Show+=1
                  EndIf
              Next
              Sleep(30)
      ;~         If $Show > 0 Then ShowLatestJobs()
      ;~         _ArrayDisplay($aPPH)
              FileDelete($sTempFile)
              InetClose($hDownload)
              $hDownload = 0
              $hTimer = 0
              Return $Show
          EndIf
          Sleep(30)
      EndFunc   ;==>_CheckPPH
    • By malicioussoap
      back when I was still doing autohotkey you could write (in code) spanisha::á
      and whenever you were in google docs or notepad you could type (in the document) spanisha  and it instantly turns it into á 
      I tried doing this in autoit, here's what I have so far (my entire code so if this language requires a main function or a return/exit to function properly please let me know):
      ;is this a proper comment? HotKeySet ( "spanisha" , "SendAltered" ) Func SendAltered() Send("á", flag = 0); EndFunc I assume that autoit follows a sort of contemporary hotkey pressing model where you have to hold all keys at the same time? which makes this impossible. So
      I'm wondering if anyone has a workaround for this? or maybe an alternative to HotKeySet()
      I have a vague memory of someone saying that doing something like spanisha:á would work. I don't know if I'm slightly off syntax but this doesn't work.
      Also I tried doing 
      ;is this a proper comment? ;HotKeySet ( "a" , "SendAltered" ) ;spanisha::á Func SendAltered() Send("á"); EndFunc just to try and debug,
      I'm also wondering if my approach is fundamentally wrong since HotKeySet() was made for something like !^A. Or if Send() is syntactically wrong.
      I'm also noticing on these forums that specifying installation is important so to whom it may concern I downloaded both installers, editor and main files. Although I'm about to be real mad if the editor installation makes a difference.
    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By nacerbaaziz
      goodmorning; autoit team
      please their are any youtube search way working?
      because i was using the get source and split it to get the result but know it does not working any way.
      is the youtube disabled that? and is their any other simple way to do that?
      i tested all examples found in this post but also it don't work
      https://www.autoitscript.com/forum/topic/123945-youtube-search/
      and here is the example that i use to and it don't work any more
      local $hSearchOpenHNDL, $hSearchConnect, $sSearchGet local $a_UrlsArray[1][5] local $b_ButtonsDisabled = false, $b_SearchBTNFocus = false, $b_SearchListFocus, $h_SearchFocusHND local $Return = "0" local $s_OpenStringY = "/feed/trending" local $ChannelUrl = "", $channelName = "" if Not ($a_YoutubeSearchArray[0][0] = 0) then GUICtrlSetData($searchInp, $s_youtubeSearchLastSearch) $s_OpenStringY = "/results?search_query=" & StringReplace(GUICtrlRead($searchInp), " ", "+") $a_UrlsArray = $a_YoutubeSearchArray for $i = 1 to $a_UrlsArray[0][0] _GUICtrlListBox_AddString($SearchList, $a_UrlsArray[$i][0] & $a_UrlsArray[$i][2] & $a_UrlsArray[$i][3]) next _GUICtrlListBox_SetCurSel($SearchList, $I_youtubeSearchLastIndex-1) GUICtrlSetState($SearchList, $GUI_focus) else if Ping("youtube.com", 1000) > 1 then $hSearchOpenHNDL = _WinHttpOpen('') if not (@Error) then $hSearchConnect = _WinHttpConnect($hSearchOpenHNDL, "youtube.com") if Not (@Error) then $sSearchGet = _WinHttpSimpleRequest($hSearchConnect, "get", $s_OpenStringY) if not (@Error) then local $a_strings = _StringBetween($sSearchGet, '<a href="/watch', "<ul") local $title = "" local $url = "" local $length = "" local $result = "" GUICtrlSetData($SearchList, "") ReDim $a_UrlsArray[1][5] for $i = 0 to UBound($a_strings)-1 $url = _StringBetween($a_strings[$i], "?", '"') if @error then ContinueLoop $url = "https://www.youtube.com/watch?" & $url[0] $title = _StringBetween($a_strings[$i], 'dir="', '</a>') if @error then ContinueLoop $title = $title[0] $title = StringRegExpReplace($title, '(.*\"\>)', "") if StringRegExp($a_strings[$i], '[0-9]{1,2}\:[0-9]{1,2}\:[0-9]{1,2}', 0) = 1 then $length = StringRegExp($a_strings[$i], '[0-9]{1,2}\:[0-9]{1,2}\:[0-9]{1,2}', 2) elseIf StringRegExp($a_strings[$i], '[0-9]{1,2}\:[0-9]{1,2}', 0) = 1 then $length = StringRegExp($a_strings[$i], '[0-9]{1,2}\:[0-9]{1,2}', 2) else $length = "" endIf if IsArray($length) then $length = ": (" & $length[0] & ")" else $length = "" endIf $ChannelUrl = stringRegexpReplace($a_strings[$i], '(^.*?<a.*?\"(\/user|\/channel))+', "$2") $channelName = stringRegexpReplace($ChannelUrl, '(.*?\".*?>)(.*</a>)+', "$2") $ChannelUrl = stringRegexpReplace($ChannelUrl, '(\".*)+', "") $channelName = stringRegexpReplace($channelName, '(</a>.*)+', "") $result &= $title & @crlf & $url & @crlf ReDim $a_UrlsArray[UBound($a_UrlsArray)+1][5] $a_UrlsArray[UBound($a_UrlsArray)-1][0] = $title $a_UrlsArray[UBound($a_UrlsArray)-1][1] = $url $a_UrlsArray[UBound($a_UrlsArray)-1][2] = $length if not ($channelName = "") then $a_UrlsArray[UBound($a_UrlsArray)-1][3] = ", (" & $channelName & ")" if not ($channelUrl = "") then $a_UrlsArray[UBound($a_UrlsArray)-1][4] = "https://www.youtube.com" & $channelUrl $a_UrlsArray[0][0] = UBound($a_UrlsArray)-1 _GUICtrlListBox_AddString($SearchList, $a_UrlsArray[UBound($a_UrlsArray)-1][0] & $length & $a_UrlsArray[UBound($a_UrlsArray)-1][3]) next endIf endIf endIf endIf endIf  
      i hope any one can help me
      thanks in advance
    • By shelly
      I have to press tab to reach a selected button then double click on that
      Here's my script but its not working:
      WinActivate("Policy Decisions -- Webpage Dialog","")
      Sleep(3000)
      Send("{TAB}")
      Send("{TAB}")
      Send("{TAB}")                    // at this point its working according to my need  but double click is not working
      Mouseclick("Left",510, 320, 2, 0)

×
×
  • Create New...