Search results

Hi, See attached. I added a column on the second tab which is hopefully OK so that I use a SUMIF approach.

Try this: =CALCULATE(DISTINCTCOUNT([UniqueIDProcessID]),FILTER(Table1,NOT(ISBLANK([UniqueIDProcessID])))) (replace Table1 with the table name in your data model)

Hi Veronica, I have a personal budget I made for myself and for split transactions, I just don't bother with the "Split" line as you have it currently laid out. For example, if I go to Costco and buy cat food, dog food and groceries, I list Costco 3 times. My suggestion is to keep it simple...

Try this: Data tab -> From Other Sources -> From Microsoft Query -> In the pop-up ensure <New Data Source> is selected and press OK 1. Enter a name for the data source 2. Select from the driver dropdown "Microsoft Text Driver (*.txt, *.csv)" 3. "Connect..." Select the directory where the file...

maybe this would help point you in the right direction? http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

Hi SirJB7 The formula I posted works on my laptop and I can't get yours to work on mine, which I believe could be due to the "@" referencing that is not a part of XL 2007 tables. I also assumed "mycol" was the first column of the table. try this one...can't test it with the @ referencing in...

something like this should work =INDEX(Table1[#All],ROW(Table1[[#This Row],[mycol]])-1,1) works as intended if the table starts in row 1 by pulling in the data in the row previous row of mycol...

Strictly based on the SQL you posted, a Cartesian Product query would provide what you describe. SELECT B.yr, B.Dept, A.Grade FROM B, A GROUP BY B.yr, B.Dept, A.Grade HAVING (((A.Grade) In (3,4,5,6,7,8))) ORDER BY B.Dept;

kaushik03, You can try this code that I did for someone else about 7 months ago, modified slightly. It updates the chart title with the X values of both series, prefixed by the x-axis label for that data point. You have to roll over the actual columns, rather than the actual x-axis. The...

henksss, The formula provided returns the same value as you are looking for, 3. My comment about the link related to the post prior to mine, not my formula. sreekhosh, thanks

Hi henksss, Array enter this formula (ctrl+shift+enter while in the formula bar): =SUMPRODUCT(--(MMULT((A1:A7),TRANSPOSE(1/(B1:B7)))=1)) The link just provided will colour the duplicates but will not count them into a cell, if that is what you need. Kyle

Hi Sajan, Based on your original post, I understand what you are trying to do, as I tried something similar but with the PRODUCT function. Unfortunately, I could not figure out how to iterate through each row of the virtual array in a single formula (or named formula) without the use of helper...

Hi xcruc1at3r Don't see any screenshots in the file you linked to. Is it bluezone that are you referring to? Kyle

Hi All, Threshold Price Price Difference 0 5 5 100 4 -1 500 3 -1 1000 2 -1 =SUMPRODUCT((B1&#62;A6:A9)*(B1-A6:A9)*(C6:C9)) Where B1 Contains the volume of units, A6:A9 is the threshold range and C6:C9 is the price difference...

AlanS1337, You could also do something like this =SUMPRODUCT((((B2:B4=&#34;A&#34;)*C2:D4)/SUM((C2:D4&#62;0)*(B2:B4=&#34;A&#34;)))) or =SUMPRODUCT((((B2:B4=&#34;A&#34;)*C2:D4)/SUM((C2:D4&#60;&#62;&#34;&#34;)*(B2:B4=&#34;A&#34;)))) Only difference between the two is the C2:D4&#62;0 and...

Luke M, based on the original question, 4 is the correct answer as RichBehan indicated the methodology and the desired answer; summing from top to bottom and 4. Note my formula should read "&#62;=A1" if 20 is to be included. The minimum number of cells to reach 20 could be done like this...

Hi RichBehan, Here is another approach that does not require a UDF. Names are used to simplify (in my opinion) Create 2 Names: d - refers to: =Sheet1!$B$1:$B$5 dMMULT - refers to: =MMULT(--(ROW(d)&#62;=TRANSPOSE(ROW(d))),d) Then use this formula =MATCH(TRUE,INDEX(dMMULT&#62;A1,,),0)...

Hi Faseeh, I like yours better than mine, very nicely presented (especially like the search option). I'd suggest making some minor adjustments, namely change all the OFFSET formulas to INDEX formulas to remove the volatility. Kyle

Hi Debraj Roy, First, set a reference to Microsoft Forms 2.0 Object Library (will already be selected if you have a userform). Sub CopytoClip () Dim dClip As DataObject Dim sSQL as String Set dClip = New DataObject sSQL = &#34;list of unique data using VBA and concat&#34; dClip.SetText sSQL...

Montrey, You could try this as well, if the numbers are for presentation purposes as it creates a text number that cannot be used in calculations (without further formulas) =IF(ABS(A1)&#62;=1000000,TEXT(A1,"0.0,,M"),IF(ABS(A1)&#62;=1000,TEXT(A1,"0.0,K"))) 150K is formatted as 150.0K, not...

https://docs.google.com/open?id=0BxTFwBrMVo2nWmFBRzFXRzRUWEtkYXNTVWFJd3Q3QQ

SirJB7 The reason why those mismatches were occurring relates to the weighting that {1;2;4} gives each of the fields. A container match = 1 A destination match = 2 A quantity match = 4 So if a row, such as the example provided, matches one row on the data tab with container and...

Hi, I had to do a reverse look-up earlier this week for work. See the linked file for what I threw together. Didn't fully test or make the most efficient but believe it does the trick, including treatment of duplicates. Thanks Kyle...

Hi xcruc1at3r & SirJB7, I apologize for my lack of response, as I have been quite busy. I see what you mean regarding the incorrect results. I think inverting the constant {1;2;4} to {4;2;1} will give you the results you want. Change the lookup table to this 0 No Match 4 Destination &...

1/If you have many VLOOKUPs referring to other sheets, the slow down will be considerable. 2/If you are using exact match for the VLOOKUPs it will be slower. Add those two together and you can easily have a 3-4 minute calculation. From the sound of it, you have such a large number of formula...