copy rows on cell based value and condtionaly paste it to other column

**sanketgroup** · 09-29-2011, 10:07 AM

Hi
this is very dynamic requirement.
I have attached image as well sample sheet for what i need.

In brief:
I want to transfer(cut or moving) the cells to respective column but on same row from where is has been cut.

For example: (where , (comma) means different column

Account No.1
item33,quantity,amount,
item2,quantity,amount,
item48,quantity,amount,

Account No.2
item9,quantity,amount,

Account No.3
item19,quantity,amount,
item2,quantity,amount,
item6,quantity,amount,
item75,quantity,amount,
item52,quantity,amount,

Now i want put it like this

Account No.1 item33,quantity,amount,
Account No.1 item2,quantity,amount,
Account No.1 item48,quantity,amount,

Account No.2 item9,quantity,amount,

Account No.3 item19,quantity,amount,
Account No.3 item2,quantity,amount,
Account No.3 item6,quantity,amount,
Account No.3 item75,quantity,amount,
Account No.3 item52,quantity,amount,

I have also attached image and sample excel file.
[img=http://s4.postimage.org/3208mebvo/data_convert.jpg]
Excel file is attached below.

**Kyle123** · 09-29-2011, 11:02 AM

Hi,

Give this a whirl, your data format goes a bit funny halfway down so I wasn't sure what you wanted to display - I assumed that this hadn't been entered correctly.

To run this make sure there is nothing to the right of your data

Public Sub Kyle()

Dim rng As Range
Dim var
Dim lr As Long

On Error GoTo handler

Do
With Sheet1
    Set rng = Range("A:A").Find("Total :").CurrentRegion
    
    With rng
        var = Array(.Cells(1, 1).Value, .Cells(1, 3).Value, .Cells(1, 5).Value, .Cells(1, 6).Value, .Cells(1, 7).Value)
    End With
    
    lr = Sheet1.Range("I65000").End(xlUp).Row + 1
    .Range("I" & lr).Resize(rng.Rows.Count - 2, 5).Value = var
    .Range("N" & lr).Resize(rng.Rows.Count - 2, 4).Value = rng.Offset(1, 0).Resize(rng.Rows.Count - 2, rng.Columns.Count - 3).Value
    
    
    rng.ClearContents
End With
Loop

Exit Sub

handler:


End Sub

**sanketgroup** · 09-29-2011, 12:39 PM

Thanks a lot Kyle123
this is what i exactly wanted

Data format which you are talking about its actually there.
This data is auto generated from some other software. i dont have control over it. It comes every time.
Is there anyway way to delete those rows?
For example, required rows in Column A - always have either xxxxx-x formatted Account# or date or word begins with "Total :" anything other then this- those rows are not useful and we can delete it before moving the data.
So how abt some new addition code which first delete the the rows which has value other then xxxxx-x formatted account# or date or word "Total :" and then run code which u gave for moving and arranging data.
[Account# could be xxxxx-x or xxxx-x or xxx-x or xx-x or x-x] means 5digit-1digit or 4digit-1digit or 3digit-1digit or 2digit-1digit or 1digit-1digit
Thanx a lot
i really appreciate your help