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).
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.Partname |Component Name |
partname1 | R1, R2, R35, R42 |
partname2 | C2, C5, C7|
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:
for each row.partname1 | R1| R2| R35| R42 |
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.
Bookmarks