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

[est]

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.

[Paul_Hossler]

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

[est]

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!

[Paul_Hossler]

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

[est]

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

[est]

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!

[est]

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.