Moving data from rows to multiple columns

rstrohfus

New Member
Joined
Jan 16, 2008
Messages
9
I use excel and I am not very good at writing VB so hopefully this makes since to someone. Any help would be greatly appreciated!!



I have a worksheet that has six columns, BU,GL Acct, Descr,OU,Ledger and Amount. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Data looks like this: Worksheet1<o:p></o:p>
<o:p></o:p>
BU GL Acct Descr OU Ledger Amount<o:p></o:p>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 ACTUALS 2500<o:p></o:p>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 INGGAAP 1500<o:p></o:p>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 STATUTORY 3500<o:p></o:p>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 USGAAP 4500<o:p></o:p>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 IFAS 7500<o:p></o:p>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 ACTUALS 3000<o:p></o:p>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 INGGAAP 5000<o:p></o:p>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 STATUTORY 7000<o:p></o:p>
<o:p></o:p>
I need to copy rows based on criteria that = column A, column B and column D are the same to worksheet 2. this needs to happen all the way through worksheet 1.<o:p></o:p>
<o:p></o:p>
Then in worksheet 2 their are five new columns starting in column H thru K. The names for these columns are ACTUALS, INGGAAP, STATUTORY, USGAAP and IFAS.<o:p></o:p>
<o:p></o:p>
Based on column E (Ledger), if the cell = ACTUALS then the Amount (Column F) for that string will be placed in the first row under that column name = ACTUALS and so on.<o:p></o:p>
<o:p></o:p>
Worksheet should look like this:<o:p></o:p>
<o:p></o:p>
BU GL Acct Descr OU Ledger Amount ACTUALS INGGAAP STATUTORY USGAAP IFAS <o:p></o:p>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 2500 1500 3500 4500 7500<o:p></o:p>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 3000 5000 7000 <o:p></o:p>
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

nirvana_

Board Regular
Joined
Mar 25, 2009
Messages
141
here you go. make sure your worksheet 1 and 2 is named "Sheet1" and "Sheet2" respectively. Or else you may need to check accordingly.

Code:
Sub CopyData()
    Dim i As Integer
    i = 2
    Do While Worksheets("Sheet1").Cells(i, 1) <> ""
        If Worksheets("Sheet1").Cells(i, 1).Value = Worksheets("Sheet2").Cells(i, 1).Value And _
        Worksheets("Sheet1").Cells(i, 2).Value = Worksheets("Sheet2").Cells(i, 2).Value And _
        Worksheets("Sheet1").Cells(i, 4).Value = Worksheets("Sheet2").Cells(i, 4).Value And _
        UCase(Worksheets("Sheet1").Cells(i, 5).Value) = "ACTUALS" Then
            Worksheets("Sheet2").Cells(i, 8) = Worksheets("Sheet1").Cells(i, 6).Value
        End If
        i = i + 1
    Loop
End Sub
 

rstrohfus

New Member
Joined
Jan 16, 2008
Messages
9
Thanks for the quick response! The macro worked for the first row(row2) and it placed the amount in the ACTUALS column, but then it ended. The next row on sheet1 with the same 3 field criteria (BU, GL Acct and OU) should of placed the amount in the INGGAAP column because column E has cell = INGGAAP. The amount should be on row2 under the INGGAAP column.

I liked how the first row worked!
 
L

Legacy 68668

Guest
try
Code:
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, t As Long, z As String
Dim dic1 As Object, dic2 As Object
Set dic1 = CreateObject("Scripting.Dictionary")
dic1.CompareMode = vbTextCompare
Set dic2 = CreateObject("Scripting.Dictionary")
dic2.CompareMode = vbTextCompare
With Range("a1").CurrentRegion.Resize(, 6)
    a = .Value
    ReDim b(1 To UBound(a, 1), 1 To 200)
    n = 1 : t = 4
    For i = 1 To 4 : b(1, i) = a(1, i) : Next
    For i = 2 To UBound(a, 1)
        For ii = 1 To 4 : z = z & a(i, ii) & ";" : Next
        If Not dic1.exists(z) Then
            n = n + 1 : dic1.add z, n
            For ii = 1 To 4 : b(n, ii) = a(i, ii) : Next
        End If
        If Not dic2.exists(a(i, 5)) Then
            t = t + 1 : dic2.add a(i, 5), t
            b(1, t) = a(i, 5)
        End If
        b(dic1(z), dic2(a(i, 5))) = a(i, 6)
    Next
    .Offset(, .Columns.Count + 1).Resize(n, t).Value = b
End With
End Sub
 

rstrohfus

New Member
Joined
Jan 16, 2008
Messages
9
That worked great!!!! Just have one last thing,

How can I get the rows that match the criteria (BU, GL Acct and OU) to rollup into one row and then delete the other rows that match the criteria.

So row 2 with BU = NNNNN GL Acct = 102581 and OU = 10030, should have a amount in each column starting with Column G throu K and the other roiws with the same criteria will be deleted.

If you can do that you our a genious!!!!!!!!


Thanks

Rich
<TABLE style="WIDTH: 72pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=96 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=96 height=17></TD></TR></TBODY></TABLE>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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
Top