+ Reply to Thread
Results 1 to 5 of 5

Copy Rows with Certain parameter to a fixed Coloumn

  1. #1
    Registered User
    Join Date
    02-20-2008
    Posts
    28

    Copy Rows with Certain parameter to a fixed Coloumn

    Dear All,

    I have a range of data which when comes out from a sytem is jumbled up. I need to segregate the data to particular coloums which are of identical in nature but the issue is that that particular string can be from any coloumn.

    I have enclosed a sample sheet where there is a sheet with fixed coloumn names and the data for the same is jumbled in different rows. Where there is no data for that partiuclar row for that coloumn it will be blank.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    Try using the following macro


    Please Login or Register  to view this content.
    Martin

  3. #3
    Registered User
    Join Date
    02-20-2008
    Posts
    28

    Feedback

    Well thanks for the macro but I need the complete phrase for the relevant feild and not just the part for Example Net Amount Coloumn should have Net Amount: 0.1315 and not just the value. This concept hold goods for all the rest of the coloumn where I need the entire phrase and not just the part. Also it seems that data is missing as if you count teh raw data it is 166 while the final output is only 93. If there is a certain row where the data is missing for that particular coloum then its blank but rest of the data of that row should be filled up. There are some data as SEDOL or ISIN begining data which can come in the last coloumn heading as ID.

    Kindly modify the macro and let me know as I would be much obliged.
    Last edited by liarliar; 05-29-2008 at 02:12 AM.

  4. #4
    Registered User
    Join Date
    02-20-2008
    Posts
    28

    Latest Feedback

    I have modified the code to get the complete data and not just a part but now the problem is that say for the first coloum I have all the data with *Type* into it but also want to include where "*ltd*" is also mentioned.

    Please let me know how do I modify the code....


    Sub Test()
    Dim TypeFound As Boolean
    Sheets("cadl771").Activate
    Sheets("Sheet1").Rows("3:65536").Clear
    For N = 2 To Cells(65536, 1).End(xlUp).Row
    TypeFound = True
    On Error GoTo TypeNotFound
    X = Rows(N).Find("*Type*", , xlValues, xlPart).Column
    On Error GoTo 0
    If TypeFound = True Then
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(1, 0) = Mid(Rows(N).Find("*Type*", , xlValues, xlPart), 1, 999)
    On Error Resume Next
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 1) = Mid(Rows(N).Find("Net Amount", , xlValues, xlPart), 1, 999)
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 2) = Mid(Rows(N).Find("Gross Amount", , xlValues, xlPart), 1, 999)
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 3) = Mid(Rows(N).Find("Currency", , xlValues, xlPart), 1, 999)
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 4) = Mid(Rows(N).Find("Frequency", , xlValues, xlPart), 1, 999)
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 5) = Mid(Rows(N).Find("Record Date", , xlValues, xlPart), 1, 999)
    Sheets("Sheet1").Cells(65536, 1).End(xlUp).Offset(0, 6) = Mid(Rows(N).Find("Pay Date", , xlValues, xlPart), 1, 999)
    End If
    Next N
    Exit Sub

    TypeNotFound:
    TypeFound = False
    Resume Next
    End Sub

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    The following should work. I notice that in some rows there are two cells that contain Ltd and the code will pick up the left most one of these.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1