Forming Delete Queries Using Excel data

[Sundar43]

Hi All,

Greetings.!

I have excel sheet with 1 Lakh data. I need to form delete queries by using 2 columns.
For example,

[TABLE="width: 130"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]A2
[/TD]
[/TR]
[TR]
[TD]300
[/TD]
[TD]C1
[/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]B2
[/TD]
[/TR]
[TR]
[TD]300
[/TD]
[TD]C2
[/TD]
[/TR]
</tbody>[/TABLE]

Sample Query I formed manually is,

delete from table1 where Column1=100 and Column2 in(A,A2);
delete from table1 where Column1=200 and Column2 in(B1,B2);
delete from table1 where Column1=300 and Column2 in(C1,C2);
.
.
.
. so on..

Could you please advise is there anyway I can automate this by using excel features or macro ?
Since I have lakh data in excel it is killing my time to form the queries manually.
Help would be greatly appreciated..

Regards,
Sundar

 

[wigi]

Hello

Here's a very fast and efficient approach:

Sub Wigi()
    With Sheets(1).Cells(1).CurrentRegion.Resize(, 3)
        .Columns(1).Name = "MyRange_1"
        sq = Filter([transpose(if(MyRange_1<>"","|" & MyRange_1 & "|","~"))], "~", 0)
        .Columns(2).Name = "MyRange_2"

        Do While UBound(sq) <> -1
            v = Replace(sq(0), "|", "")
            vv = vv & vbCr & "@" & v & "@@" & Join(Filter(Application.Transpose(Evaluate("IF(MyRange_1=" & v & ",MyRange_2,""~"")")), "~", 0), ",") & ");"
            sq = Filter(sq, sq(0), False)
        Loop
        
        If Len(vv) Then
            sq = Application.Transpose(Split(Replace(Replace(Mid(vv, 2), "@@", " and Column2 in ("), "@", "delete from table1 where Column1="), vbCr))
            .Columns(3).EntireColumn.ClearContents
            .Columns(3).Resize(UBound(sq)) = sq
        End If
    End With
End Sub