Spreadsheet info into a list format

[richtea141]

Hi all,

I have a big job that needs doing for my work and i have no idea how to do it.

I have data in a cells layed out like below:

https://docs.google.com/leaf?id=0Bx...ZmE3OTc5ZGMxMmU3&sort=name&layout=list&num=50

I need the information from the rows 'part number' and 'total quantity' in a vertical list like below.

NBD0001 40
NBD0002 42
NBD0003 42
NBD0005 20
NBD0006 14
NBD0007 40
NBD0011 20
etc

If possible to have all the info in the table in a vertical list e.g.
NBD0001 2 300 600 45 20 450 2.92 40 116.8
NBD0002 3 300 900 65 14 455 3.79 42 210.84

I have lots more to do and would be very very grateful if anyone could help.

Thanks

Rich

 

[richtea141]

Any ideas guys?

 

[Silvrhelm]

Hi, I think I have a solution for you.

So first of all insert a second worksheet in your spreadsheet.
Now copy each part of your table into the new worksheet so you have like one long table rather than seperated (make sure you paste values not formula)

PartNumber nbd0001 nbd0002 nbd0003...................................................................................nbd0048
rows
Row Width
Location Width
...
...
...
...
Total Weight

Now insert this macro into your VBA editor

Sub MakeList()
    Dim i As Integer
    Dim j As Integer
        For i = 1 To 10
        For j = 1 To 32
            Cells(i, j).Copy
            Cells(j + 12, i).Select
            ActiveSheet.Paste
        Next j
        Next i
End Sub

And run the macro, should give you the same table but like inverted so that the nbd numbers run vertically and the headers run horizontally.

 

[richtea141]

Thanks.

But this doesnt really help because i still have to copy and paste the info. There is a lot of data i have got to sort. I could copy the sections i need and transpose it into a list but i was wondering if there were any other ways of doing it.

 

[Silvrhelm]

Hi, I spent ages trying to think of a way to organise it into the table i described for large amounts of data, but I cant come up with anything.

Sorry I cant help more. Im sure someone else will be able to advise you.

 

[richtea141]

No worries mate