09-26-2020 04:19 PM
09-26-2020 04:19 PM
Every month I get about 40 Excel files and need to clean the data (remove extraneous rows and columns, remove unnecessary headers, ensure all values are numbers not text, convert merged cells to regular cells, etc.). Is there such a thing as an Excel data cleaner that can do something like this across all 40 excel files if I put it them in a folder with the click of a button? If yes or if you know of anything similar, please post recommendattions below.
09-27-2020 03:08 AM
09-27-2020 04:16 AM
I do this all the time maybe I can help you. Can you upload a sample file that points out the data cleaning tasks? If you have sensitive data just replace it with something random.
09-27-2020 10:07 AM
Many roads lead to Rome, as well as many proposed solutions in Excel lead to a solution.
Here is a small VBA example to clean files / worksheets, etc. yourself, you just have to adapt to what you want to clean exactly.
Sub clean () Dim wksA As Worksheet Dim rngB As Range Dim lngLine As Long Dim lngLastline As Long Set wksA = ActiveWorkbook.Worksheets ("Sheet1") 'adjust !! lngLlastZeil = wksA.Cells (wksA.Rows.Count, 2) .End (xlUp) .Row wksA.AutoFilterMode = False wksA.Range (wksA.Cells (4, 1), wksA.Cells (last line, 22)). Sort _ Key1: = wksA.Cells (4, 21), _ Order1: = xlDescending, _ Header: = xlNo, _ OrderCustom: = 1, _ MatchCase: = False, _ Orientation: = xlTopToBottom, _ DataOption1: = xlSortNormal For lngZeile = lngLlastZeil To 5 Step -1 If Application.WorksheetFunction.CountIf (wksA.Range (wksA.Cells (3, 2), wksA.Cells (lngZeile, 2)), wksA.Cells (lngZeile, 2))> 1 Then wksA.Rows (lngZeile) .Delete End If Next 'possibly. sort the names last line = wksA.Cells (wksA.Rows.Count, 2) .End (xlUp) .Row wksA.Range (wksA.Cells (4, 1), wksA.Cells (last line, 22)). Sort _ Key1: = wksA.Cells (4, 2), _ Order1: = xlAscending, _ Header: = xlNo, _ OrderCustom: = 1, _ MatchCase: = False, _ Orientation: = xlTopToBottom, _ DataOption1: = xlSortNormal End Sub 'From German to English translate
I know I don't know anything (Socrates)