help with a macro to sort my data into readable order

[tonywatsonhelp]

Hi Everyone,

So everymonth we export data that needs to be sorted out.

I need some help but its probably easiest if i show you in an example below

	A	B	C	D	E	F	G	description
1								so in column A we have some rows that start with "P"
2	P100	adress	address2	addres3	adress4	address5	address6	Each one is the address for the data below it.
3	order details	soop	12	1234	11	11	someything	so i want a macro that can organise this data for me
4	more orders	etc	etc	etc	etc	etc	etc	in another sheet called "Sorted"
5								simply take the address that starts with P and put it into columns A to G then take each row for that order and put it into H to N so every order has the address before it
6	P21							if posiblle leave a space between each order?
7	next lot of orders	etc						example below
8
9
10	P11
11	next lot of orders
12
13
14	Pw11
15	next lot
16

A

B

C

D

E

F

G

H

I

J

K

L

M

N

P100

adress

address2

address3

address4

adress5

adress6

order details

soop

12

1234

11

11

something

P100

adress

adress2

adress3

adrdress4

adress5

adress6

More Orders

etc

etc

etc

etc

etc

etc

P21

and the same again

So sorry its a bit rushed but hopefully you can see what i mean,
Each row that starts with a "P" is the address and everything under it is the order for that address,
all i want is the info on one row for all oders?

please help if you can i'm totally stuck

Thanks

Tony

 

[offthelip]

try this code:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 7))
With Worksheets("Sorted")

.Range(.Cells(1, 1), .Cells(lastrow, 14)) = ""
outarr = .Range(.Cells(1, 1), .Cells(lastrow, 14))
Dim Adrarr(1 To 7) As Variant

indi = 2
For i = 2 To lastrow
  ft = UCase(Left(inarr(i, 1), 1))
  If ft = "P" Then
  ' copy new address
    For j = 1 To 7
     Adrarr(j) = inarr(i, j)
    Next j
  Else
  For j = 1 To 7
  outarr(indi, j) = Adrarr(j)
  outarr(indi, j + 7) = inarr(i, j)
  Next j
  indi = indi + 1
  End If
Next i
 .Range(.Cells(1, 1), .Cells(lastrow, 14)) = outarr
End With
End Sub

 

[tonywatsonhelp]

This is great off the lip thank you