0

I am exporting a parts list from a schematic program. I need to be able to sort in a specific order to group the components by type and then by part number. I have that figured out.

What I need to do now is add a new number automatically that will group all common part numbers with the same new number with some numbers at a predefined position. We have common parts on all parts lists.

IE All capacitors with xxx-xxxxx1 will be item 10
All capacitors with xxx-xxxxx2 will be item 11 etc.

I would also like to be able to insert x# of blank numbers between groups. Example: Caps from 10-30 then diodes would start 5 after that at 35.

Is this even possible?

I am trying to automate the generation of parts lists so that all someone has to do is push one or two buttons, the macros/VBA code is run and the resulting report can be uploaded into the system.

This is what I would like it to look like at the end. The only difference between 'before' and 'after' is the addition of the position number for the final report having all the same part numbers assigned the same position number.

part number ref value position description
550-2127-476 C57 47uF 15 CAP,CHP,TANT,L-ESR 47UF
550-2137-103 C14 100pF 16 CAP,CHP,CER,X7R,100PF
550-2137-103 C16 100pF 16 CAP,CHP,CER,X7R,100PF
550-2137-103 C14 100pF 16 CAP,CHP,CER,X7R,100PF

patRc
  • 3
  • 2

1 Answers1

0

Since you seem determined not to show 'before' and 'after' I am guessing you start with: 

550-2127-476 C57 47uF CAP,CHP,TANT,L-ESR 47UF
550-2137-103 C14 100pF CAP,CHP,CER,X7R,100PF
550-2137-103 C16 100pF CAP,CHP,CER,X7R,100PF
550-2137-103 C14 100pF CAP,CHP,CER,X7R,100PF

and want to end up with: 

550-2127-476 C57 47uF 15 CAP,CHP,TANT,L-ESR 47UF
550-2137-103 C14 100pF 16 CAP,CHP,CER,X7R,100PF
550-2137-103 C16 100pF 16 CAP,CHP,CER,X7R,100PF
550-2137-103 C14 100pF 16 CAP,CHP,CER,X7R,100PF

I obtained one from the other without VBA by assuming 47uF is in A1 and there is a table_array I called Larry: 

1   10
2   11
3   16
6   15

with the formula: 

=REPLACE(A1,FIND(" ",A1,FIND(" ",A1,14)+1),1," "&VLOOKUP(MID(A1,12,1),Larry,2,0)&" ")
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Sorry for the confusion, it was hard to get the fields to line up correctly just showing the "after". The "before" is the same but without the position #. This list can be long, the part # can be any of over 10,000 I was looking to run a compare, assign the same position # to all items with the same part #. I would like to automate the insertion of skipped #s (example after every 5 #s skip 2 then 5 more) later if parts are added to the schematic, there will be areas to put them so they are still in their family grouping (caps, diodes, etc.) I hope this helps. – patRc Oct 20 '15 at 17:20