Posts by bigdan

    Re: my huge 100K line file keeps freezing


    Quote from snb;633384

    Did you check the amount of tablequeries in the file ?
    Did you check the amount of drawingobjects in the file ?
    Did you check the amount of links in the file (and whether they are valid) ?
    Does the file contain any workbook eventcode or worksheets eventcode ?
    Does the file contain dynamic named ranges ?



    i dont know what all that means or how to do that, but it doesnt look relevant. not using any vlookups or external code.

    Re: my huge 100K line file keeps freezing


    Quote from LocalPeople;633376

    What are you using the file for? At that size, and if you're mostly filtering to create sub-sets, a database program is going to be faster, more reliable, and less likely to go wrong in unexpected and/or catestrophic ways.


    a work assignment. basically checking who the singer of each song in thsi excel file is in a paper list i have, and then putting it into excel.

    I'm working on a huuuge excel file that keeps freezing and is therefore taking me ages to finish working with. I'm hoping someone can advise how to lower the processing time. This file has 100K rows. It even freezes when I'm scrolling! The main actions I'm taking within it, if this is relevant, is that I'm applying a couple filters at a time then putting in information into the first line and pasting that all the way down.


    I was working on another file of the same length last week. That was also very slow in the beginning until I shut off automatic calculations, made them manual. But that hasnt fixed things here.


    Can anyone help?


    Thanks
    Dan

    Re: help with grouping months in a year's pivot table


    thanks Smallman.


    If I knew how to make what I wanted to see, I wouldnt need your help!


    At the moment you can see 2 rows for the date, one saying Group1, below it saying Jan 5 / Jan 20. The next col saying Group 2, below being 4 Feb / 19 Feb.


    Instead I just want ONE row, just showing Jan, Feb, Mar etc. Not Group 1, Group 2, and definitely not the various dates the group is made up of.

    i'm trying to create a pivot table to figure out categories of expenses over the last year. the problem is when i create this pivot table on the horizontal i see alll the days of the year, as opposed to categorized by month. i've tried to 'group' the months together but not very successfully. could someone please break down HOW to do this grouping elegantly?


    i've attached 2 screenshots so you can better advise where the problem may lie.


    thanks!



    Pic 1 - all days given




    [Blocked Image: http://i39.tinypic.com/30b0l54.jpg]


    +++++++++++++++++++++++




    Pic 2 - attempt at grouping gone wrong




    [Blocked Image: http://i42.tinypic.com/v6jcbr.jpg]

    Re: how to import multiple values w/ a vlookup into a single cell


    thanks for this. its close but not exactly correct. with the code given B2-4 are all 220. B2 should be 220, B3 100, B4 70. ie raw mat expense should be raw mat 1 + raw mat 2 etc.


    i also didnt understand your code. do you think you could explain it to me? i guess im not as advanced as some of you :)


    in the first code i didnt get this part:
    TRIM(SUBSTITUTE(A2,"Expense",""))&"*"



    in the second code i was confused from the beginning. id appreciate the help.

    ive posted this question on another forum as well [http://www.mrexcel.com/forum/showthread.php?t=505173]but im not really getting a very informative answer there so wanted to ask it here. hope i can get some help... need this for a job interview :)



    in my data worksheet there was Labour Expense 1 and Labour Expense 2.
    in the main sheet I was dealing with I just had Labour Expense. how would i get the sum of both LE1 and LE2 in there? now of course if i did it one by one this would be no problem but we want this to be in a way that can be duplicated easily, so by using vlookups or sumif statements. b/c in the data sheet there was also raw material expense 1 and RM expense 2, then rent 1 and rent 2, etc. now in this case there was only 1 and 2, but what if there had been a third, is there any simple way of dealing w/ that as well? i guess the simple way of doing this was to say - if in this range (ie column A) there's a value which starts off with "Labour" I want the corresponding figure (in column B) to be added.

    also in the data tab i had values for north, south, and east, but not west. when i created a vlookup to import those values in, west gave n/a instead of 0. which woulda been OK except the main worksheet then tried to calculate a total and average, both of which got messed up b/c of the n/a. what was the right way to tackle this?

    Re: USD $20 negotiable - populate a template from a source file


    thanks so much Wigi!

    I've gone thru this but I dont see the button anywhere? I'm not sure if its just not in there or its not being picked up on my end for some reason. so i havent really been able to do anything yet. i hope you're still checking this forum from home :)

    Re: USD $20 negotiable - populate a template from a source file


    Quote

    So you want to copy the source data to template, but:


    - some columns need to be copied in any case, while there can be a choice for the last column (Value or NoA)
    - you want to be able to select on a certain column, so that only the filtered results are copied over


    Is this true?



    Hi Wigi,

    - Yes, some columns, ie type, name, code# would have to be copied over, and only one of the two numeric accounts (Value or NoA). Actually there are a couple other columns but I didnt include them to make it easy to read. But yes, every column would be copied over except one of the two numeric ones.

    - Yes, only the filtered results should be copied over. Ideally nothing should be done to the source file, every action should be done on the template.

    If you're able to do this, please let me know when you can get this in to me. Also, please confirm that you can do it in such a way that either makes it easy to modify or else you can make some modifications for me.

    Thanks
    Dan

    I need to get this project done asap so hope people can get back to me right away.


    I'm going to be receiving a source file regularly with a bunch of data. This will typically be in the format of:

    Type / Name / Number / Value / NoA (Number of Accounts)

    At the moment the source file is about 400 lines long but I assume it could change over time.
    Now in my template I'd like to keep the column headings of Type, Name, and Number, but dont want both Dollar and Number of Accounts. Those are two different ways of allocating taxes and I can only choose one. So I'd like an option at the top of the spreadsheet specifying which I want to do (Value or NoA) and it to choose accordingly. Finally I'd like to be able to choose what type of fund I'm working with (whether to include just Fund A, or B, or C, or all) and for it to then populate everything.

    As far as creating a drop down that selects how to calc (Value or NoA) i've already done that. But I dont how to create something that will populate from the source file.

    I'm attaching the two files, the source and the template. In the Template workbook, O3 shows the dropdown, which I'd like to choose which type is displayed (Fund A, B, C etc, or in this case labelled CC, Closed Many, etc), while the dropdown at G8 would ideally show what type of calc is done (NoA, or AUM). Either way I'd like the B to F columns to populate.

    After all the columns are populated I'd like the totals to be calculated.


    So that's what I need. Now whatever is written, I'd need it to be something that is either fairly simplistic or can be explained to me, because I will need to modify it to fit my needs. At the moment the file I've uploaded doesnt contain the correct names due to confidentiality clauses. And as I said this file could change in size over time. So it needs to be able to be modified.

    Please get back to me and let me know when you can get the work done. I'd ideally like it within 24 hours, by end of day today would be even better. I think this can be done in 15-60 minutes for those who know what they're doing.


    Thanks
    Dan

    I have attached an excel file to explain what I want. The raw data is on sheet 1 and the way I want it to end up is on sheet 2.


    Basically I have a file with various data on it. I want to just display the date, stock advisor, and ticker. Each row should only have that information.


    Thats the main thing. Ideally I would also like to be able to sort it by the number of times a certain ticker has been mentioned. For example if Google has been mentioned (recommended) 5 times, once each by advisors A, B, C, D, and E, and Microsoft has been recommended 3 times, once each by advisor A, B, and C, when I do a sort I would like the first 5 lines to be for the Google ticker, mentioning which advisor recommended it and on what date. The subsequent 3 lines should each be for Microsoft, each mentioning the ticker, the date it was recommended, and the advisor.


    I'm new here so dont know much about pricing, I'm guessing $10 would be a good figure. Please msg me if interested in doing part or whole of it, or to negotiate price.


    Thanks
    Dan