Data Format: how can I re-format

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 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
Click to expand...

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
 
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,
Click to expand...

The last line above should read as:
Copy E2 across 5 cols to I2,
Click to expand...

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