PDA

View Full Version : Solved: Compare column with multiple sheets



tlchan
12-31-2011, 09:46 AM
Hi there,
I wish to compare column A of "Summary Year" sheet with the remaining sheets in the workbook in column A for matched value. If matched then copy the respective row value under column D to "summary Year" sheet column B and so forth. The column value under various sheets may not in same sequence as in "summary Year" sheet.


How do I accomplish this task?


Example of workbook attached

Thank you
:(

mdmackillop
12-31-2011, 02:35 PM
Here's a UDF solution
Option Explicit

Function GetData(typ As Range, Dt As Range)
Dim sh As String, c As Range

sh = Format(Dt, "dd-MMM-yy")
On Error Resume Next
Set c = Sheets(sh).Columns(1).Find(typ, lookat:=xlWhole)
If Not c Is Nothing Then
GetData = c.Offset(, 3)
Else
GetData = "-"
End If
End Function

tlchan
12-31-2011, 06:31 PM
Thanks mdmackillop. Your code works brilliant!

How should I put it under command button where the function will be appended automatically at respective columns and at the same time extract value from respective sheet name under Row 1 of column B,C,D....

Please help!
:dunno

marreco
01-01-2012, 04:38 AM
Hi...
maybe this will help

Private Sub workbook_Open()

Range("B2").FormulaLocal = "=getdata($A2;B$1)"
Range("C2").FormulaLocal = "=getdata($A2;C$1)"
Range("D2").FormulaLocal = "=getdata($A2;D$1)"
End Sub

mdmackillop
01-01-2012, 06:08 AM
or

Sub AddFmla()
Selection.FormulaR1C1 = "=GETDATA(R[]C1,R1C[])"
End Sub

tlchan
01-02-2012, 07:38 AM
[/URL]
Thanks both of you for the quick solution.

The code works perfectly.
[URL="http://www.vbaexpress.com/forum/member.php?u=87"] (http://www.vbaexpress.com/forum/member.php?u=37997)