Data Format: how can I re-format

[Guest]

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

 

[Guest]

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)),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Select C22, 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

 

[Guest]

Sorry, a typo & a missing line corrected ..

Put in E2:
=OFFSET($D$2,ROW(A1)*5-5+COLUMN(A1)-1,)

Copy E2 across 5 cols to I5,

The last line above should read as:

Copy E2 across 5 cols to I2,

The above should read as
(for the sample data set given):