Announcement

Collapse
No announcement yet.

Data cleaning with conditions - VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data cleaning with conditions - VBA



    Hi I am new to VBA and learning things:I have data in column A:A1 - it is Heading say NameA2 - MOL:22 Need to buy (2345 h,35 m) single rowA3,A4,A5 - MOL:21 need to Sell (853 h,8 m) (these are merged cells)A6, A7 - Need to rent (300 m) (these are merged cells). It goes on...Coulmn BB1, C1 and D1 are merged so it is like ABC/XYZ/PDFB2 - New (2345 h,35 m)B3 - New (483 h, 11 m)B4 - old (369 h, 56 m)B5 - Recent (2 s)B6 - New (150 h , 30 m)B7 - Old (149 h, 30 m) SO it goes on..Column C:C1 - it is already merged.C2 - XC3 - yC4 - yC5 - yC6 - ZC7 - ZSO it goes on..Coulmn D:D1 - it is already merged.D2 - 2345 h,35 mD3 - 483 h, 11 mD4 - 369 h, 56 mD5 - 2 sD6 - 150 h , 30 mD7 - 149 h, 30 mSO it goes on..Here I need to remove data within the parentheses in column A and retain only the text. Column B1,C1 and D1 needs to be unmerged and give the respective heading to that particular column like B1- ABC, C1 - XYZ, D1 - PDF. And for column D retain as it is . Create column E to find the total hours from column D. So convert minutes to hours, seconds to hours and get the total hours for Column D (Hours+min(converted to hours)+sec(converted to hours if it is there)).And also create column F convert from hours to days taking data from column E.So I have multiple excel files like excel 1, excel 2 and so on in a folder and it needs to be formatted as above by creating one macro file. So far what i have done is below and I am stuck up - I need a help please. Unmerge and repeat rows:Sub sbUnMergeRange()Range("A1:A30000").UnMergeDim strlast As StringDim lngRow As Longstrlast = Range("A2")For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count If Cells(lngRow, 1).text = "" Then Cells(lngRow, 1).Value = strlast Else strlast = Cells(lngRow, 1).text End IfNextEnd SubSub SplitValue() Dim avarSplit As Variant avarSplit = Split(Range("A1:A30000").Value, "(")End SubRemove parentheses:Function InParens(s As String) As String Dim i1 As Long, i2 As Long i1 = InStr(s, "(") If i1 Then i2 = InStrRev(s, ")") If i2 Then InParens = Mid(s, i1 + 1, i2 - i1 - 1) End If End If End Function ThanksAnil

  • #2
    Re: Data cleaning with conditions - VBA

    Anil
    Welcome to the Forum. I have a few suggestions for you that will help to get you a response.
    1. Separate your code from the body of your explanation and encompass it with code tags in accordance with the forum rules you agreed to when you joined.
    2. Upload a sample copy of your file with a before and after scenario. It should not be the entire file, but a representative sample.
    3. Clearly separate each step of your explanation with bullets. It is currently very difficult to follow and I will assume that based upon the current presentation, most will pass on it
    4. Be sure to clearly state your needs and objectives in simple language.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Re: Data cleaning with conditions - VBA

      Please let me know how to post in bullets if the below content is not readable. Thanks.# Hi,I am new to VBA and learning things:# I have attached the sample excel file for data cleaning - Explanation given below.# Here I need to remove data within the parentheses in column A and retain only the text. # Column B1,C1 and D1 needs to be unmerged and give the respective heading to that particular column like B1- ABC, C1 - XYZ, D1 - PDF. # And for column D retain as it is . # Create column E to find the total hours from column D. So convert minutes to hours, seconds to hours and get the total hours for Column D (Hours+min(converted to hours)+sec(converted to hours if it is there)).# And also create column F, convert from hours to days taking data from column E.# Lastly, I have multiple excel files like excel 1, excel 2 and so on in a folder this folder will contain only raw data. Raw data files needs to be formatted as above by creating one macro file. Macro file to placed in the same folder where raw data exists.
      Attached Files

      Comment


      • #4


        The previous ways of managing companies and enterprises no longer satisfy the business. Automation managemart is essential for the survival of business these days in a highly competitive market. The automation system allows us to standardize and accelerate all business processes to achieve goals. As a result, communication with customers becomes productive, departments of the company are in constant interaction, and income grows.

        Comment

        Working...
        X