Search results

Hi: May an array formula? =MEDIAN(IF(G6:G12<>0,G6:G12)) Confirm Using Control+Shift+Enter Keys. Thanks

Hi: No, the M code I have given is from the start, if you already have codes that connect to source you do not need the lines to connect the source again. Open the excel file I have uploaded and go through the logic I have applied. You may have to look at the following lines, the logic are in...

Hi: I have highlighted in yellow the helper row I have created and have highlighted in red rectangle where you have Type 1 repeating. The formula is straight forward just use evaluate the formula to understand what I have done. Thanks

Sorry Typo, I mean to say you had two "Type 1" percentages in your file.

Hi: I have fixed the formula, since you had to type 1 % i had to create a helper row in BF. Thanks

Hi: This is easy to do with a power query, find the attached. The result is in green table. let Source = Excel.Workbook(File.Contents("C:\Users\nebusud\Downloads\Concat.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" =...

Hi: May be something like this? Step 1: Add index column Step 2: Calculate Previous sales as Measure Step 3: Calculate Percentage difference as column Thanks

Hi: In case you are using Sumproduct formula yo do not need a macro(ignore my macro). I guess you need to go with formula which will be much faster considering you have only limited no:of rows to do the calculation. Thanks

Hi: You do need need additional macro for this just use the following formula. =SUMPRODUCT(($AD45:$AH45)*(INDIRECT("$AD$"&BB$10+10&":$AH$"&BB$10+10))) Thanks

Hi: I do not think you need a loop for this, the macro will trigger when ever you change the year in AW9 and copy the values to respective columns. Private Sub Worksheet_Change(ByVal Target As Range) With Me If Not Intersect(Target, .Range("AW9")) Is Nothing Then .Range("AW11:AW47").Copy i& =...

Duplicate Post: https://chandoo.org/forum/threads/ambiquous-name-detected-error.40333/#post-241214

Hi: The only way you can do this in excel is by adding the data set to data model and merging it in power query and use this merged data set to build power pivot. There are a lot of resources available on internet how to do this, please give a google search. Alternatively, if you do not have...

Hi: Formulas in Coloured cells Thanks

Hi: I still recommend G's method , pivots are designed to summarise data into meaningful information and is much more easier to manage. If you want a formula solution here is one. Thanks

Hi: Have included the named ranges to make the rows dynamic. Thanks

Hi: Formula in Yellow cells. Thanks

Click on the highlighted button to tag the code

Hi: Which code are you using? The code you have given above is different to what I had given you. Note: Please use code tags when you paste VBA. Thanks

Not sure What you want to achieve here May be this? Sub Macro2() 'AS SHEET Dim Lr As Long With Sheets("AS") Lr = .Range("H:H").Cells.SpecialCells(xlCellTypeConstants).Count + 3 .Range("H4:J" & Lr).Copy Sheets("Dump").Range("A5000").End(xlUp).PasteSpecial xlPasteValues...

Hi: Do this Non-Array formula work for you? =SMALL(IF(MMULT((Share_Id=Share_Key)*(Shares_sold>0)*(Date>=From_Date),1),Num),ROW(A1)) Thanks

Hi: Formulas in yellow cells. Thanks

Hi: May be this formula? =INDEX('Card Number'!$A$2:$AH$5,ROW(A1),AGGREGATE(15,6,(COLUMN('Card Number'!$A$1:$AH$1))/('Card Number'!$A$1:$AH$1=$C$3),1)) Thanks

Hi: May be this? formula in red. Thanks

Hi: Can't you use a pivot as attached? If you want only a formula solution use the native function "MAXIFS" in excel. Note: The slicer will control both the pivot Thanks

Hi: Use the following code Sub test() 'Set the Variables to be used Dim pt As PivotTable Dim Field, Field2, Field3 As PivotField Dim NewCat, NewCat2 As String Dim pi As PivotItem 'Here you amend to suit your data Set pt = Worksheets("Top (Cons)").PivotTables("PivotTable1") Set Field =...