Try tinkering around with these steps
Assuming your list is in col A,
data in A2 down
(If data starts in A1 down, insert a new row for the col header)
Put a label in A1: List (say)
The list would look something like the sample data-set below in A1:A23
(a mix of groups of 4 rows and 3 rows, separated by blank rows)
List
CName1
Contact1
Add1
City1
CName2
Contact2
Add2
City2
CName3
Contact3
Add3
CName4
Contact4
Add4
City4
CName5
Contact5
Add5
Put in B6: =IF(COUNTA(A2:A5)=4,"X","")
Copy B6 down until 1 row after the last row of data in col A
(For the sample data, copy down to B24)
Put in C2:
=IF(B2="x",ROW(),IF(OR(OFFSET(B2,4,)="x",OFFSET(B2 ,3,)="x",OFFSET(B2,2,)="x",OFFSET(B2,1,)="x"),ROW( ),""))
Put in D2:
=IF(ISERROR(MATCH(SMALL(C:C,ROW(A1)),C:C,0)),"",IN DEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))
Select C2:D2, fill down to D24
Col D will extract only the groups of 4 rows
(we'll do the groups of 3 rows a little later)
Now to re-lay col D into the adjacent cols:
Put in E2:
=OFFSET($D$2,ROW(A1)*5-5+COLUMN(A1)-1,)
Copy E2 across 5 cols to I5,
fill down until blanks appear,
signalling exhaustion of data extracted from col D
Cols E to G will return the desired results
(Ignore col I which will return zeros)
viz.:
CName1 Contact1 Add1 City1
CName2 Contact2 Add2 City2
CName4 Contact4 Add4 City4
etc
Just freeze the results in cols E to G elsewhere with a copy paste special
values ok
Now to extract the groups of 3 rows
Select row1
Click Data Filter Autofilter
In the autofilter droplist in C1,
select "(Blanks)"
Select col A copy
In a new Sheet2 (say)
--------------------------
Right-click on A1 paste special values Ok
This'll paste over the filtered rows only
which is all the groups of 3 rows (including the header in A1)
To re-lay the groups of 3 rows in col A
Put in B2:
=OFFSET($A$2,ROW(A1)*4-4+COLUMN(A1)-1,)
Copy across 4 cols to E2,
fill down until blanks appear,
signalling exhaustion of data extracted from col A
Cols B to D will return the desired results
(Ignore col E which will return zeros)
CName3 Contact3 Add3
etc
As before, just freeze the results in cols B to D elsewhere with a copy
paste special values ok
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"TexasDon" wrote:
The spreadsheet that was exported for me to use has the information in
paragraph form:
It is customer ID name and billing info but it is like a long series of
address labels.
I want to be able to have them listed under captions like company name,
contact, address, city, etc.. so I can use the data.
There is an empty row between each entry. Each entry is 3 or 4 rows.
There are about 500 records so i dont want to manually set them up.
Thanks
|