Hi Bryan
I studied your solution carefully, well done.
I was considering an alternative approach and wish to share with you. My
proposal is:
use text to column, delimited with "space" and ":" on the whole file;
Heading row
cells(1,1)="Item details"
cells(1,2)="Model nr"
cells(1,3)="Code nr"
etc
for data row=2 to last data row
now Find row number containing word "Item" (as rownr)
then refer to relevant data using rownr + "nr of rows down" and colnr (which
can be counted easily)
cells(2,1)=cells(rownr,3)
cells(2,2)=cells(rownr,6)
cells(2,3)=cells(rownr+1, 3)
etc
next row
for description field which has been broken into multiple cells, we can
concatenate them back
"Bryan Hessey"
wrote in message
news:Bryan.Hessey.26lnxb_1145598602.5195@excelforu m-nospam.com...
Sample file after following the F2 formula, copy-paste special values to
a new sheet, delete columns A to E, sort = header row, column A
ascending, remove all non-required rows.
As previously stated the bottom portion of your sets is a little
non-fixed format and some manual intervention seems required, but it
may be more meaningful to you.
Hope this helps
--
Bryan Hessey Wrote:
Hi,
A quick play with your file, opened in MS Word, replaced " " (3
spaces) by tab, saveas DOS text. In Excel, Data, import external, in
Wizard take Tab and colon : as delimiters, treat consecutive delimiters
as one, into A1
That gave A to L in the attached.
There were two spaces, not 1, in front of your data.
Set the formula in H2 as
=IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)="
",MID(B2,2,999),IF(B2<"",B2,"")))
copied across, and bulk copied down.
That gave columns H to L
With that range still selected, did Copy and Paste Special Values into
cell N2
That gave columns N O P and Q as your partially cleaned data.
Columns A to M can be deleted, they were just to show.
Does this help?
-AMENDED POST-
The formula to post in F2 is
=IF(LEFT($A2,12)="ITEM
DETAILS",OFFSET($A$2,(INT(ROW()-2/4)-1)+INT((COLUMN()-6)/4),(MOD((COLUMN()-6
),4)),1,1),"")
then formula copy that to ED - then, whilst still highlighted,
bulk-formula copy to end of data
note, ED is one column too many, but where ED does not contain ITEM
DESCRIP then an error has occurred and manual adjustment is required,
sometimes by deleteing a row after joining data bits, sometimes by
inserting a row.
--
+-------------------------------------------------------------------+
|Filename: Spc4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4671 |
+-------------------------------------------------------------------+
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=534393