|
I've been asked to help out with some VBA code. My function needs to (among other things) check if each of the cell values in one column is present in another column, and return the ones that are found as a range. So I did that in the most obvious way (I'm not very familiar with VBA and excel programming) and just loop through everything, using Union to add to the range once another cell is found. Everything worked fine, until it turned out that the other column can contain as many as 6k rows, at which point my extremely naive algorithm of course starts to choke and take forever to complete. So before I start implementing something much more complex, is there a way to use some of the built in Excel functions, which I assume would be more optimized already? Countif, for example, seems to be able to very quickly check if one of the values is found, but I don't know how to neatly use it with the rest of my function. TIA.
|
#
¿
Jun 10, 2010 16:45
|
|
|
|
| # ¿ Apr 27, 2024 17:19 |
|
|
TheAngryDrunk posted:Isn't that what he needs? Maybe I don't understand the question. Yeah, I need to return the range of the column A cells which were found in column B (could be in another sheet or workbook). Sorry if I phrased the original question poorly. The data are numeric IDs, and are mostly sorted in column B but this can't be relied on unless we do the sorting. This isn't really an algorithm question as I could just use binary search, which I think would be fast enough. I mainly wanted to avoid reinventing the wheel and overcomplicating the macro if some built in functions could do the job. Which, thanks to the suggestion above seems possible. I also found that somebody already wrote a function that searches a given range for a specific value, and returns the range of the cells where it was found.
|
|
#
¿
Jun 11, 2010 09:07
|
|
|
Is there a way to get a 100% stacked column pivot chart to display the percentages within the column properly? There recently was a question about pivot charts and the solution was basically to not use pivot charts, and while that would work in my case as well, it would be quite ugly compared to just fixing this one problem. I made a simple example that illustrates my setup and my inability to get this to work. This is in Excel 2010, but the solution should work in 2007 as well.  The chart itself looks correct, and for May the labels are fine as well, but the rest is crap no matter what I choose in value field setting (% of row, column, total, etc). Any ideas?
|
|
#
¿
May 14, 2011 19:01
|
|
|
My bad, I didn't make this as clear as I could have. The 33% Sum of Peach in my first chart is probably the percentage of total peach sales per month (9 peaches sold totally, 3 per month), while the bars accurately represent the breakdown of monthly sales by fruit type. Which is what I'm actually or trying to plot, how much of the monthly sales each fruit represent. I think the actual graph shows that correctly, but I can't get the labels to properly show the corresponding percentages. Here's the same chart but with the sums, where everything looks fine: So in March, 7 items were sold, so the 3 peaches and apples should be ~43% each, with pears making up just 14% of that month's sales. I can't get the labels to say that. Hope this clears up my issue.
|
|
#
¿
May 15, 2011 00:43
|
|
|
Aredna posted:The other option is setting up a pivot table formula. If the columns are named apple, pear, and peach then the formula for peach percent would simply be: Peach / (Apple + Pear + Peach). Then instead of putting the 3 value columns in your pivot you put the 3 formulas in your pivot table. They will accurately sum up the totals for each grouping, which in this case is month). I can't reorganize the data as was one of esquilax's suggestions, but I didn't use a pivot chart instead and then made some calculations based off it. This formula suggestions sounds like a good option though, I'll give it a try. Thanks everyone!
|
|
#
¿
May 17, 2011 00:34
|
|
|
This is stupid but it's just driving me nuts. What's the best way to convert dates like this: Dec 18, 2015 5:09:53.0 AM into an Excel date? I got as far as converting that into "18-Dec-2015" which is supposed to work with datevalue but doesn't.
|
|
#
¿
Feb 12, 2016 17:27
|
|
|
I'm trying to build a report with PowerPivot that would calculate some values based on percentages allocated to different categories, and I'm almost there but can't get it to aggregate correctly. Here's a simple example that I'm using to figure this out: So out of $1000 apple sales, 50% would be fresh apples, 50% of which would be in the western region, for example - assuming the splits are equal here, 250 will be fresh in the west. The formula I'm using right now works perfectly for the individual items and row sums, but not when summed across items or aggregated somehow. code:
|
|
#
¿
Jan 10, 2017 18:04
|
|
|
No idea how to work with that plugin unfortunately. But a quick update in case anyone finds themselves with a similar situation. It was solved with another SUMX which I think had to do with introducing the appropriate row context so that only the appropriate rows were summed up in the related tables for each fruit: code:
|
|
#
¿
Jan 12, 2017 10:27
|
|
|
|
| # ¿ Apr 27, 2024 17:19 |
|
|
This is pretty straightforward but I was having difficulty explaining this to google. With a pivot table (and I assume that would work with power pivot, SSAS which is what I really need), is there a way to break down only some measures in one table, like so: It makes no sense (at least in this example) to split number of stores or employees by the product type, but it does for revenue. Of course when I add product type to columns, it splits all measures, not just sales.
|
|
#
¿
Jun 28, 2018 13:15
|
|