Excel formula to find 1st 2nd (second) 3rd (third) to Nth occurrence in range and match multiple conditions through different columns

Query : I have database containing many columns, i have different tab/sheet for report sheet, in that I have to match values from two columns and extract matching values, problem is that my database there are same values in all two columns, so i want to match & extract 1st instance for 1st case, 2nd instance for second case and so on.

Data is in sheet in below format:




I want report in below format:



Solution: We can use an array formula to match multiple conditions & extract values, but I always try to avoid array formula since I believe it is heavy on sheet when it contains large. This formula is unique since I have tried using Large() function & used index to receive an array value in single column.

I have use following formula:

=INDIRECT(ADDRESS(LARGE(
                                                          INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1),
                                                          SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1),
                                                          COLUMN(D1),1,1,"DATA"))


Step 1: Get row number for matching condition usinh Index Formula:

INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1)

Step 2: To handle repeated values; we need to first fiind total matching values form that total count we will deduct instance of repetation at particular row, this will give us largest matching value for specific cell. Eg.In given report XYZ is repeated 3 times, but for finding value in cell C1 we will find third largest value, for finding value in cell C2 we will find second largest value & for finding value in cell C3 we will find first largest value. This can be arrived by using:

SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1)

Step3: If we put array result of step1 & result of step 2 in function Large() we will get row number of matching value, so formula will be :


=LARGE(INDEX(ROW(DATA!$A$2:$A$19)*(DATA!$B$2:$B$19=A2)*(DATA!$A$2:$A$19=B2),,1), SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2))-SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))+1)

Step 4: Using Address formula to convert row number to cell refferrence (address) of destination cell.

=ADDRESS( step3 formula , COLUMN(D1),1,1,"DATA")

Here we used COLUMN(D1) because required result in column D.

Step 5: Using Indirect() function to converer cell address in  Step 4 to required result.


Download Example file

Please give your feedback & you can ask your query in iur open form www.ExcelVbaLab.Com

Cheers!!

Excel VBA File Upload & Download Manager / File copy paste from one location to another

If you have shared folder without control of who is a uploading & downloading (copying) file, there a simple solution to it. Through this file a manager you can keep a store of your shared folder.

In this macro i have kept C:\temp as default folder which you can change as per your choice, Run macro & select option either upload & download.

When you select download you will be asked to C:\temp folder & you can select file to be download, select file & after that you will be prompted for destination folder where you want to downalod/copy file. Enter file description if any & you are done.

Similarly, for uploading a file you will be prompted to select a file to be uploaded to default folder, select file & file will be copied/uploaded to a default folder.


Click to Download File


Give Feedback & Recommendation


tag:Excel VBA File Upload & Download Manager / File copy paste from one location to another

Outlook VBA Macro to group unread email with subject containing Excel & forward all email as attachment to specific email ID

Query: I want to automate incoming emails on the basis of its subject, if this contains word excel I want to attach all such email to new email as a mail item & forward it to particular email ID.

Solution:

We can create loop for all unread email in outlook & check whether it is unread & contains word Excel, and if contains then add email item as an attachment & forward to defined recepient.

Option Explicit

Sub ConsolEmail()

Dim objMailItems As Items

Dim objMail As Object

Dim ns As NameSpace

Dim mFound As Boolean

Set ns = Application.GetNamespace("MAPI")



Set objMailItems = ns.GetDefaultFolder(olFolderInbox).Items

   

'Assign the email address to forward items to

Dim msgList As String

msgList = "vba@vabs.in"

    

'Assign Subject

Dim msgSub As String

msgSub = "Excel"

mFound = False

'Create new email

Dim objNewMail As Object

Set objNewMail = Application.CreateItem(olMailItem)

    

'Forward each item as attachment

For Each objMail In objMailItems

'Debug.Print objMail.Subject

    If objMail.UnRead = True And _

     InStr(1, objMail.Subject, msgSub, vbTextCompare) > 1 Then

    objNewMail.Attachments.Add objMail

    objMail.UnRead = False

    mFound = True

    End If

Next



If mFound Then

objNewMail.To = msgList

objNewMail.Subject = "Group email for " & msgSub

'objNewMail.Save

objNewMail.Display

'objNewMail.Send ' untag this line for sending email instead of displaying

End If



MsgBox "One operation completed"

   

Set objMail = Nothing

Set objMailItems = Nothing

Set objNewMail = Nothing



End Sub

Do give your feedback & post your query on form www.ExcelVbaLab.com

Cheers!

#Tag : #OUTLOOK #VBA #MACRO #TO #GROUP #UNREAD #EMAIL #WITH #SUBJECT #CONTAINING #EXCEL & #FORWARD #ALL #EMAIL #AS #ATTACHMENT #TO #SPECIFIC #EMAIL #ID

Excel formula to get total of/sum of all digits(number) in a cell

Query: I have got numbers in cell, i want to find total of all digits (number) in a particular cell.

e.g. If in cell A1 there is number 1234, then i want total/sum of 1+2+3+4=10 in next cell.


Answer: We can achieve this result using SUMPRODUCT formula,

formula is =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))


Cheers!!




tag: #EXCEL #FOORMULA  #total #of/sum of all #digits(number) in a #cell

Excel VBA Macro to Import sheet from all file in folder and then merge first two columns in to master sheet

Hi Every one.

It is big pain for MIS guys to import data from many files to the master file and manipulate the same. Below macro will import all Only Active sheet from each file into the master workbook, after impoting sheets, it will copy first two columns and keep last 10 character from it.

Option Explicit

Sub Merge_All_Excel()



Application.ScreenUpdating = False

Application.DisplayAlerts = False



Dim sn(9999) As String

Dim xDir$, fName As String

Dim sheetname As String

Dim master As Workbook, import As Worksheet, file2 As Workbook

Dim r As Long, i As Long

Dim xt As String

Dim lr As Long

Set master = ThisWorkbook

  

With Application.FileDialog(msoFileDialogFolderPicker)

     .InitialFileName = Application.ThisWorkbook.Path & "\"

     .Title = "Please select a folder containing files"

     .Show

If .SelectedItems.Count <> 0 Then

   xDir$ = .SelectedItems(1) & "\"

End If

End With

  

fName = Dir(xDir$)



    r = 1

    Do While Len(fName) > 0

        If UCase(Split(fName, ".")(UBound(Split(fName, ".")))) = "CSV" Or _

        Left(UCase(Split(fName, ".")(UBound(Split(fName, ".")))), 2) = "XL" Then

        sn(r) = fName

        r = r + 1

        End If

        fName = Dir

    Loop



    On Error Resume Next

    For i = 1 To r - 1

        Workbooks.Open xDir$ & sn(i), ReadOnly:=True

        Set file2 = ActiveWorkbook

        Set import = file2.ActiveSheet

        import.Copy After:=master.Sheets(master.Sheets.Count)

        file2.Close False

        master.Sheets(Sheets.Count).Activate

      

        xt = Split(sn(i), ".")(UBound(Split(sn(i), ".")))

        sheetname = Replace(sn(i), "." & xt, "")

        master.ActiveSheet.Name = sheetname

    Next i



Dim shCount As Integer

Dim col As Integer

Dim sh As Worksheet

Dim dRng As Range

Dim cell As Range

Dim LastCell As Range

Dim rng2 As Range



Set sh = Sheets("MASTER")

sh.Activate

sh.Cells.Clear



shCount = Sheets.Count

col = 1

For i = 1 To shCount

If Sheets(i).Name <> "MASTER" Then

Sheets(i).Activate

Set dRng = sh.Cells(1, col)

dRng.Resize(1, 2).Value = Sheets(i).Name

lr = Cells.Find(What:="*", SearchOrder:=xlRows, _

    SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Set dRng = sh.Cells(2, col)

Sheets(i).Range("A1:B" & lr).Copy

sh.Select

dRng.PasteSpecial xlPasteValues

col = col + 2

End If

Next

sh.Activate

Application.CutCopyMode = False

Cells.Columns.AutoFit



Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row, Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Set rng2 = Range(Range("A3"), LastCell)



For Each cell In rng2

If cell.Value <> "" Then

cell.Value = Right(cell.Value, 10)

End If

Next cell



Cells.Columns.AutoFit





Application.ScreenUpdating = True

Application.DisplayAlerts = True



MsgBox "Done", Title:="www.ExcelVbaLab.Com"

Range("A1").Select

End Sub

Do write your feedback & post your excel VBA macro related query on www.ExcelVbaLab.Com

Cheers!!

Tags: #Excel #VBA #Macro to #Import #sheet #from all #files in #folder and then #merge #first two columns into #master #sheet

Excel Formula double lookup, match heading & sub heading and lookup data from main table

Hi,

I have a data structure like heading & subheading. Each heading has four subheadings, I want to lookup a data by matching heading & then subheadings, refer data below:

Data is like below:

I want output in below format:








Solution:

First we need to match heading & get location of heading & after that we can create specific range related to heading for searching & apply Hlookup formula in that ramge to get result.

Formula 1 : =HLOOKUP(B$7,OFFSET($A$1,2,MATCH($A8,$A$1:$AB$1,0)-1,2,4),2,0)

Formula 2: =INDEX($A$1:$AB$4,4,MATCH($A8,$A$1:$AB$1,0))

Formula 1 is though complecated but gives result in case of there is jumble or interchange in sub headings.

Comment or write to us on forum for clarifications or to post your querries www.ExcelVbaLab.com

Cheers!!

Tags: #Excel #Formula #double #lookup, #match #heading & #subheading and #lookup #data #from #main #table #to #Sub #Table

Multi column combo box.

Multi column combo box..

Have you ever come across situation wherein you want to show multiple items in combo box with description, but on selection you want to restrict it to only 1st item..

So we will have different view item in combo box & different result value.

See attached file for such example.

Click to Download

Cheers!!