MS Excel / VBA help thread

Posted on
Page
of 92
  • However, I'd like to be able to search for the character, delete it, etc without using VBA. I can't find an Excel version of ChrW (the VBA function for returning a character from unicode). Is there one, is there any other way of identifying this character without already having it?

    If they're numeric codes, you can do a '-0' (zero) on them to turn them into numbers once they're pasted in. This will remove any formatting differences

  • Does Sumifs(etc only work on a certain number of rows?
    Do the ranges need to be sorted?

  • Does Sumifs(etc only work on a certain number of rows? Effectively, No ( Actually Yes, but only limited by the no of rows your version of excel can handle)
    Do the ranges need to be sorted? No

  • No but the ranges need to match in size.

    Depending on your complexity, you could make the data a table. Then the header name becomes the range and you can sumif(Brian=yes And Derek=156)

    If the makes sense?

    (Love a table)

  • They are slow though. If you have loads of them with lots of different conditions and sizeable data sets it will be a pretty slow spreadsheet.

  • 150k rows. It's strange, it works for most cases but has one exception.

  • You want to sumif on 150k rows?

    Kiss goodbye to your computer.

  • 150k rows is starting to push it for excel in my experience, though I'd expect a sumif to work, just be slow. Paste the formula? (might not help, as obviously dependent on the rest of the sheet)

  • It's way worse. Got approx 200 different Sumifs on those 150k rows which are all in turn driven by 3 different if statements.
    It's slow. Pivoting the data has helped but won't apply to all cases.

  • Jesus. Have you put aside the rest of your day?

  • I could divide the effort by 12 if I just check one month's worth of data...

  • Rest of your week.

  • If you *really* want to kill your PC, try SUMIFS with nested arrays.

  • could you vba then hard code each step of that sumif's? i.e. change the "=sumif(..." to "=sumif(" then replace "=" with "=" one cell at a time via vba?

    I think (without proof) that doing one at a time would be quicker than trying to do them all at once, depending on dependencies. You may get something from hard coding the if's as well once they're calculated.

  • Might not make a difference so don't spend ages on it. Calculation trees are a bit of a mystery at times ...

  • If you're using vba, why bother with SUMIF formulae at all?

  • Pivot tables might help - they're a lot more efficient at calculating things

  • 150,000 lines isn't too bad. It'll take a while but unless you're regularly recalculating it probably isn't worth spending too much time trying to make it more efficient.

    Sticking it in Powerpivot and using =calculates is probably the best way to go if you know how to use it.

  • Or vba chop the data up so that you don't need any sumifs - just sums. Don't know how viable that is, but you can vba autofilters on tables, then use subtotal(..., 109) to get the sum ignoring the hidden rows .

    You'll have to vba the answer of subtotal away somewhere.

    Again, can't prove that this would increase performance, but it feels like it might.

  • One Sumifs still returns zero even though a manual check confirms there is data. No trailing spaces etc. Oh well. It runs, takes around 5 mins to refresh.

  • Is it filtering on a date? I seem to remember the exact syntax for that is a bit weird and always takes a couple of goes to get right. The > or whatever needs to go in speechmarks which is a bit strange.

  • Moar excel/logic help required.

    I have a table of contractor placements, each placement is a row and has a unique ID. Next to that ID is a column called Extensions. The format of the extension is to give the placement ID of the previous placement with EXT1, EXT2, EXT3 so on after it.

    As each placement has a £ value, what is the easiest way to link them so I can sum the full contract revenue?

    e.g.

    Col A Col B
    Placement Ext ID
    55124 53355Ext1

  • sounds like a wildcarded sumif is in order. If you can provide a few rows of data we can sort it (in the next few mins :)) .

  • I'm using Visio 2010 to do some early project planning (the brainstorming bit). I want to export the text with associated hierarchy so am using the export function. I select to export to but it is kicking out an XML file. header of the XML file is:

    Workbook xmlns:html="http://www.w3.org/TR/REC-html40"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:bs="http://schemas.microsoft.com/visio/2003/brainstorming">

    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"/

    suggesting it is for Excel. How do I get it into excel?

  • Looks like that XML scheme is called SpreadsheetML which you should be able to import into Excel somehow.

    If not, could you export direct to Excel from Visio? (Tools|Export to Database, then choose Excel)

  • Post a reply
    • Bold
    • Italics
    • Link
    • Image
    • List
    • Quote
    • code
    • Preview
About

MS Excel / VBA help thread

Posted by Avatar for mattty @mattty

Actions