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