looking for a VB Script

[navrichard]

Hi All,

I am looking for a VB Script where the column A and column B have data
if column b is has data (in a cell) then the subsequent cell in cell A shoul be deleted, if the cell in column b is "NULL" then the value in cell a should remain,
eg:

1(remove) = 1
2 (remain)= "NULL"
3 (remain)= "NULL"
4 (remove) = xyz

Step 2,
in cell C i have lots of duplicate entries say
1
1
1
2
2
2
3
3
3
here the duplicate values should be replaced by "DUPLI"
some thing like this
1= 1
1="DUPLI"
1="DUPLI"
2=2
2="DUPLI"
2="DUPLI"

i am no VB expert - but since i have to do this on a daily basis on a hudge sheet link it takes almost 3hrs to replace the duplicates and removing the data on the macro one,

PLEASe PLEASe can someone help me on this ....

 

[PCL]

Perhaps next code

Option Explicit
Sub Treat()
Dim LAST_ROW  As Long
Dim MyRG As Range
Dim I As Long
'---------  FOR  COLUMN  C   -----------
    LAST_ROW = Range("C" & Rows.Count).End(xlUp).Row
    For I = 1 To LAST_ROW
        Set MyRG = Range("C1:C" & I)
        If (Application.WorksheetFunction.CountIf(MyRG, Cells(I, "C")) > 1) Then Cells(I, "C") = "DUPLI"
    Next I
     
'---------  FOR  COLUMN  A   -----------
    LAST_ROW = Range("A" & Rows.Count).End(xlUp).Row
    For I = 1 To LAST_ROW
        If (Cells(I, "B") <> "NULL") Then Cells(I, "A").ClearContents
    Next I
End Sub

 

[jbeaucaire]

If these BEFORE/AFTER samples are correct, the macro below will do what you wish:

Excel Workbook
	A	B	C
1	1	1	1
2	2		1
3	3		1
4	4	xyz	2
5	5		2
6	6	2	2
7	7		3
8	8	ddd	3
9	9		3
BEFORE

Excel Workbook
	A	B	C
1		1	1
2	2		DUPLI
3	3		DUPLI
4		xyz	2
5	5		DUPLI
6		2	DUPLI
7	7		3
8		ddd	DUPLI
9	9		DUPLI
AFTER

Option Explicit

Sub EvaluateData()
'JBeaucaire  (9/5/2009)
Dim LR As Long, i As Long, Rng As Range

LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LR
        If Not Cells(i, "B") = vbNullString Or Not Cells(i, "B") = "NULL" Then _
            Cells(i, "A") = ""
        If i > 1 Then
            Set Rng = Range("C1", Cells(i, "C"))
            If WorksheetFunction.CountIf(Rng, Cells(i, "C")) > 1 _
                Then Cells(i, "C") = "DUPLI"
        End If
    Next i

End Sub

I wasn't sure if you meant column B was "empty" or really had the word "NULL" in it, so this works either way.

 

[Peter_SSs]

navrichard

Welcome to the MrExcel board!

If jbeaucaire's 'BEFORE' and 'AFTER' are what you are looking for, then you may wish to try the code below. It should be considerably faster as it does not involve looping through rows individually. However, if your dataset is not very big you may not notice the speed difference.

My code does assume that column D is available to use as a helper column. If that is not the case, the code can easily be adjusted.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> navrichard()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("D1:D" & Range("C" & Rows.Count).End(xlUp).Row)<br>        .Formula = "=IF(RC2="""",RC1,"""")"<br>        .Offset(, -3).Value = .Value<br>        .Cells(1, 1).Value = .Cells(1, 1).Offset(, -1).Value<br>        .Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = _<br>            "=IF(RC[-1]=R[-1]C[-1],""DUPLI"",RC[-1])"<br>        .Offset(, -1).Value = .Value<br>        .ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jbeaucaire]

That is faster. I originally used the non-loop procedure and abandoned it when I realized comparing C1 to C2 might seem correct but might not be. The duplicated values could possibly NOT be adjacent, so I opted for a loop and a comparision that took the whole column above each row into account.

Reading yours I now realize we still could have accomplished that "anywhere above" comparison without the loop.

Sub EvaluateData()
'JBeaucaire  (9/6/2009)
Dim LR As Long
Application.ScreenUpdating = False

LR = Range("C" & Rows.Count).End(xlUp).Row
    
    With Range("D1:D" & LR)
        .Formula = "=IF(OR(RC2="""",RC2=""NULL""),"""",RC2)"
        .Offset(, -3).Value = .Value
        .Cells(1, 1).Value = .Cells(1, 1).Offset(, -1).Value
        .Offset(1).Resize(.Rows.Count - 1).FormulaR1C1 = _
            "=IF(ISNUMBER(MATCH(RC3, R1C3:R[-1]C3, 0)),""DUPLI"",RC3)"
        .Offset(, -1).Value = .Value
        .ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

The OP still hasn't indicated whether "NULL" means an empty cell or literally the word "NULL", this version will work either way.

 

[PCL]

Guys,
I was interested to investigate the time concern and made some tests.
With 32768 rows filled
With Treat macro code updated with the screenfreeze.

Option Explicit
Sub Treat()
Dim LAST_ROW  As Long
Dim MyRG As Range
Dim i As Long
Dim StartTime, StopTime
    Application.ScreenUpdating = False
'---------  FOR  COLUMN  C   -----------
    LAST_ROW = Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LAST_ROW
        Set MyRG = Range("C1:C" & i)
        If (Application.WorksheetFunction.CountIf(MyRG, Cells(i, "C")) > 1) Then Cells(i, "C") = "DUPLI"
    Next i
     
'---------  FOR  COLUMN  A   -----------
    LAST_ROW = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LAST_ROW
        If (Cells(i, "B") <> "NULL") Then Cells(i, "A").ClearContents
    Next i
    Application.ScreenUpdating = True
End Sub

Treat macro --> 162 s
EvaluateData macro (Rev1) --> 164 s
EvaluateData macro (Rev2) --> 1021 s
navrichard macro --> 1 s but the result seems very different

Does it improve to use non loop code?
Is to freeze the screen not enought?

 

[jbeaucaire]

Is to freeze the screen not enough?

It's a start.

And there are different quality loops, too, but if you can apply a change to 32000 rows of data all at once with 8-10 lines of code and be done, surely you can see how that has to be WAY faster than evaluating 32000 rows of data one at a time with 2 or 3 lines of code EACH.

 

[Peter_SSs]

but the result seems very different

PCL

Not sure what test data you started with, and there remains some doubt about what the OP meant by "NULL". However, in the absence of any other test data/expected results, I used jbeaucaire's 'BEFORE' data as my test data and the 'AFTER' as the expected results and I interpreted a "NULL" cell as being empty.

My code produced the same results as jbeaucaire's. As far as I can see, so does yours if "NULL" is replaced in your code with ""

Does it improve to use non loop code?

I think your test results show that the answer is: Definitely!!!