• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a bloated spreadsheet and reporting

Davealot

Davealot

Member
Greetings everyone,
Had a quick question. Is there a formula that will fit my requirements? I'm using a series of vlookups currently, but it's blown my workbook to over 30mb, so that's not gonna work. I'm needing something that will take a part number that I drop into a spreadsheet, reference it against a part list master, and once the part number is found it generates the following 8 columns after it. I have a workbook that has columns "Part Number, Model, Color, Cover, Sub Assy, Chrome, RefID, Line". I would like it to match the part number and pull the remaining columns in one formula, is that possible? Thanks everyone for the help!
 
I'm not saying that a macro couldn't fix the issue, I'm just not entirely sure how to *make* it work lol
 
@Davealot
1st step would try to record Your wanted actions or
do a sample workbook and send it here.
Then it would be easier to make code for You.
 
I've recorded macros, but I'm not sure how to record a macro to search for a part number and once part number is found to return the information in the row. I guess I'm not sure how to return the actual lookup function in the macro, if that makes sense
 
@Davealot
One possible ... press [Do it]-button, right side of Qty.
You can add 'as many rows in yellow as You need.
Hint: be careful then copy+paste, there are extra characters and so on...
 

Attachments

  • Helpbook.xlsb
    38.8 KB · Views: 11
Davealot: I'm not sure of the wisdom of jumping straight to a Macro solution, when your issue can likely be solved with more efficient use of formulas. For instance, instead of doing 7 VLOOKUPS on each item, you can do one MATCH on each item, and then via an INDEX function reuse the result from that all 9 columns. The end result is that your file is 9 times more efficient, and the filesize will probably be a good deal smaller.

Couple of questions on your data, before I give specifics:
1. How many rows of data are in the Toyota Part List Master'!D

2. Your sameple file refers to a User Defined Function (UDF) called PartChange to reformat the TMS Part # from something like "8173048020" to "81730-48020-". A UDF may not be the most efficient way to do this...it might be much more efficient to use a native worksheet function and/or a lookup table to do this. Can you elaborate on exactly what this UDF is doing? Or even post the code here so we can take a look?

3. For each sheet, when you push the End key, what cell does it take you to?
To save memory and reduce file size, Excel tries to store information about the area only on a worksheet that was used. This is called the used range. Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions. You can check what Excel thinks is the used range by pushing Ctrl + End. If you find yourself miles below or to the right of where your data ends, then delete all the rows/columns between that point and the edge of your data:

  • To quickly do the rows, select the entire row that lies beneath the bottom of your data, then push Ctrl + Shift + Down Arrow (which selects all the rows right to the bottom of the spreadsheet) and then using the Right-Click DELETE option.
  • For columns, you would select the entire column to the immediate right of your data, and use the using Ctrl + Shift + Right Arrow to select the unused bits, and then use the Right-Click DELETE option.
(Note that you’ve got to use the Right-Click DELETE option, and not just push the Delete key on the keyboard.)

When you’ve done this, then push Ctrl + End again and see where you end up – hopefully close to the bottom right corner of your data. Sometimes it doesn’t work, in which case you need to push Alt + F11 (which opens the VBA editor) and type Application.ActiveSheet.UsedRange in the Immediate Window and then pushing ENTER (and if you can’t see a window with the caption “Immediate” then push Ctrl G).
 
Jeffrey,
Thank you for the response my friend. I have fiddled with the "match" formula but it brings back an "N/A" response. My match formula would be on the second sheet, formula stating "=match(am,'sheet 1'!A:A,0)". I've never used the Match function so I'm under the assumption that I am doing it incorrectly. Thank you for the additional information about the field range as I was unaware of that and there was a running sum in a row close to 20000 for some strange reason. As for the VBA code, it's used in a separate place in the spreadsheet for trimming down part numbers and putting them in a specific format as I take the part numbers from an order portal, manipulate them to upload into an operating system, and they are also scanned with a check digit, the VBA code removes the check digit and formats the part numbers for easy entry into a separate system.
 
@Davealot
One reason that Your =match(am,'sheet 1'!A:A,0) gives "N/A" is because You have 'extra characters'? Like '8888' is different than '8888 '.
'My version' uses that 'Match' and 'Index' combination.
No need to take care number of rows of formulas.
If You need to change something then You'll change only 'one formula row' and that's all. Someone likes to have a lot of formulas in sheet ... I don't.
 
@vletm, Gotcha my man. I prefer to keep as many calculations out of a worksheet as well, but I'm unfortunately not that great at macros, but yours is working great! Thank you both for your input and help, it's been greatly appreciated!
 
Greetings again, Wondering if you can assist me with one last thing, I hate to bother. If you'll go to the second worksheet, titled "Week1", the columns in green. I'm trying to figure out a way to make a report that will show, by model number, their requirements for that week that will auto generate, or at least require as little of input from someone as these will be passed off eventually for them to use themselves. I've messed around with pivot tables quite a bit, but I can't get the amount of information needed to show to show in a way that's readable on a single/two sheet report.
 

Attachments

  • Toyota Part List Master.xlsx
    254.3 KB · Views: 6
@Davealot
1st I noticed ... a lot of formulas ... of course, I took all of those away!
2nd I noticed ... that all those weeks were same and all rows are unique ...
3rd all 'Qty...' values are same ...
if some values would be different then ...
Which numbers are You looking for?
One more time, please ...
 

Attachments

  • Toyota Part List Master.xlsb
    79.6 KB · Views: 9
@vletm, I apologize, I just tossed an old spreadsheet in there, I pulled the same order for each week, so it has the same part numbers and quantities, I run it that way so any errors stick out pretty easily. Sorry, please see attached, added worksheet titled "Report", something in that format, but it takes the model numbers and alphabetizes them, so that LH and RH parts appear together, by model. Thank you for all of the help I am appreciative!
 

Attachments

  • Toyota Part List Master.xlsx
    255.1 KB · Views: 3
@Davealot ...
1st I noticed ... 'Report'-sheets and everything else
were like Your previous file. ... a lot of formulas ...
of course, I took all of those away! As previous time.
2nd ... 'Report!B2' link were ... to 'Week1!A5' ? hmm ?
3rd ... You maybe asked something that
I start to build to 'Report' - not sure at all!
Model - Modification - Qty ... if need something else ...
ask/modify/ need more details of Your vision.
 

Attachments

  • Toyota Part List Master.xlsb
    97.2 KB · Views: 6
@vletm Someone likes to have a lot of formulas in sheet ... I don't.

Nor do I. But in this case, we still don't know how many formulas - or what size lookup range - we are dealing with. So there may not be a lot. And it all depends on what your definition of 'a lot' is. My definition of 'a lot' is that performance starts to degrade. If there's no degradation, then I'm quite happy to have formulas in my sheet, rather than move to a macro-driven solution for no noticeable gain. Why?
  • They are generally far more robust compared to macros (e.g. in this particular case, if a user was to insert additional rows or columns, a formula-driven solution would still work, whereas the Macro would return incorrect results with no warning.)
  • You can retrospectively change something in your lookup table, and the results will be updated automatically. (Might not be a consideration here, but it is a key requirement for many other projects)
  • Formulas are generally better understood by end users than macros. Meaning more end users are likely to be able to diagnose and troubleshoot potential issues with a formula-driven solution than a macro-driven one.

@Davealot Out of curiosity, how many rows of data are in the Toyota Part List Master tab, and how many rows of data are routinely in your Week1, Week2 reports?
 
It's bad formula choice, for sure. But 'many' formulas <> 'too many' merely based on some arbitrary number of formulas.

In other words:
  • You could do this with formulas, and no macros
  • You could do this with a macro, and no formulas
  • You could probably do away with lots of formulas by leveraging off PivotTables (although Davealot's end requirements aren't that clear to me)
  • You could probably use PowerQuery and no formulas.
  • The user would probably not notice any difference whatever approach you took.
(I say 'probably' in that last bullet point because I still don't know how many rows in the lookup table, and how many lookups are being done. Certainly there is no discernible difference given the amount of formulas in the sample file)

So no, I don't necessarily believe there are too many, simply based on number.
 
Gentlemen, I appreciate the help, I believe I may have stepped in a bit over my head on that one however. I did fiddle with it for a little while this weekend, on a whim pitched it into access, ran report and designed it how I wanted it and it worked like a charm. I appreciate the willingness to help however, It is greatly appreciated!
 
You must log in or register to reply here.
Back
Top