Posts by wonka1234

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Instead of using Power query I want to Unpivot data with VBA code

    I have a code that crashes my excel, it takes the data from one sheet and transforms it from a tabular view to vertical. I believe it is slow because it is taking it from a database view to over 45k rows. I get excel not responding error.



    does anyone have any tips for optimizing this code?





    Code
    +---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|  col 1  |      col 2       |      col 3      |    col 4     |    col 5    |    col 6     |    col 7    |    col 8     |    col 9     |   col 10    |   col 11    |   col 12    |    col 13    |    col 14    |   col 15    |   col 16    | col 17 | col 18 | col 19 |    col 20    |    col 21    |   col 22    |    col 23    |   col 24    |   col 25    |   col 26    |   col 27    |    col 28    |   col 29    |    col 30    |   col 31    |    col 32    |   col 33    | col 34 | col 35 | col 36 | col 37 | col 38 | col 39 | col 40 | col 41 | col 42 | col 43 | col 44 | col 45 | col 46 | col 47 | col 48 | col 49 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| oz      | questions forums | excel questions | -540.0689323 | 1543.570725 | -144.7954348 | 2298.261951 | -9019.970702 | -14669.27805 |  2400.31011 | 642.2459256 | 5573.176935 | -19167.60096 | -17070.78503 | 2884.343252 |   2262.2904 |      0 |      0 |      0 | -4866.524221 | -5470.616311 | 6722.889306 | -6749.153327 | 8483.707603 | 7513.052842 | 3768.659869 | 8600.703543 | -8642.799155 | 1322.251923 | -1323.911031 | 3651.739593 | -259.3401823 | 9369.890794 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| oz      | questions forums | excel questions | -325.5117945 | 641.8568521 | -58.21010305 | 977.4626836 | -3505.695779 | -7455.410001 | 777.9341271 | 385.2714806 | 1932.531773 | -8861.136183 | -6679.463121 | 1177.775583 | 881.2548725 |      0 |      0 |      0 | -1813.822794 | -2266.860562 | 2278.669772 | -2361.758467 | 3356.446385 | 2741.992369 | 1461.950204 | 3289.154294 |  -3469.10217 | 804.7989704 | -816.9003551 | 1907.515323 |  432.8435868 | 3074.256129 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| oz      | questions forums | excel questions | -36.42618332 | 65.26139258 | -6.513963305 | 99.38442773 | -435.0485137 | -1047.099199 | 79.09717611 | 39.17283622 | 186.7060257 | -1272.372107 |  -922.750792 | 118.3261869 | 89.60240903 |      0 |      0 |      0 | -210.3183182 | -267.1376584 | 214.6223869 | -280.0000537 | 293.4738136 | 248.5196226 | 144.0720039 | 288.5506437 | -430.0886416 | 81.82868405 | -91.41469707 | 184.4395708 |  44.00977438 | 272.8284368 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
| oz      | questions forums | excel questions | -582.3647427 | 1316.573479 | -165.4555206 | 1925.519573 | -7138.977944 | -17532.94829 | 1404.004642 | 930.6126154 | 3648.013625 | -19585.55834 |  -13758.8035 | 2376.319408 |   1898.9449 |      0 |      0 |      0 | -3625.886962 | -4833.808881 | 4232.764078 | -4449.956081 | 6883.584715 |  5398.12044 | 4048.773452 | 6632.405148 | -7240.871663 | 1959.676076 | -2008.657583 | 4413.431721 |  1360.661107 | 5484.849776 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
+---------+------------------+-----------------+--------------+-------------+--------------+-------------+--------------+--------------+-------------+-------------+-------------+--------------+--------------+-------------+-------------+--------+--------+--------+--------------+--------------+-------------+--------------+-------------+-------------+-------------+-------------+--------------+-------------+--------------+-------------+--------------+-------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+


    and want to make it look like:


  • Excel function to exclude a list dates/columns

    I am forming a chart from sheet A. Sheet B contains all my data.
    I want to exclude a specified date(s).



    Sample data:

    Code
    +---+----------+--------------+--------------+-------------+-------------+-------------+
    |   |    A     |      B       |      C       |      D      |      E      |      F      |
    +---+----------+--------------+--------------+-------------+-------------+-------------+
    | 1 | Date     | 29/03/2017   | 30/03/2017   | 31/03/2017  | 03/04/2017  | 04/04/2017  |
    | 2 | Number 1 | -594590.4649 | -636666.4504 | 795637.1614 | 842563.4322 | 496463.9301 |
    | 3 | Number 2 | 2189587.44   | 1301681.418  | 2080839.353 | 1945335.214 | 2421728.123 |
    +---+----------+--------------+--------------+-------------+-------------+-------------+


    The final output would be me excluding 30/03/2017 , and keeping the rest in my data selected for my chart.
    the issue is that I want to maybe exclude a date in the middle of my selected range. But since this may be a hassle to input a long formula each time into my data selected. I would like to see if there is any formula/function to eliminate a specified date/column. Perhaps manually enter the column you want to exclude in a formula.


    My current range is something like

    Code
    `=Graph!$AB$5:$KA$7`

    But is there a function to exclude one of these columns?


    I can manually select which dates with Ctrl but seems tedious.

  • Userform Tree Structure / Node Help

    Re: Userform Tree Structure / Node Help


    Quote from cytop;775829

    Perhaps if you explained the exact structure of the data and how this will be used then some suggestions might be possible.



    Hi, the sample data layout is like this:


    [TABLE="width: 500"]

    [tr]


    [td]

    Path/Node

    [/td]


    [td]

    City

    [/td]


    [td]

    Area codes

    [/td]


    [td]

    Population

    [/td]


    [td]

    Governers

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/USA

    [/td]


    [td]

    New York

    [/td]


    [td]

    45235

    [/td]


    [td]

    10000000

    [/td]


    [td]

    Mike Ross

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/USA

    [/td]


    [td]

    Miami

    [/td]


    [td]

    235252

    [/td]


    [td]

    2

    [/td]


    [td]

    Louis Litt

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/Canada

    [/td]


    [td]

    Toronto

    [/td]


    [td]

    252452

    [/td]


    [td]

    645

    [/td]


    [td]

    Harvey Specter

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/Mexico

    [/td]


    [td]

    Mexico City

    [/td]


    [td]

    25235

    [/td]


    [td]

    52562

    [/td]


    [td]

    Morgan Freeman

    [/td]


    [/tr]


    [/TABLE]



    I am using it as a dashboard which will be used for different users. So them being able to view the data in an easy and pretty way is a must.
    We want to see the parent and the children nodes and the corresponding city, area codes, pop, and governers data for each node. It should be rolled up into the parent node.
    ie. North America node will have all the data that the countries have individually.


    I need some sort of dashboard view.


    is this detail that would help?

  • Userform Tree Structure / Node Help

    Re: Userform Tree Structure / Node Help


    Thanks so much! Is there a way to identify States or/and even cities (col b) ? perhaps a color ?


    is there a search function I could add?


    I want a user to be able to search a node and identify states/cites/area codes.

  • Userform Tree Structure / Node Help

    Re: Userform Tree Structure / Node Help


    Hey, sorry just to clarify, do I have to create a userform first then run the code? Or just run the code and it should create it?


    Also if I want to add addition columns in my original data such as area codes in the cities, how could I add this to the code?

  • Userform Tree Structure / Node Help

    Hi,


    I have the following data set;


    [TABLE="width: 500"]

    [tr]


    [td]

    Path

    [/td]


    [td]

    City

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/USA

    [/td]


    [td]

    New York

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/USA

    [/td]


    [td]

    Miami

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/Canada

    [/td]


    [td]

    Toronto

    [/td]


    [/tr]


    [tr]


    [td]

    Global/North America/Mexico

    [/td]


    [td]

    Mexico City

    [/td]


    [/tr]


    [/TABLE]


    Anyone have ideas of displaying this data better then just a spreadsheet? My spreadsheet has over 20000 paths(unique) with corresponding values(unique) in the following columns.
    Usually these paths are like above but more extensive, ie it will stay at a continent and go through every country as a child to that parent.


    I would like some sort of roll up display.


    Any help would be appreciated, thanks.

  • VBA, Formatting data from Matrix to Vertical format

    Hi all,



    My current state of macro opens my text files in excel(via file picker) and delimits them.
    However now they are in a matrix format and would like to get them in a more legible format.



    My current format:


    [Blocked Image: http://i.imgur.com/7ckM5dF.jpg]



    Desired Format:



    [Blocked Image: http://i.imgur.com/hmIRubX.jpg]


    this format is continue down the page and repeats.
    A1 is needed and stays the same for several matrices then changes.
    A5:A7 is the same for each batch.
    B5:B7 changes each batch. (first name)
    L10:L24 change each batch, (last name)



    Numbers in matrix will change for each person.
    Values circled is actually what needs to be extracted.







    My current code:




    Any help would be greatly appreciated.

  • VBA, pulling values together to do a comparison

    Re: VBA, pulling values together to do a comparison


    Quote from StephenR;774460

    That suggests it's not finding the value. When it errors, debug and hover over the coloured line and see what the value or 'r' is. I thought you said the tables were identical? If not, perhaps it's not the 4th column.



    Ah I see , that value that r equals does not exist in one of the workbooks, is there a way of identifying these as N/A's in the master sheet?