Vlookup and variable rows of data Macro

[jsutton]

Sorry if this has been covered but I have been searching post now for weeks on all sites.

I work in healthcare and need to compile a list of patients who do not meet certain requirements. Due to charting software I can only compile a list of patients who meet said requirements and compare to list of all patients.
All patients (worksheet "all") - Certain patients (Worksheet "well") = target patients.

In both worksheets, the patients unique account number is in Column A. In "all" I was adding a blank column B to insert the Vlookup formula. In "well" I was inserting a blank coumn B then adding YES to every row that contains data. Then I would run: =VLOOKUP(A13066,'well'!$1:$65536,2,FALSE) and simply sort the list for column B in "all"

The problem is, when I run this report monthly the number of rows in "well"
changes and also the number of rows in "all" changes. I am sure this is not the most effecient way of doing it. I could use a macro but can not figure out how to account for varying rows of data.

Most boards people just post equations and formulas, so I cannot figure out how to change those to meet my needs because I do not know what the individual variables within those formulas mean. Any help is greatly appreciated.

 

[texasalynn]

Welcome to MrExcel board...

If you record your macro going through the steps, will give you the basics. Then the macro can be modified to make it generic. Try that and post back with questions on the macro to make it generic.

 

[jsutton]

I recorded my macro and when I went to the Debugger I get this:

' compare Macro
' Macro recorded 3/23/2011 by user
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "well"
Sheets("well").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "well"
Range("B2").Select
ActiveCell.FormulaR1C1 = "yes"
Selection.AutoFill Destination:=Range("B2:B4565"), Type:=xlFillCopy
Range("B2:B4565").Select
Sheets("All").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],well!R1C1:R4565C2,2,FALSE)"
Selection.FillDown
ActiveWindow.SmallScroll Down:=-4309
End Sub

do you have any suggestions. I need it to look up the account numbers in the ALL sheet and see if it is in the WELL sheet. The problem is the number of rows of data on each sheet changes every month. I would also like it to show the result "YES" if the account number is on "WELL" and "NO" if it is not.

I probably can figure that out but I do not know how to add the extra information to the VBA editor.

 

[texasalynn]

here I have made some modifications to make it more generic

Sub Compare()
' Macro recorded 3/23/2011 by user
Dim LR As Long, ALR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
'
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "well"
Sheets("well").Select
ALR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "well"
Range("B2").FormulaR1C1 = "yes"
Range("B2").AutoFill Destination:=Range("B2:B" & ALR), Type:=xlFillCopy
Sheets("All").Select
Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],well!R1C1:R" & LR & "C2,2,FALSE)"
Range ("B2:B" & LR)
End Sub

 

[darkallstar]

Hi I know this is a old thread but I wias trying to do the same thing with my sheet but can't get it to work I bolded the vlookup im trying to replace


'


'
Sheets("E.EEDEP").Select
Cells.Select
Selection.Copy
Sheets("M.EEDEP").Select
Cells.Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "Age Range"
Columns("E:E").Select
Selection.ColumnWidth = 12.11
Columns("A:A").ColumnWidth = 6.89
Columns("C:C").ColumnWidth = 10.89
Columns("F:F").Select
Selection.Replace What:="EE", Replacement:="Employee", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="EC", Replacement:="Employee Child", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ES", Replacement:="Employee Spouse", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="FAM", Replacement:="Family", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:C").Select
Selection.Replace What:="M", Replacement:="Male", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="F", Replacement:="Female", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Replace What:="E", Replacement:="Employee", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="C", Replacement:="Child", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="S", Replacement:="Spouse", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R.AgeRange!R2C1:R139C2,2,)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E1889")
Range("E2:E1889").Select
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 806
ActiveWindow.ScrollRow = 894
ActiveWindow.ScrollRow = 1341
ActiveWindow.ScrollRow = 1372
ActiveWindow.ScrollRow = 1449
ActiveWindow.ScrollRow = 1464
ActiveWindow.ScrollRow = 1526
ActiveWindow.ScrollRow = 1549
ActiveWindow.ScrollRow = 1561
ActiveWindow.ScrollRow = 1568
ActiveWindow.ScrollRow = 1626
ActiveWindow.ScrollRow = 1642
ActiveWindow.ScrollRow = 1680
ActiveWindow.ScrollRow = 1688
ActiveWindow.ScrollRow = 1746
ActiveWindow.ScrollRow = 1753
ActiveWindow.ScrollRow = 1757
ActiveWindow.ScrollRow = 1765
ActiveWindow.ScrollRow = 1769
ActiveWindow.ScrollRow = 1776
ActiveWindow.ScrollRow = 1780
ActiveWindow.ScrollRow = 1784
ActiveWindow.ScrollRow = 1788
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1796
ActiveWindow.ScrollRow = 1803
ActiveWindow.ScrollRow = 1807
ActiveWindow.ScrollRow = 1811
ActiveWindow.ScrollRow = 1819
ActiveWindow.ScrollRow = 1830
ActiveWindow.ScrollRow = 1861
ActiveWindow.ScrollRow = 1869
ActiveWindow.ScrollRow = 1881
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1819
ActiveWindow.ScrollRow = 775
ActiveWindow.ScrollRow = 497
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("DIS Menu").Select
Range("A11").Select
End Sub
Sub finalhortonworkscrubHR()
'
' finalhortonworkscrubHR Macro
'


'
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("E.HealthR").Select
Cells.Select
Selection.Copy
Sheets("M.HealthR").Select
Cells.Select
Range("A17").Activate
ActiveSheet.Paste
Range("A24").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Risk Level"
Range("A25").Select
ActiveCell.FormulaR1C1 = "Low Risk"
Range("A26").Select
ActiveCell.FormulaR1C1 = "Average Risk"
Range("A27").Select
ActiveCell.FormulaR1C1 = "Moderate Risk"
Range("A28").Select
ActiveCell.FormulaR1C1 = "High Risk"
Range("A29").Select
ActiveCell.FormulaR1C1 = "Very High Risk"
Range("B32:B36").Select
Selection.Cut
Range("C25").Select
ActiveSheet.Paste
Range("B24").Select
ActiveCell.FormulaR1C1 = "PRI-L"
Range("C24").Select
ActiveCell.FormulaR1C1 = "PRI-B"
Sheets("DIS Menu").Select
End Sub
Sub finalhortonworksscrubflexq()
'
' finalhortonworksscrubflexq Macro
'


'
Sheets("E.Flex").Select
Cells.Select
Selection.Copy
Sheets("M.Flex").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "BI Member ID"
Columns("B:B").Select
Selection.Replace What:="0", Replacement:="Employee", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="1", Replacement:="Spouse", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2", Replacement:="Child", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "Age Range"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R.AgeRange!R2C1:R139C2,2,)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H53524")
Range("H2:H53524").Select
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 1206
ActiveWindow.ScrollRow = 10195
ActiveWindow.ScrollRow = 22472
ActiveWindow.ScrollRow = 24335
ActiveWindow.ScrollRow = 28172
ActiveWindow.ScrollRow = 29816
ActiveWindow.ScrollRow = 40230
ActiveWindow.ScrollRow = 43738
ActiveWindow.ScrollRow = 47903
ActiveWindow.ScrollRow = 48670
ActiveWindow.ScrollRow = 48999
ActiveWindow.ScrollRow = 49876
ActiveWindow.ScrollRow = 49986
ActiveWindow.ScrollRow = 50205
ActiveWindow.ScrollRow = 51849
ActiveWindow.ScrollRow = 52617
ActiveWindow.ScrollRow = 52726
ActiveWindow.ScrollRow = 53494
ActiveWindow.ScrollRow = 53165
ActiveWindow.ScrollRow = 48451
ActiveWindow.ScrollRow = 47684
ActiveWindow.ScrollRow = 44834
ActiveWindow.ScrollRow = 44066
ActiveWindow.ScrollRow = 34091
ActiveWindow.ScrollRow = 32995
ActiveWindow.ScrollRow = 21266
ActiveWindow.ScrollRow = 19183
ActiveWindow.ScrollRow = 13374
ActiveWindow.ScrollRow = 12935
ActiveWindow.ScrollRow = 12716
ActiveWindow.ScrollRow = 10304
ActiveWindow.ScrollRow = 10085
ActiveWindow.ScrollRow = 9756
ActiveWindow.ScrollRow = 9537
ActiveWindow.ScrollRow = 7783
ActiveWindow.ScrollRow = 7345
ActiveWindow.ScrollRow = 5372
ActiveWindow.ScrollRow = 4824
ActiveWindow.ScrollRow = 4495
ActiveWindow.ScrollRow = 2741
ActiveWindow.ScrollRow = 2631
ActiveWindow.ScrollRow = 329
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 1
Range("K1").Select
ActiveCell.FormulaR1C1 = "Diagnosis"
Range("L1").Select
ActiveCell.FormulaR1C1 = "EDC"
Range("M1").Select
ActiveCell.FormulaR1C1 = "MEDC"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R.ICD!R2C1:R18261C4,2,)"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K53524")
Range("K2:K53524").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R.ICD!R2C1:R18261C4,3,)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L53524")
Range("L2:L53524").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R.ICD!R2C1:R18261C4,4,)"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M53524")
Range("M2:M53524").Select
Range("N1").Select
ActiveCell.FormulaR1C1 = "Month"
Columns("N:N").Select
Selection.NumberFormat = "[$-en-US]mmm-yy;@"
Range("C10").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-8
Sheets("DIS Menu").Select
Range("B11:F12").Select
End Sub