Excel-Buzz

Hi Everyone, The situation is very clear from the picture. The aim is to search F2:H2 (Name, Team, Jersy No) collectively in the A1:C7 table and return corresponding Goals.I have used the following formula:- =INDEX(D1:D7,MATCH(3,MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0)),0)) The formula is An Array formula. So use CTRL + SHIFT + ENTER to confirm it. I will start by explaining the following part:- MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0))

Hi Everyone, As you can see in the picture, is the situation which I came across while serving at ExcelForum.com, So I decided to explain the work around. Actually, what I am doing is, Listing Unique Fruits for Smith and Jones in a Drop Down. I have used Named Ranges to make it more understandable. Here are the steps:- Making some Dynamic named ranges. Note:- While Making these Named ranges, you have to select the any cell in the first row. This is Important as behaviour of the Named Ranges Depends up on that. 1. Name =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",100),Sheet1!$A:$A,1)) 2. Fruit =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",100),Sheet1!$B:$B,1))

We generally come across situation where we want to list Unique rows (for ranges having more than one column) or Unique cells (for ranges having one column). Here is the Simple Macro for that. Sub AdvancedFilter() 'Declaring Variables Dim Des As Range 'Destination Variable Dim Sou As Range 'Source Variable Set Des = Range("G1") 'Set Destination Cell here Des.CurrentRegion.ClearContents 'Clearing Previous Content Set Sou = Range("A1").CurrentRegion.EntireColumn 'Set Source Range here 'Using Advanced filter Sou.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Des, _ Unique:=True Des.CurrentRegion.EntireColumn.SpecialCells(4)

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.

Hi one and all, This time I have come up with a code that will Save a Copy of your current Excel file on a Specified Location (with Data & Time as Prefix) after an Specified Interval. You can always change the Folder Path and Interval Time.  Remarks:- As it involves macros, so make sure that you save the file in .xlsm ( Macro Enabled Format ) and also to enable macros after while Excel Workbook. So here are the steps:- 1. Open the Workbook. Press Alt + F11 to open Visual Basic Window. 2. On the Left Side of the Screen, there would be a Thisworkbook Module. Double Click it. 3. Now Copy and Paste the code below there:- Private Sub Workbook_Open() Call Ontime End Sub

Hi one and all, This time I have tried to Explain the Frequency Function. 1. How Frequency Function works with Non Duplicate, Ascending Bin Array Values Frequency Function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula. Syntax FREQUENCY(data_array,bins_array) Data_array   is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros. Bins_array   is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Hi One and all, This time I have come up with a macro which will disable the Saving As functionality of the Excel Workbook. By using this macro, you can restrict user to use F12 or SaveAs button to save the current progress. Thus allowing only Saving (Ctrl + S) the file. Here are the steps:- 1. Copy the Code. 2. Open the Excel Workbook. 3. Press Alt + F11 to open VB Window. 4. In ThisWorkbook Module, Paste the code. 5. Close VB Window. 6. Try Pressing F12 and Clicking Save As button in the File menu, it won't be working any more. 7. Macros should be enabled for this to work. Here is the Code:- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then Cancel = True End Sub Regards, Check the attached file:- SaveAs Disabled.xlsm

NthMatch  is Custom UDF made by me to overcome the cons of Match Function and Array formulas. NthMatch function provides the row no. of the Nth match in a range, say in the given example, it is providing the Row No ie. 7 for the third Match of "Sachin" in the Range. The Reason I made this formula is that the Match Function provides only the first match and also it needs a lot to skill for a lay man to write an array formula to do the same job. As you can see in the above picture, Both functions gives the same results but the NthMatch syntax is much shorter and easier to understand than array formula.

Hi one and all, This time I have come up with a code to send emails automatically. This code may have plenty of uses to many of you. Actually it uses Outlook email client to send email. The example use can be if you run a procedure, you may want to intimate yourself or other via email, at the end of the Procedure, that task as been accomplished. Make sure OUTLOOK Client application is open before running this macro. Here is the code ( with Late Binding ) Sub SendMailAuto() Set OL_Obj = CreateObject("Outlook.Application") Set New_Mail = OL_Obj.CreateItem(olMailItem) With New_Mail .To = "Add Second Recipient Here" .BCC = "Add First Recipient Here" .CC = "Add Third Recipient Here" .Attachments.Add "Path of the file or Document" .Subject = "Enter your Subject here." .Body = "Write the Content of the Mail"

Hi all of you, This time I have come up with a macro which will consolidate or combine all Multiple Workbooks in one workbook. It will do so by Comparing the Sheet names and Column Heads as well. All you have to do is to make a Main Book containing all sheet names as exact as in the Extract books and with Exact Column heads in the same manner. Then Run this macro by inserting a Module in the VBA editor. It clears all data in the Main Book except column heads before pulling any data. So here are the steps:- 1. Make a workbook as I have said. 2. Put all your Individual files or Extract files in one Folder. 3. Assign this path to FPath Variable in the Code. (That's important, otherwise it won't work) 4. The Main Book should not be downloaded or copied or saved in the Extract Folder. 5. Run the Macro. 6. If you have any problem in implementing the code in your actual workbook, then just give a com

Hi one and all, This time I have come up with a RunOrNot Macro.The purpose of this macro is to restrict user to run the Main Macro (YOUR_MACRO) for once in a day. Changes required in the code:- In the Code below you have to change the YOUR_MACRO with your actual main macro.The text of Msgbox can be changed accordingly. Sub RunOrNot() ExDate = GetSetting("Excel", "Forum", "ExDate") If Not IsDate(ExDate) Then SaveSetting "Excel", "Forum", "ExDate", Format(Date, "DD-MM-YYYY") MsgBox "This is the first and last time today that you are running the macro.", vbInformation Call YOUR_MACRO ElseIf (Date - DateValue(ExDate)) & 0 Then MsgBox "This is the next day. Click OK to run the macro.", vbInformation SaveSetting "Excel", "Forum", "ExDate", Format(Date, "DD-MM-YYYY") Call YOUR_MAC