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-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (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 MiKa666
      Hi all,
      I'm developing a tool for exploratively testing another software. For that I wanna cover all the GUI functionality from that software and verify everything works as intended. But now I've a problem which I couldnt solve so far.
      One thing that software I'm testing does, is to load up either a single picture or a series of pictures. Depending on how much pictures got loaded, further actions will be proceeded...
      This loaded pictures are schon within a ToolbarWindow and they're clickable. What I need to know is, how much pictures are in this ToolbarWindow...
      Can anybody provide me a tip on how to achive this?
      Would be amazing!!
      BR,
      Mic
    • By meety
      Hello!
      wingettext function can get the text content of the IE browser page, but the text content of the page cannot be obtained in the chrome browser? What should I do?
    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By DirtyJohny
      Hi everyone.Need rewrite this function how in еxample.
      Original:
      #RequireAdmin #NoTrayIcon Opt("MustDeclareVars",1) Func _a() Local $sls=ObjGet("winmgmts:{impersonationLevel=impersonate," _ &"authenticationLevel=Pkt}!\\"& _ @ComputerName&'\root\wmi'),$lss=$sls.ExecQuery _ ('SELECT * FROM WmiMonitorID'), _ $lll,$sll,$sss="",$lsl,$lls,$i,$z For $z In $lss $lsl=$z.UserFriendlyName For $i=0 To Ubound($lsl)-1 if ($lsl[$i]) Then $lll&=Chr($lsl[$i]) Next $lls=$z.SerialNumberID For $i=0 To Ubound($lls)-1 if ($lls[$i]) Then $sll&=Chr($lls[$i]) Next $sss&="Model"&@TAB&@TAB&": "&$lll&@CR&"Serial Number"&@TAB&": "&$sll&@CR&@CR $lll="" $sll="" Next MsgBox(262144,'Monitors '&$lss.Count,$sss&" "&@CR) $lss=Null $sls=Null EndFunc _a() Example:
      Func _InfoPC() Local $ObjService = ObjGet('winmgmts:{impersonationLevel = impersonate}!\\' & @ComputerName & '\root\cimv2') Local $ObjMB = $ObjService.ExecQuery('SELECT * FROM Win32_BaseBoard', 'WQL', 0x30) If IsObj($ObjService) Then For $objItemMB In $ObjMB $sInfo &= @TAB & 'Motherboard: ...... ' & $objItemMB.Product & @CRLF I'm beginner in this sphere and need  you all speak easy and simply because i'm Russian.Thanks)
×
×
  • Create New...