Search results

Please see the attached file. I have a range [Amount} defined as B2:B11. Whenever there's a "Y" in column C, Reset, I want the sum of all the Amount cells up to, and including, the row with the "Y". For each successive value of Y in Reset, I want the sum of Amount NOT INCLUDING the prior...

I consider myself a fairly advance user, but I need help understanding what this formula is doing =SUMPRODUCT(--ISNUMBER(--(ROW(INDIRECT(YEAR(StartDate)&":"&YEAR(EndDate)))&"-02-29"))) For StartDate, use 10/1/2001. For EndDate, use 2/1/2014. The correct answer is 3 (2004, 2008, and 2012) and...

I'd to run VBA against some very large datasets (> 1MM rows). Is it possible to leverage the capabilities of Power Query which can handle enormously large files and run VBA code inside of Powe

I'm having trouble getting my head around the "Load" process. I get that while one's in the PQE the data file size is, for intents and purposes, unlimited. But once your transform, Data Model, and everything else is complete and your opt for Load, it seems you're back in Excel which has the...

I must be doing something incredibly stupid. I have a named range with 5 pieces of data, say A1 thru A5. I want to paste this data into another sheet using the Named Range. Can't do it. Any ideas?

I've included a file. Col A and Col B are sets of numbers. Col C denotes which rows to sum using SUMIF. The first formula (cell F3) is =SUMIF($C$3:$C$25,"X",$A$3:$A$25) I want to be able to drag/copy this formula down and have the third argument point to $B$3:$B$25. Using a drag or copy...

I posted this a couple of days ago in the Excel area, and got a couple of Power Pivot responses. My dilemma is that I'm bringing in the EU Date with time, and it's not converting to US. In those cases where there's no time component, the conversion works just fine. Here's what I use using the...

I'm having difficulty converting EU dates (DMY) that include a time component into US (MDY). See the last 2 rows (36 and 37) of the attached file. I want them to resolve to 06/25/2021, but they're staying at 25/06/2021. I've tried a number of techniques (Import wizard), Text to Columns, etc...

When I use ERASE in the forms of: Erase arrTest, or Erase arrTest() the entire array structure is obliterated: the dimensions are gone, it looks just like another variable. Right now, I'm looping through the dimensions and setting the values to whatever fits (0, "", etc.) Any ideas? Thanks...

Say I want to do something with columns 2 through 5. Is there a way to specify Columns(2:5) or something similar? What about a set of non-contiguous groups like 2-5, 7, 8-11, and 15? (can this be done with letters?) Thanks in advance.

My preferred number formatting is #,##0 Is there a way to automate this by "replacing" what Excel says is Number (I have to go in and opt in for Use 1000 separator and set the Decimal places to 0)? I have another which is #,##0;-#,##0;;@ Again, I'd like to do this with one button simplicity...

Easy to do in VBA, not so much in Excel. Often, I put totals in Row 1 followed by a column header, and the data beginning in row 3. I'm constantly having to do something like =countif(A3:A10000,"ValueToTest") knowing in this instance there would never be anything near 10,000 rows. Seems like...

It's taking almost 9sec to open a measly 8mb workbook. I only need to interrogate the data in the WB's 2 tabs. I write nothing to the WB, I don't sort. Nothing. Any suggestions would be welcome.

This is driving me nuts. I'm merging all files with the mask *.txt into one .txt file using a DOS shell command. The command is Shell Environ$("COMSPEC") & " /c Copy C:\TEST\*.txt C:\TEST\CombinedFile.txt " I'd like to use variables for both the From and To environments. Because there's a...

Range A2:A6 contains an integer value Range B2:B6 contains a blank or N value The objective is to highlight the cell in Range A2:A6 having the minimum value, but ONLY using those cells in Col A where its corresponding Test value is blank. So, in this example, the result of the formula s/b 2...

Here's what I want to do 1. Import a large (220MB) pipe-delimited txt file very quickly (this works). Let's say the file has 280,000 rows. At this point I'll have a 1 dimensional array (strData) with 280,000 rows. 2. Now, I want to split each row using the "|" character into another array...

Obvious answer uses the Transitive property we learned early on; you know, if A = B, and B= C then A = C. Could be written as =AND(Cell01=Cell02, Cell02=Cell03) = True For N values, though, is there an elegant using a CSE formula, perhaps? or something else? Thank you, in advance.

I should be able to do this, but I'm having difficulty. I have a range of values (A1:A5). Based on whether the value is Positive or Negative, I want to MULTIPLY the values together. Example 1 2 -3 4 -5 The formula (certainly an array/CSE type, I would think) would arrive at 8 (1* 2 * 4) for...

I hope this question is "in bounds" for this forum. I've taken an awkwardly written VBA-driven process in Access and re-written it in Excel. How awkward you ask? What took 35 min now takes 8 seconds. The "old" Access process sets things like Run Date, File Paths, etc via an input box or by...

I'm attempting to speed things up when searching a multi-sheet workbook, cell by cell, for links within a cell. My approach is to use INSTR to find the value ".xl" within the actual formula text. When I do this cell by cell, I have to use artifacts like cell.formula and cell.hasformula. I'd...

I'm out of my depth here. I'm opening 5 workbooks dim'd as Objects (the code lives in an Access module, that's why they're objects). All but one of the workbooks have one sheet. One workbook has 4 sheets. I start the macro with an Excel.Application object and end the macro quitting the...

Situation: 500,000 rows with 12 columns. I need 3 of the columns, and they're not contiguous. I want to load an array(500000,3), but I want to load each dimension separately - is such an ask possible?

I have a common formatting routine that for a given worksheet computes the last row, loops through each row and depending if ROWNUM MOD 2 is 0 or 1 formats the entire row with alternating colors, aka Zebra formatting. Never thought this process was that "expensive", performance-wise, until...

I have a number of stand-alone macros that I run against worksheets in other open workbooks. Let's say my macro workbook is Chandoo.xlsm, one of my many open workbooks is Target.xlsx, and there are many worksheets in Target.xlsx. I'm looking for a dialog process that will prompt me for which...

Does Excel have issues using INDIRECT with a named range defined by a dynamic formula? Example: A1 = Chandoo (and named ChandooNR) CI = ChandooNR =INDIRECT(ChandooNR) returns the value Chandoo. It's what we'd expect. But, when I create a dynamic named range called ChandooDNR defined by...