Rali

Deleting Cell Value when condition is met

4 posts in this topic

#1 ·  Posted

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

@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 :)


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#4 ·  Posted

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 ;)

1 person likes this

√-1 2^3 ∑ π, and it was delicious!

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