Interesting use of the Offset

"Bryan Hessey" wrote:


PY,

Thank you for your response.

Whichever option the OP chooses I think there will always be a need to
manually adjust some data. In the trial data I noticed two occasions
where the data had split over two lines, and with no real identifier to
detect these splits (ie, the supposed next line may or may not be
present) I see no easy way to automate all parts of that concatenation,
plus, not all fields seem present on all items. However, with a mere
1,000 or so records it would be more difficult to code than to fix
manually. Where 98% of the file is correct the OP could easily do these
fixes, The data is meaningful to the OP, more easily read and problems
detected, I was trying to produce a worksheet where the OP could see
those changes take effect as they were made rather than setting another
rule and re-importing the data.

Having said that I have no objections whatsoever to other suggestions
being made, after all it's the 'hive-mind' coupled with a few experts
that makes a forum a successful place, so please, go ahead and make any
suggestion.

Whatever gets the task completed for the OP is really the only
consideration, and that may be either by a more clever solution, or by
a more simple solution that can be understood, adapted, and used by the
OP.

Bryan

--

PY & Associates Wrote:
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



--
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