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

0 Members and 1 Guest are viewing this topic.

Offline Zenrei

  • Contributor
  • Posts: 16
  • Country: pr
Re: Anyone wanna take a shot at my Excel problem?
« Reply #25 on: October 19, 2017, 12:56:26 am »
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 ^_^
« Last Edit: October 19, 2017, 03:31:37 am by Zenrei »
 
The following users thanked this post: rx8pilot

Offline rx8pilotTopic starter

  • Super Contributor
  • ***
  • Posts: 3634
  • Country: us
  • If you want more money, be more valuable.
Re: Anyone wanna take a shot at my Excel problem?
« Reply #26 on: October 19, 2017, 03:11:00 am »
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.
Factory400 - the worlds smallest factory. https://www.youtube.com/c/Factory400
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Anyone wanna take a shot at my Excel problem?
« Reply #27 on: October 19, 2017, 03:16:10 am »
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)

« Last Edit: October 19, 2017, 03:18:14 am by sokoloff »
 
The following users thanked this post: rx8pilot

Offline apelly

  • Supporter
  • ****
  • Posts: 1061
  • Country: nz
  • Probe
Re: Anyone wanna take a shot at my Excel problem?
« Reply #28 on: October 19, 2017, 04:03:18 am »
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.
 
The following users thanked this post: Someone

Offline rx8pilotTopic starter

  • Super Contributor
  • ***
  • Posts: 3634
  • Country: us
  • If you want more money, be more valuable.
Re: Anyone wanna take a shot at my Excel problem?
« Reply #29 on: October 19, 2017, 05:03:40 am »
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)

« Last Edit: October 19, 2017, 05:06:10 am by rx8pilot »
Factory400 - the worlds smallest factory. https://www.youtube.com/c/Factory400
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Anyone wanna take a shot at my Excel problem?
« Reply #30 on: October 19, 2017, 11:20:00 am »
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.
 

Offline forrestc

  • Supporter
  • ****
  • Posts: 646
  • Country: us
Re: Anyone wanna take a shot at my Excel problem?
« Reply #31 on: October 19, 2017, 12:15:03 pm »
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.
 

Offline rx8pilotTopic starter

  • Super Contributor
  • ***
  • Posts: 3634
  • Country: us
  • If you want more money, be more valuable.
Re: Anyone wanna take a shot at my Excel problem?
« Reply #32 on: October 22, 2017, 10:22:29 pm »
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  :-DD
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.
Factory400 - the worlds smallest factory. https://www.youtube.com/c/Factory400
 

Offline IanB

  • Super Contributor
  • ***
  • Posts: 11790
  • Country: us
Re: Anyone wanna take a shot at my Excel problem?
« Reply #33 on: October 23, 2017, 01:23:56 am »
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.
 

Offline sokoloff

  • Super Contributor
  • ***
  • Posts: 1799
  • Country: us
Re: Anyone wanna take a shot at my Excel problem?
« Reply #34 on: October 23, 2017, 02:15:27 am »
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  :-DD
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.
 

Offline rx8pilotTopic starter

  • Super Contributor
  • ***
  • Posts: 3634
  • Country: us
  • If you want more money, be more valuable.
Re: Anyone wanna take a shot at my Excel problem?
« Reply #35 on: October 23, 2017, 02:24:22 am »
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  :-DD
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

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


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf