Consulting

Results 1 to 7 of 7

Thread: Copy specific data from a row based on value in column A

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location

    Copy specific data from a row based on value in column A

    Hi All,

    I have looked through past threads so please accept my apology if this has already been covered.

    I have a set of data, it is a horrible data dump from an online form and I do not need it all, only specific columns contain the data I want.

    Furthermore, each time the online form is completed, the questions are tailored based on business sector. Hence there is data in different columns that I would like to group into a single column under that heading.

    To clean this data, I want to copy the data I need for each category and paste it into a different tab.

    To help further articulate, if the category in cell C3 reads Apples, copy Sheet1.E3 to Sheet2.C1 and if cell C4 reads Pears, then copy Sheet1.J4 to Sheet2.C2.

    So if E3 contains the profit for company in row 3 and J4 contains the profit for company in row 4 - the profits for each company will now be collated into one column under 'company profit'. Excel will know which cells to copy based on the value in the category field in the data source.

    It would be incredible if I could work out how to get a Macro to run the above instruction for every row in a selection and paste into next tab starting from first empty row.

    I hope that makes sense, any help would be appreciated! I am really stuck. Thanks in advance.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Easier to understand if you attach a small sample workbook with the 'before' and what you want for the 'after' so we can understand better
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Easier to understand if you attach a small sample workbook with the 'before' and what you want for the 'after' so we can understand better
    Hi Paul, great idea! I have attached a sample for you. Two tabs, before (how it looks now) and after (how I would like it to look).

    The information I need in the before tab needs to be reorganised based on the category. So if the category reads A, the macro will know where to find the income before and income after data for that row.

    If I can help clarify anything further, please let me know!
    Attached Files Attached Files

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    This is not very general purpose, but it works with your sample data


    Option Explicit
    
    
    Sub FormatData()
        Dim wsIn As Worksheet, wsOut As Worksheet
        Dim r As Long, c As Long
        Dim rData As Range
        
        Set wsIn = Worksheets("Before")
        Set wsOut = Worksheets("After")
        
        'copy input cells to output
        wsIn.Cells(1, 1).CurrentRegion.Copy wsOut.Cells(1, 1)
        
        'reference output cells
        Set rData = wsOut.Cells(1, 1).CurrentRegion
        
        'down the rows, starting at 2 until the end
        For r = 2 To rData.Rows.Count
            'accross the columns, starting at 4 untile the end
            For c = 4 To rData.Columns.Count
                'if the c-th cell in the r-th is blank, get the non-blank one to the right and put it there
                '   clear it afterwards
                If Len(rData.Cells(r, c).Value) = 0 Then
                    rData.Cells(r, c).Value = rData.Cells(r, c).End(xlToRight).Value
                    rData.Cells(r, c).End(xlToRight).ClearContents
                End If
            Next c
        Next r
    
    
        'delete columns that only have the headers left
        Set rData = wsOut.Cells(1, 1).CurrentRegion
        Do While Application.WorksheetFunction.CountA(rData.Columns(rData.Columns.Count)) = 1
            rData.Columns(rData.Columns.Count).EntireColumn.Delete
            Set rData = wsOut.Cells(1, 1).CurrentRegion
        Loop
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    Hi Paul,

    Thanks, I will try to adapt it to the bigger data set that I have. I will let you know how I get on.

    Thanks again for your time!

    Erinc

  6. #6
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    Hi Paul,

    My data isn't uniform like the sample (there wont be blanks in the same way), probably a poor sample by me! So I need another approach.

    Is it at all possible to follow the logic below:


    For each row (of selection on Sheet1)
    IF column C reads "C" then copy Sheet1.D paste to Sheet2.D
    and
    copy Sheet1.E paste to Sheet2.E and so on...

    Then

    For each row (of selection)
    IF column C reads "D" then copy Sheet1.F paste to Sheet2.D
    and
    copy Sheet1.G paste to Sheet2.E and so on...

    Repeat for entire selection on Sheet 1

    Paste in destination starting from first blank row. I have left the row numbers out because these would change based on when the code encounters a specific value in column C on sheet 1 and where the first blank row is on sheet 2.

    Thanks again!

  7. #7
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    I guess ideally what I need to do (I don't even know if something to this effect would be possible hence the copy and paste approach), is have a vlookup where the return column changes based on the value of column C. So if column C reads Apples, the vlookup will return the value in column 20, but if the value in column C reads Chicken, then the vlookup would return the value in column 25.
    Last edited by est; 06-02-2020 at 06:42 AM.

Posting Permissions

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