### Author Topic: Anyone wanna take a shot at my Excel problem?  (Read 2600 times)

0 Members and 1 Guest are viewing this topic.

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Anyone wanna take a shot at my Excel problem?
« on: October 16, 2017, 01:03:21 pm »
Hi all -

Trying to process some BOM data that come from my EDA software. It needs to be formatted a number of different ways to create the documentation, program the pick and place machine, setup assemblies in accounting, etc.

The output of the EDA software includes our part number and reference designator (among other stuff). It is one row per part and many of the part numbers repeat. The goal is to collapse each part number to a single row with the number of parts total and adding the associated reference designators on new columns on the right.

Column A is the part number that can repeat. Column B is the reference designator that is always unique.

INPUT DATA EXAMPLE:

OUTPUT DATA EXAMPLE:
Column G is the total number of instances of the part number. The columns to the right are the associated reference designators.

I managed to count the number of unique part numbers, but could not figure out how to do anything else.
« Last Edit: October 16, 2017, 01:08:05 pm by rx8pilot »
Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### Brumby

• Supporter
• Posts: 7895
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #1 on: October 16, 2017, 01:57:05 pm »
Hmmmm.....................

... interesting.

#### Brumby

• Supporter
• Posts: 7895
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #2 on: October 16, 2017, 02:25:00 pm »
A simple Pivot Table will give you columns F and G

Getting the list of reference designators (columns H, I, J, K, etc.) will be the fun bit.  I daresay you would prefer them in ascending sequence?
« Last Edit: October 16, 2017, 02:27:45 pm by Brumby »

#### IanB

• Super Contributor
• Posts: 9189
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #3 on: October 16, 2017, 02:43:31 pm »
This would be trivial in a programming language like Perl. Excel isn't necessarily the answer to every problem.

If you want to keep the data in Excel, I would script the solution using VBA or another scripting language. Columns A and B are a key-value table, where the part number is the key and the reference designator is the value. Iterate over the table and push each new reference onto the end of a list keyed by the part number in a hash (dictionary).

When you are done, iterate over the hash by key (part number), and write out the contents of list associated with that key. The "write out" part could include dropping the results into the output table on another sheet in the workbook.
I'm not an EE--what am I doing here?

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #4 on: October 16, 2017, 02:56:11 pm »
This would be trivial in a programming language like Perl. Excel isn't necessarily the answer to every problem.

Agreed - Excel is set up for everything else at the moment so it would be nice to accomplish this task in the same place. I considered using Python to process the CSV data as it comes out of Eagle. I could also write a ULP in Eagle, although that involves a learning curve that offers little future use.

I have never done any VB in excel, but it does it not seem too challenging for someone that can do mid-level Python and C.

The long-term goal is to use something like FileMaker Pro to manage the design/manufacturing process - but that is a huge project.

Fingers crossed that this is easier than it looks. Doing it manually is really slow and prone to errors and I need to do quite a few PCB's in a short period of time. Getting all the data organized for purchasing, assembly, and documentation needs to be streamlined for sure.
Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### Brumby

• Supporter
• Posts: 7895
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #5 on: October 16, 2017, 03:04:19 pm »
Oh, I agree that doing this in Excel is not the best approach - but I look it as an exercise in getting Excel to do things "outside the box" as it were.

Bit busy right now ... but I'll see if I can find some time later today.

#### hendorog

• Super Contributor
• Posts: 1257
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #6 on: October 16, 2017, 04:10:30 pm »
Can mostly be done in a Pivot Table and then an array of formulas.
You end up with a 'spaced out' array of the Reference Designators.

Converting from that into the final result requires each row to be copied, transposed and sorted to remove the spaces, then transposed again and copied back into place. That step would probably be best done in VBA I'd say.

The following users thanked this post: rx8pilot

#### alanb

• Frequent Contributor
• Posts: 316
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #7 on: October 17, 2017, 02:32:09 am »
Try using the Data tab in Excel, it gives you SQL like functionality that should enable you to do what you need. Keep the original data on one sheet of a multi sheet workbook and out put the resultant data onto another sheet.

The following users thanked this post: rx8pilot

#### alanb

• Frequent Contributor
• Posts: 316
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #8 on: October 17, 2017, 02:57:28 am »
After looking again at Excel I'm doubtful that the Data tab functionality is going to help. VBA as suggested previously is probably the best way to go.

#### Vtile

• Frequent Contributor
• Posts: 951
• Country:
• Ingineer
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #9 on: October 17, 2017, 03:02:15 am »
VBA/OpenOfficeBasic and a few FOR-IF routines to parse the data. ..Or that external Python etc. script/program...

Use one tab for input and another tab for output.

..'Protip' for beginning of use the VBA, record macro to see how the cell referencing go.
« Last Edit: October 17, 2017, 03:08:05 am by Vtile »

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #10 on: October 17, 2017, 03:27:10 am »
It seems like Python may be the shortest path to a solution on this one. VBA most likely has the capability according to the responses here and another friend of mine - but it will be a learning curve for me. I am not gifted in Python either, but this would be fairly trivial compared to learning VBA. I was hoping there was some secret function in Excel as there always seems to be, but I guess this is a fairly unique problem.

The only remaining option is a custom ULP for Eagle (the origin of the data). I have rudimentary skills that are enough to get the data that I have although some other BOM scripts are able to group by value like this. I just can't follow the code well enough to figure out how they do it. Anyone have any ULP scripting skills for Eagle? I could scare up some  for a solution that works.

Output from Eagle: Separate files
TOP SMD/P&P parts - part number (attribute), qty, reference designators
TOP Hand place parts - part number (attribute), qty, reference designators
BOTTOM SMD/P&P parts - part number (attribute), qty, reference designators
BOTTOM Hand place parts - part number (attribute), qty, reference designators
WHOLE PCB All parts - part number (attribute), qty, reference designators

That data can be read by existing spreadsheets, assembly, and purchasing software to create the various docs I need. Doing all this manually is not too bad when you do a couple of simple PCB's. I am now at the point where I am dealing with about 14 different designs and over 300 unique parts and still a 1 man shop. I have to design, order, inventory, assemble, test, etc.....the whole enchilada and it is a MESS!

Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### Vtile

• Frequent Contributor
• Posts: 951
• Country:
• Ingineer
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #11 on: October 17, 2017, 03:31:27 am »
Once you have learned the basics for VBA, you can't use excel without.

The following users thanked this post: rx8pilot

#### Avacee

• Supporter
• Posts: 291
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #12 on: October 17, 2017, 03:53:23 am »
You don't have to use VBA to achieve this - it can be done with normal excel functions
As you go to the right you are in basically looking for the Column B row that matches the nth item on Column A that matches your column F value.

Option A) Using INDEX+SMALL/MATCH  .. Inspiration =  https://exceljet.net/formula/get-nth-match-with-index-match

Option B) Using a vLookup .. Inspiration = https://exceljet.net/formula/get-nth-match-with-vlookup

I've PM'd you a link to an excel sheet on my OneDrive that shows you both methods.

+1 on Learning VBA within Excel

« Last Edit: October 17, 2017, 03:59:02 am by Avacee »

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #13 on: October 17, 2017, 05:02:41 am »
That is awesome.....just looked at it and will be playing tonight!

Thanks for the tip.
Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #14 on: October 17, 2017, 05:13:22 am »
Question:

To derive column E in your example, did you just filter the repeating data from the original data in Column B? It looks like you manually entered that part and then automated the reference designators being populated on the rows with VLOOKUP and INDEX-SMALL functions.

It looks promising for sure. I totally believe that learning VBA is a great idea, but my plate is really full and I am already juggling a dozen pieces of pro software on any given day. My brain is begging for mercy.
Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### Avacee

• Supporter
• Posts: 291
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #15 on: October 17, 2017, 05:20:25 am »
Column E is a copy+paste of Column B with the Duplicates Removed (Data Tab -> Data Tools -> Remove Duplicates).
Then set the formulas in the top left cell and dragged

Yes, it's a manual step (https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) but quick and dirty for this demo.

If you need an automatic Remove Duplicates then that's achievable too
« Last Edit: October 17, 2017, 05:23:31 am by Avacee »

#### 691175002

• Regular Contributor
• Posts: 64
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #16 on: October 17, 2017, 05:41:03 am »
You can do this purely with vanilla formulas, but the problem is that Excel really does not deal well with data of unknown length.  In a worst-case scenario you could have say a 2000 line BOM of the same part, in which case you would need to prefill >2000^2 cells with formulas to accomodate the potential output.

The second problem is that each cell is independently calculated, so intermediate results that can be reused end up getting calculated n^2 times.  This task should be O(nlogn) but in excel starts to look like O(n^3) or worse.  Toss in the ridiculous memory overhead of Excel and a script that would ordinarily run instantly will end up consuming several minutes and >8gigs of ram to update.

If you know for sure that there will be say less than 400 lines in the BOM and no more than 20 of a single part then the formulas might be good enough.  Otherwise this task is quite simple in VBA since they already have For...in loops and Hashtables available.
« Last Edit: October 17, 2017, 05:45:47 am by 691175002 »

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #17 on: October 17, 2017, 06:31:12 am »
The manual de-duplication is pretty easy(even if done manually) and you provided a rather simple solution for the reference designator column.

A big leap forward from the 100% manual method for sure. In addition to being slow, it is pretty easy to make a mistake. Thank you very much for working out the demo.

You can do this purely with vanilla formulas, but the problem is that Excel really does not deal well with data of unknown length.  In a worst-case scenario you could have say a 2000 line BOM of the same part, in which case you would need to prefill >2000^2 cells with formulas to accomodate the potential output.

The second problem is that each cell is independently calculated, so intermediate results that can be reused end up getting calculated n^2 times.  This task should be O(nlogn) but in excel starts to look like O(n^3) or worse.  Toss in the ridiculous memory overhead of Excel and a script that would ordinarily run instantly will end up consuming several minutes and >8gigs of ram to update.

If you know for sure that there will be say less than 400 lines in the BOM and no more than 20 of a single part then the formulas might be good enough.  Otherwise this task is quite simple in VBA since they already have For...in loops and Hashtables available.

At the moment, I am counting on modest sized BOM's with a long term goal of escaping Excel completely in favor of a custom solution in Filemaker Pro (or similar). There I can send in raw data and automate nearly every subsequent task from PO's to assembly to invoices to stock alerts - but that is the \$50k solution.

Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### RVergo

• Newbie
• Posts: 2
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #18 on: October 17, 2017, 07:12:47 am »
I took a look at it just for fun.
Here is what i have come up with.
i cant attach the excel file, so i have ZIP it

I hope it helps you.

Code: [Select]
Sub CreateUniqueList()Dim xRng As RangeDim xLastRow As LongDim xLastRow2 As LongDim xCollumToWrite As IntegerDim i As Integer, j As Integer, x As IntegerOn Error Resume Next' Make list of Unige items in collum A and put them in collum dxLastRow = Range("A2").End(xlDown).Row + 1 'Changes A to other collum if needeSet xRng = Range("A2:A" & xLastRow) 'Changes A to other collum if needeIf xRng Is Nothing Then Exit SubOn Error Resume NextxRng.Copy Range("D2") 'Changes d to other collum if needeActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo 'Changes d to other collum if needexLastRow2 = Range("D2").End(xlDown).Row + 1 'Changes A to other collum if neede' Count amount of the same onesFor i = 2 To xLastRow2 - 1 '    xCollumToWrite = 5 ' Means collum e Changes if neede (It is ware the recult is printet)    x = 0    For j = 2 To xLastRow - 1        If Cells(i, 4).Value = Cells(j, 1).Value Then            x = x + 1        End If    Next j    Cells(i, xCollumToWrite).Value = xNext i' Make the list of postionsFor i = 2 To xLastRow2 '    xCollumToWrite = 6 ' Means collum F Changes if neede (It is ware the recult is printet)    For j = 2 To xLastRow        If Cells(i, 4).Value = Cells(j, 1).Value Then            Cells(i, xCollumToWrite).Value = Cells(j, 2).Value            xCollumToWrite = xCollumToWrite + 1        End If    Next jNext iEnd Sub

#### apelly

• Supporter
• Posts: 1028
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #19 on: October 17, 2017, 07:36:57 am »
I'd rather a Google clue, link, or some theory than "do this" (generally)

#### Brumby

• Supporter
• Posts: 7895
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #20 on: October 17, 2017, 12:41:18 pm »
Seems I missed the boat here.

VLOOKUP was definitely one formula I was going to consider - but as others have chimed in, I will apologise for not contributing and get back to the things that demanded my attention.

#### Votality

• Contributor
• Posts: 32
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #21 on: October 18, 2017, 06:52:03 pm »
Excel can definitely do it, if with nothing else a vba module can, will i be working it out for you for free.. no

Sent from my iPad using Tapatalk

#### sd

• Supporter
• Posts: 20
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #22 on: October 18, 2017, 08:58:49 pm »
If you're using Eagle and part number == value , when exporting the BOM to csv you could try selecting "Values" instead of "Parts" under "List type".

After importing the csv to excel, you'll have all the designators in a single cell separated by commas. Then you could use "Text to columns" with "comma" as separator to split the designators on separate columns.

#### rx8pilot

• Super Contributor
• Posts: 3376
• Country:
• If you want more money, be more valuable.
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #23 on: October 19, 2017, 04:35:26 am »
VLOOKUP was definitely one formula I was going to consider - but as others have chimed in, I will apologise for not contributing and get back to the things that demanded my attention.

I have used VLOOKUP for a number of things in this same project - but it did not occur to me that it would help in this case.

You don't have to use VBA to achieve this - it can be done with normal excel functions
As you go to the right you are in basically looking for the Column B row that matches the nth item on Column A that matches your column F value.

Option A) Using INDEX+SMALL/MATCH  .. Inspiration =  https://exceljet.net/formula/get-nth-match-with-index-match

Option B) Using a vLookup .. Inspiration = https://exceljet.net/formula/get-nth-match-with-vlookup

This goes a long way for sure. My BOM are simple enough to prevent this from becoming too brittle. I was able to pretty quickly make a template that looks at the imported CSV data and arranges it in the format needed. So far, no VBA required although I can see how it would be a goto if I did not have the learning curve to tackle. With about a dozen new designs in the works.....this will save time and prevent mistakes.

All I need to do now is calculate the QTY column. That part is pretty easy. Once that column is populated, the rest of the sheet can reference that data and format it in all the ways I need to keep track of an entire project from purchasing to assembly.  Hoping to be able to generate RFQ's for DigiKey and Mouser based on this data. A big goal is to be able to key in the desired number of PCB's and the system will calculate the total number of parts needed to buy minus the current inventory while applying basic rules like minimum purchase qty and safe over-stock. The inventory and assembly parts were built about a year ago, but I still need to make the RFQ section.

At the moment - I have to manually create orders for DigiKey, Arrow, Mouser, etc - there are about 250 unique active parts to manually analyze and copy/paste into DigiKey - that is bad. Very bad.

If you're using Eagle and part number == value , when exporting the BOM to csv you could try selecting "Values" instead of "Parts" under "List type".

After importing the csv to excel, you'll have all the designators in a single cell separated by commas. Then you could use "Text to columns" with "comma" as separator to split the designators on separate columns.

I looked at that ULP and there were a few problems right away.....
I needed to separate the TOP, BOTTOM, and hand placed parts for assembly planning and documentation. I also needed to use our in-house part numbers that are an attribute for each device. I don't use the 'VALUE' in Eagle. My library is setup so that I choose the values from a list of attributes that are assigned to a part number. That way, I can pick an 0402 resistor with a specific value and when I spit out the BOM - it goes directly into the P&P and inventory modules automatically. This setup is great because when I finish a design and submit to fabrication - I have the P&P programmed and the DigiKey order really fast. With these updates - even faster.

I looked at the ULP ode for this one and a few others, but it was pretty clear that I would need to spend some time learning. I could not quickly deconstruct the code to modify it to read our custom attributes. In choosing my battles - Excel seemed like a shorter path to victory.

Factory400 - the worlds smallest factory. http://www.youtube.com/c/Factory400

#### Zenrei

• Contributor
• Posts: 16
• Country:
##### Re: Anyone wanna take a shot at my Excel problem?
« Reply #24 on: October 19, 2017, 11:49:46 am »
Okay good sir
many ways to skin this little cat.
I've had a similar problem in the past and i solved it  in excel with sables, pivots and the likes.
ill shortly post you an answer

Smf