VLOOKUP USING VBA

[raypk]

Can anyone help me extracting data from master sheet ie sheet 2 using VBA CODING FOR vlookup across different sheets to filter the parameters present in sheet1 and record it into sheet 3. i want result in sheet 3 ..CELL WISE ALL PARAMETERS PRESENT IN SHEET1. Need a macro button on sheet 3.

PS: this is a sample data i need it for larger ranges
__________________________________________________________________

Mod edit : post moved to appropriate forum …​

 

[Marc L]

Hi !

Headers in Sheet1 must be exactly same as Sheet2 to be extracted to Sheet3.
Bad headers appear in grey in Sheet1 after execution, just correct them !

Create your button after pasting this demonstration :​

Sub Demo1()
            Dim Rg As Range
            Set Rg = Sheet2.UsedRange.Rows(1)
                H$ = "1"
            Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
           .Font.ColorIndex = 0
    For R& = 2 To .Count
        With .Cells(R, 1)
            V = Application.Match(.Value, Rg, 0)
            If IsError(V) Then .Font.ColorIndex = 16 Else H = H & "," & V
        End With
    Next
End With
            Set Rg = Nothing
With Sheet2.UsedRange
    V = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), Evaluate("{" & H & "}"))
End With
With Sheet3
    .UsedRange.Clear
    .Cells(1).Resize(UBound(V), UBound(V, 2)).Value = V
End With
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Direct copy way with an advanced filter :​

Sub Demo2()
            Dim Rg As Range
            Set Rg = Sheet2.UsedRange.Rows(1)
                C& = 1
                H$ = Rg.Cells(1).Text
            Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
          .Font.ColorIndex = 0
    For R& = 2 To .Count
        With .Cells(R, 1)
            V = Application.Match(.Value, Rg, 0)
            If IsError(V) Then .Font.ColorIndex = 16 Else C = C + 1: H = H & "¤" & .Text
        End With
    Next
End With
            Set Rg = Nothing
With Sheet3
    .UsedRange.Clear
    .Cells(1).Resize(, C).Value = Split(H, "¤")
    Sheet2.UsedRange.AdvancedFilter xlFilterCopy, , .Cells(1).Resize(, C)
End With
            Application.ScreenUpdating = True
End Sub

You may Like it !

In fact with an advanced filter Sheet1 parameter is useless !
Just enter right headers in Sheet3 and code needs only 3 codelines !
​

 

[raypk]

many many thanx for help Marc L excel Ninja

Hi need one more help...
If I have another database in sheet 4 of the same kind as of Sheet 2. How to incorporate extraction of data into that same sheet 3 of same parameters alongside the data extracted with insertion of cells besides that only like total call volume day1 is stored in B so again from other sheet4 total call volume will insert after B and hence for all parameters... so that i can effectively compare the two databases....subsequently more if databases if needed

plz reply...thanx in advance

 

[Marc L]

day1 ? stored in B ?

That needs details and a joined workbook with all source worksheets
and filled desired result worksheet accordingly …

For more databases, easy way is first worksheet as extraction result
and databases start from worksheet #2.
If really a parameter worksheet is needed (unnecessary for just headers),
result in #2 and databases start from #3 …
​

 

[raypk]

i have uploaded the databases of different days of 14,15,16 Nov ..
I want data comparison of parameters associated in sheet1 of different days and consolidated report in sheet 3.

like total call day 1 today call day 2 ; TCH trafiic day 1 TCH trafic day 2 so on and so forth

 

[Marc L]

[…] and filled desired result worksheet accordingly …

Where is the result worksheet accordingly from source databases ?

​

 

[raypk]

It will be saved in "pre' workbook sheet 3 in which u have provided me the code for getting the output..

 

[Marc L]

As requested, joining a result workbook
may help to well understand your need !

You was talking of Sheet4 …
​

 

[raypk]

Okay thank you ! Do what will be best and efficient..

 

[Marc L]

I won't write a single codeline while I'm expecting details and
you attach a workbook with a filled result worksheet as I yet requested !

Now you can see in Excel help and VBA help how works an advanced filter …
​

 

[raypk]

thanx Marc for your help .. i will now try to do it on my own.. as i was novice to vba thats why i posted...still trying best to learn it and be proficent like you !! many many thanks Marc