Database download...how to check errors in the least steps?

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
Ok, I'm learning more and more but here's what I'm trying to do.

We have a database that exports a dump to .csv file on a web page. I save this sheet as .xls and then reopen.

There are 5 columns of information that can contain invalid data due to the fact that users leave placemarkers in the fields in order to save the record. These markers are often forgotten about and the record is changed to "closed" therefore, they don't check back. The markers are not consistent.

So I'm trying to figure out a better way than sorting and manually searching each line of order numbers and account numbers, to find the invalid data. I tried using Validation, but this seems I would have to recreate these rules each time (unless I could save it in a template?) Not sure if it could be a macro, but all I need to know is which database id's contain invalid data, and which column and item is invalid. thx
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

zilpher

Active Member
Joined
Mar 27, 2003
Messages
442
Re: Database download...how to check errors in the least ste

Hi Pete

Welcome to the board.

Do you have control over the database dump? If so, could you alter the SQL to prevent these records being selected?

Otherwise, you should create an addin that:

Checks the ACTIVE workbook is the right type of book to work on

Runs thru each row looking for the data you know is incorrect (placeholders as you called them). For this part you could use the Find function.

Here's a starter for ten on finding those placeholders, I have assumed it will be a '¬'

You will also need to create a menu, there's no finer way IMHO than Mr Walkenbach's tip 53 http://j-walk.com/ss/excel/tips/tip53.htm

Hope this gets you started in the right direction.

Z

*********************
Sub FindData()
Dim rngRange As Range, strFirstRange As String

If TypeName(Selection) <> "Range" Then Exit Sub
Set rngRange = Selection.CurrentRegion.Find("¬")

If rngRange Is Nothing Then Exit Sub
strFirstRange = rngRange.Address

Do
Debug.Print rngRange.Row
Set rngRange = Selection.CurrentRegion.FindNext(rngRange)
Loop While Not rngRange Is Nothing And Not rngRange.Address = strFirstRange

End Sub
*********************
 

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
Re: Database download...how to check errors in the least ste

Thanks, I'll try some of these ideas out and see how that goes. Already jumped into another project too. Fun, fun...

PS: No control over the db it is hosted from the outside.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top