Excel 2007 : Extracting data from a parts list?

Hello I wonder if you folks could advise me on how to extract data from a circuit board parts list done in Excel 2007.

In the parts list I have several columns with rows of data but am only concerned with 2 of them. (I'll delete the unwanted columns and rows).
One of the columns is part name, another column has the component names that are fitted to the board for that part number.

In each row there is a part number I.E. abc123 and in another cell all the component names that use that part name may be R1, R2, R35, R42 all in a single cell.
E.G.1 (As I receive the data).

Partname |Component Name |
partname1 | R1, R2, R35, R42 |
partname2 | C2, C5, C7|

The number of component names in a single cell is not fixed, it could be 1 it could be 30+, the number of rows is unspecified.
The part names should be a single string. The component names are general strings delimited by a comma.

What I would like to do is extract the component names all into their own row of a single column and place the part name that they use in another column next to them.
E.G.2 (How I want the final data).

abc123 | R1 |
partname1 | R2 |
partname1 | R35 |
partname1 | R42 |
partname2 | C2 |
partname2 | C5 |
partname2 | C7 |

I have been trying the "Text to Columns" routine, and have managed to get it to produce:

partname1 | R1| R2| R35| R42 |

for each row.

Now what I want is to get the data in the row into individual rows of part name and single component name as long as there is data (if the last cell is empty - move to next row) as per E.G.2

This part I am unsure how to do as its not so simple (well - it may be for you lol).

If the "part name" is in cell A1, the "component names" in cells A2, A3, A4 and so on....

I think I want to copy them to a new sheet (given that I do not know the number of cells used per row I think this is best)
so I want to count the number of filled cells after A1 per row.
copy
Sht 1 A1 to sheet 2 A1, B1, C1 and so on for the number of of used cells In A (minus 1 for A1).

Sht1 A2 to sht2 B2
sht1 A3 to sht2 C2
sht1 A4to sht2 D2

Can someone please suggest how I might achieve all this?
Ideally I'd like to create some form of routine or macro that I can configure and use on various spreadsheets as this
would likely reduce the amount of manual operations, although I know little about excel let alone making a macro.

Once I have the data in the format I showed above, I need to do further processing to it but my first step is as above and would appreciate some advice for this.

Thank you.

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A. There's a sample workbook too you could drop your data into and test it out.

Columns to Rows - Part 2

This post was of little use, however I needed to remove the attachment which made the post pointless.

I am thinking more that https://sites.google.com/a/madrocket...ows-to-columns 1a2 is along the right lines.

OH hang on - part 2 is looking better.

Parsebycolumns2 appears to do the trick!

Excellent - yes it does.

Now I need to be able to extract that macro or maybe take everything else out of the downloaded spreadsheet so I can paste
parts lists into this one.

Thank you.

I have bookmarked your pages as a great resource to teach me.

The attached spreadsheet is the result of this.

If you want to post a revised sample workbook with your actual BEFORE/AFTER examples I'll take a look.

Hi, I want to end up with a text file looking like chapter7.txt.

The header lines and end line I can deal with easily.

Using the routines from your site I managed to get Parsed_data.xls made and have added a column with a single " in it
either side of the part name but that's not coming out right so have removed them.

I want to end up with ["" "partname"] (everything between the square brackets - parenthesis included).

However, the chapter7.txt has a line like:

.ADD_COM C1 "C100PF-5%037-COG2" "C100PF-5%037-COG2"

Where I want my data to look like

.ADD_COM C1 "" "C100PF-5%037-COG2"

With extra "" in between the component name and the "partname" instead of duplicated "partname".

This is really a completely new topic. I don't do much MSDOS text exporting, so I can't comment on that. I'll ask some others to take a look.

Thank you.

The things I am struggling with now are:
First I need to swap column A with Column B - I will search for this as I bet its been asked before. (I just cut/pasted column A to C)

1) I want to put the data in the cells in the part name column (B) into parenthesis.

2) Then I want to insert a column between A & B with its data simply being double parenthesis [""].

3) Then I want to insert a column in front of A column with [.ADD_COM] in all cells - this is easy.

4) Then I want to spit it out to a txt file without any tabs or additional characters/spaces in.

5) I am also wanting to remove a single ' from the first component name cell after running the "Text to Columns" routine.
This is because it is already in the first spreadsheet, most likely a result of it being spat out of an MRP/sage system into a spreadsheet.
This I suppose I can search/replace in notepad after its been output.

6) Also some of the component names have a space in front of them that I would like to remove.

I have been playing with the data in notepad and proved to myself that as long as I can get the text in the correct format I can import it into the cad package and save oodles of time, its the excel manipulation that I'm clueless at.

I have attached AfterAddCom.xlsx that shows where I am now, I have done #3.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

This should make it less confusing when re reading this in the future.

I don't think "tidying up", changing comments, replacing sample workbooks or any other kind of editing ... after you have had responses ... helps anybody in the future. As for "less confusing", don't believe that for a minute. I've just read through this thread, as JB had asked for support, and I've found it quite difficult to follow. Half the time, it looks as though you're talking to yourself.

You've got 8 posts and they're all in this thread ... that probably says something; I'm not sure what.

Once you've made a post, leave it alone other than minor changes like fixing spelling mistakes. If you need to add detail, add it later ... don't restructure and change an earlier post.


TMS

I'm glad you have the result you were looking for.

Regards, TMS