Macro

[iuphim]

Hi,

I have a spreadsheet that has multiple chunks of column headings within the same spreadsheet. In one of the column there are data that are divided by a "|". I would like to find all the data divided by "|" in those column and bring that data down to another column and copy all the remaining columns down with this new parsed data. The amount of "|" varies. Is this something a macro can be written and if so can someone guide to do this?

I don't know how to put images in this thing to keep the format I need, so please refer to this download link for the pic of the file.

http://www.megaupload.com/?d=404EA0NH

Example:
CategoryP NumberC NumberColorSizeChair12345456 | 458 | 258Blue14 x 16
Should be
CategoryP NumberC NumberColorSizeChair12345456Blue14 x 16Chair12345458Blue14 x 16Chair12345258Blue14 x 16CategoryP NumberC NumberTypeColorSizeApplicationPaint25668124 | 568WatercolorVaries14 x 16Paper
Should be
CategoryP NumberC NumberTypeColorSizeApplicationPaint25668124WatercolorVaries14 x 16PaperPaint25668568WatercolorVaries14 x 16Paper

Thanks!

 

[wiklendt]

1) when you are writing a post, scroll down and you will see "additional options". within this section is "attach files", where you can attach image files, database files, zip files, etc. i personally hate going to third-party hosts to download files and images - it's a sure fire way to unwanted pop-ups and virus-risk.

2) have another look at the text in your post - do YOU find that easy to read? we don't either. the image helped, and i see you want to normalise the data, but the text you posted was gibberish and should have left it out. to help you help us help you, follow the links in my signature on how to post questions so they are easy for everyone to read.

3) how many of these lines do you have? it looks like not only a parsing question, but that you want/need to reformat these. it might just be easier doing it by hand. or else, parse out the C numbers, then import into access and normalise there, then export again to excel (if you absolutely need excel - looks like stuff you'd really need access for).

i assume the C number for each item is in the same column for each item? if so, use a new column to the right of your data to parse out the c numbers - you'd need to use a combination of LEFT(), RIGHT(), and MID() formulas (i.e., don't need macros) to get the values you need. excel help ought to get you started there.

HTH

(edit: someone with more excel experience in macros might be able to do all the steps in one swift motion, but it'd be quite a bit of coding, i expect)