Sep 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.
Thanks
Sep 27 2020 03:08 AM
Sep 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.
Sep 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
Nikolino
I know I don't know anything (Socrates)