Deleting Cell Value when condition is met

[Rali]

Hello All,

I have following set of test data. The cells A21 through A27 are duplicate values. Depending on the values in "Lead" and "Operative" column the program should be capable of deleting the duplicate value in column "Code". Example, The text in cell A21 shall be deleted because A1 has same text and value B21+C21 is less than the value B1+C1. The weight for letter "Y" is 1 and "N" is 0. If the calculated value is equal than any one text can be deleted.

I know the requirement is confusing but this is what I have to do for thousands of cells on a monthly basis. Any help is appreciated.

Thank you.

 

Test_Set.xlsx

[JLogan3o13]

@Rali welcome to the forum. Please be aware, this forum is dedicated to helping people write and improve their own scripts; it is not a place where you put in a request and someone writes it for you. I would suggest beginning with looking at _Excel_BookOpen() in the help file. The example shows you how to open your excel file for manipulation. You can then look at the help file for the _Excel_Range* functions to read through your columns and modify the data as needed. My suggestion would be to read through these sections, and try to write something that will do what you need. If you run into problems, post your script back here and we will do our best to assist

[benners]

This works with the file you posted, it may give you some ideas. Checked after by finding dupes in Excel

#include <Excel.au3>
#include <Array.au3>

Local $o_Excel = _Excel_Open(False)
Local $o_Workbook = _Excel_BookOpen($o_Excel, @ScriptDir & '\Test_Set.xlsx')
Local $as_RangeRead = _Excel_RangeRead($o_Workbook)
Local $ai_FindAll = 0

For $i = UBound($as_RangeRead) - 1 To 1 Step -1
    $ai_FindAll = _ArrayFindAll($as_RangeRead, $as_RangeRead[$i][0], 1)
    If UBound($ai_FindAll) = 1 Then ContinueLoop

    For $j = 0 To UBound($ai_FindAll) - 1
        If $ai_FindAll[$j] = $i Then ContinueLoop

        If LetterToNumber($as_RangeRead[$i][1], $as_RangeRead[$i][2]) <= LetterToNumber($as_RangeRead[$ai_FindAll[$j]][1], $as_RangeRead[$ai_FindAll[$j]][2]) Then
            _Excel_RangeDelete($o_Workbook.ActiveSheet, 'A' & $i + 1 & ':C' & $i + 1, Default, 1)
            If @error Then MsgBox(0, 'Delete Error', @error)
            ExitLoop
        EndIf
    Next
Next

_Excel_Close($o_Excel)

; credit to jguinch for function I modded 
; (https://www.autoitscript.com/forum/topic/182862-convert-letter-to-muber/?do=findComment&comment=1313198)
Func LetterToNumber($s_Lead, $s_Operative)
    Return (StringRegExp($s_Lead, "(?i)^[a-z]$") ? Number(StringToBinary(StringLower($s_Lead))) - 96 : 0) _
    + (StringRegExp($s_Operative, "(?i)^[a-z]$") ? Number(StringToBinary(StringLower($s_Operative))) - 96 : 0)
EndFunc   ;==>LetterToNumber

 

[JLogan3o13]

3 hours ago, JLogan3o13 said:

this forum is dedicated to helping people write and improve their own scripts; it is not a place where you put in a request and someone writes it for you.

Or just wait until someone does it all for you