Move Data to Different Columns based on value of Column in a sheet

writetoevv

Board Regular
Joined
Mar 9, 2012
Messages
71
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi Friends

I have attached two snapshots.
Input File refers to the data we receive from another business.
Output file refers to the data we needed in a specific format according to Input File.

Would you please help to do it using VBA macro.
PS, i may share excel sheet if needed.
 

Attachments

  • Input File.jpg
    Input File.jpg
    113 KB · Views: 13
  • OutPut Needed.jpg
    OutPut Needed.jpg
    121.6 KB · Views: 13

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Your Input File has only one DR while the Output Needed has three DR's. Please clarify this discrepancy in detail. It would also be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Hi Mumps

It is my mistake. To show more records in Output File, i have updated Output File Once i uploaded input file to mrexcel. It caused confusion.
Here is updated input file for your reference
 

Attachments

  • Input File new.jpg
    Input File new.jpg
    107.6 KB · Views: 4
Upvote 0
Does the the Output Needed sheet already exist or does the macro have to create it? It is hard to work with a picture. Please see the instructions in Post #2 to post a screenshot or upload your file.
 
Upvote 0
Does the the Output Needed sheet already exist or does the macro have to create it? It is hard to work with a picture. Please see the instructions in Post #2 to post a screenshot or upload your file.

Hi

file is upload to below location.


fyi, output data can be created on first sheet itself. just to show input & output data separately created it in two different sheets.
 
Upvote 0
Try:
VBA Code:
Sub MoveData()
    Application.ScreenUpdating = False
    Dim lastRow As Long, lastRow2 As Long, CR As Long
    lastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    CR = Columns(5).Find("CR", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range("B3:D" & lastRow).Copy Range("A" & lastRow + 2)
    Range("B3:B" & lastRow).Copy Range("D" & lastRow + 2)
    Range("E3:E" & lastRow).Copy Range("E" & lastRow + 2)
    Range("F" & lastRow + 2).Resize(, 2).Value = Array("Withdrawal Amount (Dr)", "Deposit Amount (Cr)")
    Range("F4:F" & CR).Copy Range("G" & lastRow + 3)
    lastRow2 = Columns(7).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range("F" & CR + 1 & ":F" & lastRow).Copy Range("F" & lastRow2 + 1)
    lastRow2 = Columns(1).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range("A" & lastRow + 2 & ":G" & lastRow2).Borders.LineStyle = xlContinuous
    Columns.AutoFit
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
You are very welcome. :)
 
Upvote 0
This macro will place the data to the right of the current data instead of at the bottom.
VBA Code:
Sub MoveData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Range("B3:D" & lRow).Copy Range("I1")
    Range("B3:B" & lRow).Copy Range("L1")
    Range("E3:E" & lRow).Copy Range("M1")
    Range("N1").Resize(, 2).Value = Array("Withdrawal Amount (Dr)", "Deposit Amount (Cr)")
    v = Range("E4:E" & lRow).Resize(, 2).Value
    For i = LBound(v) To UBound(v)
        If v(i, 1) = "DR" Then
            Range("N" & i + 1) = v(i, 2)
        Else
            Range("O" & i + 1) = v(i, 2)
        End If
    Next i
    Range("N1" & ":O" & lRow - 2).Borders.LineStyle = xlContinuous
    Columns.AutoFit
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top