MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to re-format Excel BOM output from PCB or Schematic Design Software

Posted by Tim on May 02, 2001 6:15 PM

Most PCB or Schematic Design Software on the market today has the ability to output a Bill Of Material (BOM) in an Excel format. Problem is that re-formatting is required to consolidate component reference designators by Part Number and adding the Qty, is a necessary, but tedious process. Can anyone share a macro that they created to simplify this process?

Posted by Kevin James on May 02, 2001 7:03 PM

2nd fiddle


If you don't receive a viable response, I would be interested in seeing what I can do for you.

I am unfamiliar with the industry you reference and I also am NOT a VBA programmer. I am very good with concat formulas which sounds like it might do the job.

If you are interested, please email me a sample of 10 records in an Excel sheet and indicate how the raw data is to be manipulated.


Posted by Mark W. on May 02, 2001 7:24 PM

Indented list or single-level explosion?

Posted by Dave Hawley on May 02, 2001 11:04 PM

Hi Tim

I am not familiar with the type of re-formatting you require, but below is a small macro I wrote for a programmer in England that wanted to convert imported numbers from :
1 10 100 1000
A0001 A0010 A0100 A1000

Let me know if it's close and I can modify it for you. This particular macro requires the imported numbers to reside in Column A and Column B to be empty.

Sub NumberConversion()
'Written by OzGrid Business Applications

'Converts all numbers below 10000 to 5 characters.

Dim rConvertRange1 As Range

Set rConvertRange1 = _
Range("B1", Range("B" & Range("A65536").End(xlUp).Row))

rConvertRange1.FormulaR1C1 = _

rConvertRange1.Offset(0, -1) = rConvertRange1.Value
Set rConvertRange1 = Nothing
End Sub


OzGrid Business Applications

Posted by Tim on May 03, 2001 9:37 AM

Specifically, take the output
C1, 10395
C35, 10395
C4, 10395
RE33, 11079
RE4, 11079
RE5, 11079

C1, C4, C35 10395 3pcs
RE4, RE5, RE33, 11079 3pcs

I hope this answers your queston?


Posted by Mark W. on May 03, 2001 2:48 PM

Are there always groups of 3 or is this a coincidence?
Looks like you want to sort by the left-most
alphabetic characters of the 1st column, and the
the right-most numeric, right?

Posted by Tim on May 04, 2001 10:18 AM

Nope, this is just a conincedence. But there will always be only alpha characters in front of only numeric ones. The number of alpha's, and the number of numerics following the alpha's, vary.
Furthermore, C1, C4, C35 is in one cell.

Thanks for your help. Are there always groups of 3 or is this a coincidence?