Anyone wanna take a shot at my Excel problem?

[rx8pilot]

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.

[Brumby]

Hmmmm.....................


... interesting.

[Brumby]

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?

[IanB]

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.

[rx8pilot]

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.

[Brumby]

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]

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.

[alanb]

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.

[alanb]

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]

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.

[rx8pilot]

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!

[Vtile]

Once you have learned the basics for VBA, you can't use excel without. 

[Avacee]

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

[rx8pilot]

That is awesome.....just looked at it and will be playing tonight!

Thanks for the tip.

[rx8pilot]

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.

[Avacee]

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

[691175002]

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.

[rx8pilot]

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.

[RVergo]

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 Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim xCollumToWrite As Integer
Dim i As Integer, j As Integer, x As Integer
On Error Resume Next

' Make list of Unige items in collum A and put them in collum d
xLastRow = Range("A2").End(xlDown).Row + 1 'Changes A to other collum if neede
Set xRng = Range("A2:A" & xLastRow) 'Changes A to other collum if neede
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2") 'Changes d to other collum if neede
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo 'Changes d to other collum if neede

xLastRow2 = Range("D2").End(xlDown).Row + 1 'Changes A to other collum if neede

' Count amount of the same ones
For 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 = x
Next i


' Make the list of postions
For 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 j
Next i

End Sub


[apelly]

Are you are looking for the subtotal() function?
https://support.office.com/en-us/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939

[Brumby]

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]

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]

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]

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.

[Zenrei]

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

[Zenrei]

i solved this issue a few years back for my BOM's
i have to dig the files up. i managed it using SQL. i think Microsoft query has some basics to it that are roughly the same.
this was all done in excel
Basically you make an SQL query to provide you unique 2nd_column values in the specific case of the 1st_column value being X
then you left join this table view to another view of the same table, only displaying unique numbers from the same table, different instance, then (this is the tart i forget) use the unique names as headers for each individually packed column, then you tell SQL to bind these colums together in a table, and then transpose. im still digging for the file...this was back in 2009 old schtuff

Hm i found multiple ways of doing this, however all of them require to have finite  knowledge of all  Reference numbers (wich i i think you do)
attached is the simplest form



I generated 400 entry lines with your part number format (is this...avante O.o) im assuming the middle 3 digit are the sequence number in a BOM, first 3 digits the master product class or region, and the last 4 the actual part numbers.
i also generated random references  as well
Data set tab has the good stuff.

a pivot table sorts the part numbers and gives a count whenever each reference is called
a transformer replaces the data into appropriate references
and a collector generates CSV lines for each part number.
at this point you can copy paste the results of Column D into any excel sheet or txt file and save as CSV
and presto
the.
i could dedicate more time to this and automate the length to be variable and into an SQL self feeding query on the Excel file but..you could do that? right?
FYI file is in Excel XSLX format kindly convert ^_^

[rx8pilot]

Excel can definitely do it, if with nothing else a vba module can, will i be working it out for you for free.. no

I am happy to pay for full solutions and sophisticated designs. This is a matter of muddling through two specific functions in a workbook with 200 functions. This forum is all about 'give and take' - I share hard-earned knowledge as much as I have time for and see others doing the same. The whole community is a lot smarter when we share freely. Everyone's knowledge is hard-earned, yet we all share and we all move up.

[sokoloff]

From one pilot to another, see if the attached does what you want.
(This is a straight, albeit ugly, VBA solution.)

No charge.

Tested on Excel 15.39 on Mac. (.RemoveDuplicates does not work cleanly on a Mac version of Excel)

[apelly]

I just had a better look at this.

I wouldn't do it the way you're doing it. But you most certainly can. And in many ways, but you know that by now.

The trouble with spreadsheets is they aren't self documenting. There are things you can to to mitigate the risk, but errors still creep in. Even for careful, advanced users. And they are non-obvious when they happen. If your bank rec. is out by $.10 you'll notice and track it down. No harm done. What if C66 is omitted or the wrong value somewhere along the line? Will you notice? Will it matter?

I'd think of it as a handful of reports generated from the EDA data, some of which may be used in excel at some stage, some go to documentation, some to suppliers... Even though they may be mainly csv files you can still think of them as reports.

I guarantee you can start from knowing zero about any programming language and end up with the data you want, formatted in 5 ways, by picking any language and spending a day on it. The internet has you covered on the language reference front. From then on you just export from EDA and run your software to get the same data every single time. And, importantly, you can actually read the software and easily enhance it as your requirements subtly change. Your EDA software can probably automatically run the script for you too.

I'd use python. It's cross platform, and pretty easy to pick up. Efficiency is irrelevant.

[rx8pilot]

There is little question that Excel is at least pretty close to the worst possible choice. It literally started as 'some quick calculations', then more calcs, then a list of parts tied to the calcs...then a part number generator.....then a parts list with over 3,000 parts from screws to CNC parts to resistors......then a cost estimator.....then a time estimator.....then an assembly reference......on and on and on.

This is a patchwork of efforts designed to get me to the next day and it ballooned out of control. I made a failed attempt at migrating to some manufacturing software but it was only about the process and it defined a way of working that I could not deal with. So the spreadsheets grew and grew.....until today where the whole thing is hanging on by a thread. The only long-term solution is for me to start developing in Filemaker PRO which can easily do all of this stuff ....supporting design level data to sales and contacts. It just can't happen before I need to do a lot more design, testing, assembly, purchasing etc.

The business is only myself full time and my wife is part time so clearly are a limited resource endeavor. We are managing many hundreds of parts, dozens of vendors, 10 products, about 500 customers in addition to design, test, assembly, sales, website, webstore, marketing, trade shows and periodically - a short nap :-)

At this point - I would do nearly anything to bail on Excel, but it is not so easy to do so. Python is my closest option for quick and dirty data processing - but then I have yet another secret step in the process that is already piled high with little 'helper' scripts and band-aids. At least keeping things in Excel means I do not have to remember much the next time I have a new design completed.

Tough choices, I am almost a 'real' business. Just a few more nights and weekends (I have been saying that for a bit too long, lol)

[sokoloff]

There is a shocking amount of business that runs on what engineers would derisively call "hacked together shit in Excel".

I've learned to just make my peace with it and concentrate on the business outcome enabled, not the inherent elegance of the system that is entirely invisible to customers. No one buys from rx8pilot because he has a great back-office procurement automation system. They can't buy if that procurement system fails of course, but anything even mildly working is good enough for them.

[forrestc]

This is a patchwork of efforts designed to get me to the next day and it ballooned out of control. I made a failed attempt at migrating to some manufacturing software but it was only about the process and it defined a way of working that I could not deal with. So the spreadsheets grew and grew.....until today where the whole thing is hanging on by a thread. The only long-term solution is for me to start developing in Filemaker PRO which can easily do all of this stuff ....supporting design level data to sales and contacts. It just can't happen before I need to do a lot more design, testing, assembly, purchasing etc.

I'm experiencing many of the same frustrations you are.   I've even gone as far as evaluating several ERP, MRP, CRM, ecommerce, and similar systems, and just plain haven't found one which will work.   Either they're overly simplistic or require your business to conform to their model (which isn't likely for a 4 person shop) or have a price tag which will knock you on the floor when you see it.

A couple of times I've started down the path of hacking together something in either a web app or some database application, but I quickly realized I have better things to do with my time.   So we're suffering along with quickbooks pro (manufacturing) and some tools I've hacked together to move data from our ecommerce system to quickbooks for accounting purposes.  Wish there was something better I could find and afford.

[rx8pilot]

From one pilot to another, see if the attached does what you want.
(This is a straight, albeit ugly, VBA solution.)

No charge.
c version of Excel)

I am still trying to figure out how to test/use this if that helps you understand my VBA skills 
After reading some introductory articles on what VBA can do in Excel - it is pretty clear that I would benefit from learning it. Even a fairly basic understanding would probable help.

There is a shocking amount of business that runs on what engineers would derisively call "hacked together shit in Excel".

I've learned to just make my peace with it and concentrate on the business outcome enabled, not the inherent elegance of the system that is entirely invisible to customers. No one buys from rx8pilot because he has a great back-office procurement automation system. They can't buy if that procurement system fails of course, but anything even mildly working is good enough for them.

Totally.
In the early days of a business - you have no money and no time on top of not even knowing what you even need. So, like many, you start with a list. The list grows into multiple lists. For a while, it all fits in your head and the spreadsheet is just a helper. Slowly, it grows, the complexity grows and you learn that you have to engineer a dozen solutions to calculate, filter, arrange, and present data after you have gone past the the 'copy/paste' phase of your 'system'. That works for a while, but the complexity continues to grow and you end up at the limitations of your system again. That is when I looked at various commercial systems and custom DB design - about an hour later I have realized that I would need $50k and months of planning to get started. In the end - the system would force us to do everything in a different way.

So, back to excel. Back to YouTube tutorial. Add complexity to the system to solve the problems to get past another week. It is technically working and I make incremental improvements as I have time. This is one of them. After putting in efforts in Eagle and Excel - I am able to run a single ULP and that data is automated into the system for assembly, inventory, and purchasing.


@Avacee worked up a pretty clever solution for the original problem that I doubt I would have figured out on my own.
Creating a list of part numbers with an index number appended and then using VLOOKUP. One of the elements that I never knew was in this formula

Code: [Select]

=IFERROR(VLOOKUP($G3&"-"&COLUMN()-7,$A$3:$C$85,3,FALSE),"-")
The 'COLUMN()-7' had me perplexed for a minute - I never knew you could subtract from the returned value like that. It makes sense after I see it the first time. Using that to reference the previous part number list with the index numbers added is pretty cool. I added a counter to count the total number of instances per part number and connected the raw data to an external source. Onward and upward.....

Thank you all for the tips and some VBA encouragement.

[IanB]

Another useful tool when building up solutions like this is Access. If you have Access in your Office suite, you could check out the ways it might help you, especially in conjunction with VBA, and perhaps linked to Excel.

[sokoloff]

From one pilot to another, see if the attached does what you want.
(This is a straight, albeit ugly, VBA solution.)

No charge.
c version of Excel)

I am still trying to figure out how to test/use this if that helps you understand my VBA skills 
After reading some introductory articles on what VBA can do in Excel - it is pretty clear that I would benefit from learning it. Even a fairly basic understanding would probable help.

This version might be easier. I added a button called "Do It", which will copy from the data you pasted in in columns A and B (and optionally C) into columns E and later.

I'm happy to help more as you need it. Only if it gets really out of hand would I look for any money. Anything that can plausibly be interpreted as "teaching someone to fish" is going to be free.

PS: I also realize that your username is RX8 pilot, probably the Mazda RX-8. I'd originally read it as RV8.

[rx8pilot]

From one pilot to another, see if the attached does what you want.
(This is a straight, albeit ugly, VBA solution.)

No charge.
c version of Excel)

I am still trying to figure out how to test/use this if that helps you understand my VBA skills 
After reading some introductory articles on what VBA can do in Excel - it is pretty clear that I would benefit from learning it. Even a fairly basic understanding would probable help.

This version might be easier. I added a button called "Do It", which will copy from the data you pasted in in columns A and B (and optionally C) into columns E and later.

I'm happy to help more as you need it. Only if it gets really out of hand would I look for any money. Anything that can plausibly be interpreted as "teaching someone to fish" is going to be free.

PS: I also realize that your username is RX8 pilot, probably the Mazda RX-8. I'd originally read it as RV8.

Thanks!

Yes the name refers to an RX8....my last rotary powered car. I am also a pilot and flown a few RV's but not an RV8. King Air is the biggest bird so far.

I really appreciate that you are sharing a starter kit of knowledge on this topic and pointing me to a good starting point.

Sent from my SM-G892A using Tapatalk