Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TexasDon
 
Posts: n/a
Default Data Format: how can I re-format

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


  #2   Report Post  
Max
 
Posts: n/a
Default

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


  #3   Report Post  
Max
 
Posts: n/a
Default

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,


---
CName3 Contact3 Add3
etc


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

CName3 Contact3 Add3
CName5 Contact5 Add5
etc


--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
Changing the format of a block of data Greg Excel Discussion (Misc queries) 0 January 23rd 05 03:30 AM
format cell data to display stacked data dkay10 Excel Discussion (Misc queries) 1 January 17th 05 03:20 AM
format one data series, with error bars, and use it as default JBR Charts and Charting in Excel 2 January 9th 05 05:03 PM
Format Data Series Markers KB Charts and Charting in Excel 1 November 30th 04 04:37 AM


All times are GMT +1. The time now is 11:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"