[Solved] Using COUNTIF as an equation inside a macro

[MATT.B]

I cant upload an example sorry sensitive information. In my sheet Column A contains text and column B contains a formula =COUNTIF($Sheet2.A$1:A$10000;$Sheet1.A1)
i have created a simple macro that will go row by row and check the value of column B if its greater that 1 it will delete the contents of cell A

Code: Select all

oSheet = odoc.getSheets().getByIndex(1)
For I = 0 To 10000
oCell = oSheet.getCellByPosition(1,i)
Check = oCell.getValue
if Check > 1 then
oSheet.getCellByPosition(0,i).clearContents(4)
endif
Next I
MSGBOX "DONE"

it takes a long time to step rows because oo updates the all the values of Column B every time A is deleted so I'm only processing about 10-20 lines a min
i would like to eliminate the need for column B and do the countif calculation as part of the macro so something like

Code: Select all

if COUNTIF($Sheet2A$1:A$10000;Ai) > 1 then
oSheet.getCellByPosition(0,i).clearContents(4)
endif
Next I 

but i dont know how to add the countif into the macro code. any help would be appreciated as i will have to do this each morning for the next 5-14 days while we try and locate the error that is causing the duplicate entries
and yes 10000 lines is needed for match in fact 20000 lines is actually needed for match but oo tends to crash when updating that many formulas

[FJCC]

This macro should be much faster because you do not have to repeatedly read cell values. It reads the array of data in the cell range and acts on that.

Code: Select all

Sub Main
odoc = ThisComponent
oSheet = odoc.getSheets().getByIndex(1)
oRange =  oSheet.getCellrangeByName("A1:B10000")
oData = oRange.DataArray

For i = 0 To 9999
  Value = oData(i)(1)
  if Value > 1 then
    oData(i)(0) = ""
  endif
Next I
oRange.DataArray = oData
MSGBOX "DONE"
End Sub

It is not clear to me why you are using a macro instead of filtering the data on Sheet2.

[MATT.B]

so there is a file that is uploaded from our billing software/server each day a person takes that file from her computer and uploads it in to the national wide billing account system for our dealership. usually this file only contains invoices from the previous day but there is an error in the system so the invoices are not being cleared out and the file keeps getting bigger and bigger each day. tech support is trying to find the error that is causing this but until then we have to compare the file from the day before with the current day and manually delete the duplicates.

sheet 2 is a list of invoices that have been uploaded already and sheet 1 is today's file. the macro searches sheet 2 for the invoice and removes it from sheet 1 if it exists. the macro its self isn't whats slowing it down its the countif formula in column B that is slowing down oo because every time a single value in column B changes it scans all 20,000 formulas for a change. it takes me about 3 hours to manually delete the duplicate values because i have to sift through close to 20,000 lines of data. using the macro to do this work does speedup the process i can activate the macro and go about my day doing other things but it ties up the computer for about 2 hours while it deletes lines.

i just figured it would be quicker if i put the search in the macro instead of in a cell. when the macro is finished i have about 10 lines of data to upload into the billing system the problem is the 10 lines are not just appended to the end if the previous day but scattered in the file so finding them is not an easy task. if i dont sort out the duplicate invoices every one kicks back an error that has to be sorted out 1 invoice at a time. granted not every line is a separate invoice so its not as many as 20,000 but can still take close to 8 hours because you have to search the system for each invoice number and make sure it was taken care of. unfortunately my boss rather deal with a system thats 15 years old then have to pay a fee for each invoice.

[FJCC]

A version of the macro I wrote should work for you. What I posted removes data from Sheet2 because the code you posted seems to do that. The line

Code: Select all

oSheet = odoc.getSheets().getByIndex(1)

makes the oSheet variable refer to the second sheet from the left, which I took to be Sheet 2. The line

Code: Select all

oSheet.getCellByPosition(0,i).clearContents(4)

then clears data from that sheet. If the COUNTIF formula is on the leftmost sheet and you also want the data deleted from that sheet, my macro should be

Code: Select all

odoc = ThisComponent
oSheet = odoc.getSheets().getByIndex(0)
oRange =  oSheet.getCellrangeByName("A1:B10000")
oData = oRange.DataArray

For i = 0 To 9999
  Value = oData(i)(1)
  if Value > 1 then
    oData(i)(0) = ""
  endif
Next I
oRange.DataArray = oData
MSGBOX "DONE"

See if that works on a copy of your file. I ran a test and it handled 10000 rows in less than a second.

I still think you could use a filter, such as Data -> Filter -> Standard Filter. After choosing that menu, set Column B to have a value of 1 and, under More Option, copy the results to a convenient place, maybe cell D1.

[Villeroy]

This is so absurd. Why don't you simply use a proper database? How many years did you struggle with this?

[MATT.B]

This is so absurd. Why don't you simply use a proper database? How many years did you struggle with this?

if you don't have any thing useful to add then don't comment. people that ask for help don't care about what your personal feelings are on the topic they are looking for a possible solution not to have a some one tell them why or how you feel. or that they are doing it wrong some times its out of the control of the little man that makes a mear hourly wage

moderator could you please delete his post as its not on topic

[MATT.B]

A version of the macro I wrote should work for you. What I posted removes data from Sheet2 because the code you posted seems to do that. The line

thank you for your help I tried out your code and I think its working but I did notice that it removes the formula from column B and I think its deleting non duplicates
I am marking this as solved and thank you for the help