Moving data from rows to multiple columns

[rstrohfus]

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-comfficeffice" /><o></o>
<o></o>
Data looks like this: Worksheet1<o></o>
<o></o>
BU GL Acct Descr OU Ledger Amount<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 ACTUALS 2500<o></o>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 INGGAAP 1500<o></o>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 STATUTORY 3500<o></o>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 USGAAP 4500<o></o>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 IFAS 7500<o></o>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 ACTUALS 3000<o></o>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 INGGAAP 5000<o></o>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 STATUTORY 7000<o></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
Worksheet should look like this:<o></o>
<o></o>
BU GL Acct Descr OU Ledger Amount ACTUALS INGGAAP STATUTORY USGAAP IFAS <o></o>
<st1:stockticker>NNN</st1:stockticker> 234567 Asset 10030 2500 1500 3500 4500 7500<o></o>
<st1:stockticker>AAA</st1:stockticker> 244554 Asset 10035 3000 5000 7000 <o></o>

 

[nirvana_]

here you go. make sure your worksheet 1 and 2 is named "Sheet1" and "Sheet2" respectively. Or else you may need to check accordingly.

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]

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!

 

[Legacy 68668]

try

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]

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>