PDA

View Full Version : Copy specific data from a row based on value in column A



est
06-01-2020, 09:18 AM
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
06-01-2020, 09:36 AM
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
06-01-2020, 10:01 AM
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
06-01-2020, 01:26 PM
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
06-02-2020, 01:06 AM
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
06-02-2020, 01:34 AM
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
06-02-2020, 06:25 AM
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.