• 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.

Search results

  1. J

    Faster code by looping

    Thanks Hui, that works. One question, how do I skip rows 15 and 19? There's an Excel =AND formula but VBA's syntax is a little different.
  2. J

    Faster code by looping

    Hi all, this must be a really easy question but I'm just getting started with VBA, and coding in general. I wrote a short macro to perform multiple goal seeks and the below is what I got. Now I'm sure the code can be shortened to a few lines by assigning some variables and looping, and I think...
  3. J

    VBA to transform semi-structured data into pivotable table (Solved)

    Hi folks, I've got a sheet with semi-structured data. This current table can't be pivoted. I'm trying to 'transpose' the data into a table that can be pivoted. That will require a lot of manual work as I've got 276 headers and about 20 data points per header. I did some courses on VBA but this...
  4. J

    VBA code to disable Make Unconstrained Variables Non-Negative in solver?

    Hi all, is there no VBA code to do the above? MSDN help says to use SolverGet but it does not seem to work. Similarly, when I record a macro and run solver, the part where I untick the checkbox does not seem to appear? Anyone got any tips on this? The excel file is at the link and the...
  5. J

    Congratulations Hui 5,000 Posts

    Congratulations Hui!
  6. J

    How do you create this chart?

    Awesome lifesaver!
  7. J

    How do you create this chart?

    Hi all, the chart looks deceptively easy to create, but I can't seem to do it. Tried things like format axis, gaps between axis, overlaps between charts but to no avail. Any form of help is appreciated! The link to the chart is here...
  8. J

    Return a value when value is something (VBA, complex formulas may be required)

    It's returning a nil value now. Is there a formula that will return the value in C12 dynamically based on the values in both I2:Y2 for a specified value between G3:G6?
  9. J

    Return a value when value is something (VBA, complex formulas may be required)

    Hi Suresh, is there a dynamic formula so that when I drag it to the right it will auto fill?
  10. J

    Return a value when value is something (VBA, complex formulas may be required)

    Hi all, I would love for some assistance. I'm not sure if vba must be required or a bunch of formulas is required. This really escapes me so I have no leads. Unlike my usual self which I'll investigate thoroughly. File can be found here http://bit.ly/14aSG9A. Thank you all!
  11. J

    Faster way of re-calculating cells in a sheet with 31685 rows of data

    I'm so sorry, here's the data presented properly Year Month Quarter Project Name Street Name Developer Property Type Locality Total Number of Units in Project Cumulative Units Launched to-date Cumulative Units Sold to-date Cumulative Units Launched but Unsold Units Launched in the Month Fresh...
  12. J

    Faster way of re-calculating cells in a sheet with 31685 rows of data

    hi all, it takes about 20 secs to recalculate this bunch of data when I turn on/off the filters. Is there a faster formula to do this? Probably an array i suppose? The formula in Column 'Fresh launch' reads =IF(AND(J2=M2,M2>0),"Yes","No") ' Year Month Quarter Project Name Street Name...
  13. J

    How to extract numbers from these cells

    Thanks Suresh, how forgetful that there is delimiter that I could use! However, I would like to learn how to get A1 = 1600 to 1700 B1 = 1600 C1 = 1700 using a formula too please if possible :)
  14. J

    How to extract numbers from these cells

    Hi experts, I'd like to extract numbers from these cells. I've searched online but i don't find any guide that tells me how to extract numbers when they are separated by a word. Appreciate any help. Thank you 1600 to 1700 400 to 500 900 to 1000 1500 to 1600 1500 to 1600 1300 to 1400 1200...
  15. J

    Returning a certain number based on the position of a value

    Apologies Hui. Here's the spreadsheet with the data cleaned up. https://docs.google.com/open?id=0BzQyFnW9qIEXU3lMZ3pFMVBQTHM Let me describe my problem. I want B2 in "Populate this" to return a value of "4", or "bidder number 4", or "4th place".. anything that indicates "A.C.T Holdings..."...
  16. J

    Returning a certain number based on the position of a value

    Hi Chandoo experts, I would like to seek help on the above. Let me explain in Sheet 2, A2 has the text "A.C.T Holdings..." in Sheet 1, A1:BBO134 has a whole bunch of data including "A.C.T Holdings..." in cell EN116; EN1 has text in it "bidder 4" What I need Sheet 2, cell B2 to return is...
  17. J

    If there is the word And in a cell, return Joint Venture

    Hi Luke, CSE means control shift enter. Yes FIND works for the data I have, what tweaks do I have to do to the formula if I would like to use SEARCH instead?
  18. J

    If there is the word And in a cell, return Joint Venture

    Hi all, Could anyone help me with a formula that returns "JV" (when there is the word "And" in the referenced cell) or "Non-JV" (when there is no "And") "F.E.Lakeside Pte. Ltd., Fcl Topaz Pte. Ltd. And Sekisui House, Ltd. " JV Mcl Land Limited Non-JV The formula I came up with in B1...
  19. J

    Use of large, index match, or VBA?

    Hi bob, thanks for your assistance but I didn't explain myself clearly and caused you to misunderstand. I've actually kind of found the answer to my question and it can be found using AO4 down: Confirm Control+Shift+Enter...
  20. J

    Use of large, index match, or VBA?

    Hi bob, the data comes from a third party provider and I would love to turn it into a table, but that would be a lot of manual work. Would you have any idea on how to turn it into a table?
  21. J

    Use of large, index match, or VBA?

    Hi Chandoo ninjas, would like to seek your help. I've looked through the above solutions and it's great. However, there's a tweak I'd like to make. If you look into the attached spreadsheet, is there a way to return (for example) the top buyer nationalities without the use of named ranges...
  22. J

    Pivot table: Variable divided by another variable within the SAME yearquarter.

    That was awesome chandoo! Thanks so much. However, it should be Home ribbon > Options under PivotTable Tools > Fields, items, & Sets. Then again, that's a small mistake. Thanks you've replaced 2 hours of work with 5 mins of excel! On another note. Do you intend to come to Singapore?
  23. J

    Pivot table: Variable divided by another variable within the SAME yearquarter.

    Hi all, as above. Pivot table allows "Show values as... % of.. % of parent row total.. % of grand total" etc etc. For my situation however, none of the excel pivot defined functions work. Anyone has any idea insight on whether pivot table has such a function or vba needs to be used? The...
  24. J

    Use of large, index match, or VBA?

    Absolutely great, I can work off this! Kudos to your excel skills
Back
Top