Selectively Identify Validated Cells with Macro or Formula

  • Hello all, I was trying to figure this one out using an if(isna(vlookup type of formula but not getting the desired results. I need to identify specific rows of data that meet a certain criteria, I know it sounds simple. Here is an explanation of the attached file.

    When a patient (Ryan Seacrest) visits us we log every procedure we perform whether it is billable or not. So every line of data represents a specific medical procedure performed for Mr. Seacrest. But look again, you will see Duplicates of data (this is all on the same day). So the data came out of our software screwy (which is why we bought new software). If you scroll to the far right and look at column H you will see it is already sorted by CPT code, and its showing a multiple claim/payment amounts for the exact same code is columns E and F.

    (you will also see my very rudimentary formula for checking for duplicates in column I)

    I need a formula or macro that will find a match for columns H and E in Sheet1 to Sheet2. Why? I need to preserve the values of column F because in some cases we were paid, in other cases we were not paid, and I need to filter for the code with the right claim amount to do that. Thank You in advance!

    ***NOTE: I had to delete columns I and F (which had formulas) to get the file small enough to upload, but those were of no consequence.

  • Re: Selectively Identify Validated Cells with Macro or Formula


    Well technically there aren't duplicates cause the claim amounts are different. How are we supposed to determine which claim amount is the right one?

  • Re: Selectively Identify Validated Cells with Macro or Formula


    Well you can either click the data tab, click delete duplicates, deselect claim amount, then put this into E2 and copy it down


    =INDEX(Sheet2!$B$16:$B$992,MATCH(B2,Sheet2!$A$16:$A$992,0))


    or you can use this macro

    Code
    Sub something()
        DataEnd = Range("A1").End(xlDown).End(xlToRight).Address
        ActiveSheet.Range("$A$1", DataEnd).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 7) _
            , Header:=xlYes
        DataEndRow = Range("A1").End(xlDown).Row
        For i = 2 To DataEndRow
            Range("E" & i).Value = Application.WorksheetFunction.Index(Sheet2.Range("B16:B992"), _
                Application.WorksheetFunction.Match(Sheet1.Range("B" & i), Sheet2.Range("A16:A992"), 0))
        Next
    End Sub
  • Re: Selectively Identify Validated Cells with Macro or Formula


    Thanks! In case you hadn't found it already the correct combination of CPT and Claim Amount is in Sheet2.

  • Re: Selectively Identify Validated Cells with Macro or Formula


    Again, thank you. Before I try to implement your suggestions I do want to first caution that I used a few nested if statements to delete duplicates, the problem with this is that it depends on the "desired/correct row" or "non duplicate row" to consistently be at the top or bottom. With the set of data I'm working with, I've found the "correct row" can be either on the top or bottom of its data set. That said, a simple "delete duplicate" function wouldn't work as cleanly as I need it.

  • Re: Selectively Identify Validated Cells with Macro or Formula


    That Macro works great!!!! Thank You So Much! Three Questions:

    (1) The data I presented in the file is a smaller snapshot of a larger set (rows and columns), but the only data which I'm considering... will the macro still work across that bigger data set?

    (2) What happened to the other rows, in other words are they deleted or hidden? I may need to double check the lines that were removed, so can those be stored in a separate worksheet (or can the "good" ones be stored in a worksheet, thereown?)

    (3) What is the best place to start learning how to write Macros, you guys are AMAZING with this stuff!

  • Re: Selectively Identify Validated Cells with Macro or Formula


    1) Maybe, let me explain. The third line of my code says "Columns:=Array(1, 2, 3, 4, 6, 7)" which means its going to find duplicates according to the values in columns 1,2,3,4,6,7. Column 5 (Claim Amount) is excluded cause that data may or may not be the same.

    2) The duplicate rows are deleted. I could move them to a seperate sheet (slowest macro), or I could copy the entire table to a different sheet and delete dups on same sheet (fast macro), or you could leave it the way it is (fastest macro). Just get back with me on this one.

    3) Google...Google...Google. Find tutorials. Start from the very very very basic macro and work your way up through complexity. Ask questions on forums. And one of my favorite tools is to browse through the Object Browser located in the VBE (Visual Basic Editor) and is brought up by pressing F2 while in the VBE

  • Re: Selectively Identify Validated Cells with Macro or Formula


    lawsonrw,


    Please do not quote entire posts. When quoting follow these guidelines


    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant - - not the entire post.


    This will keep thread clutter to a minimum and make the discussion easier to follow.
    Thanks.

  • Re: Selectively Identify Validated Cells with Macro or Formula


    (1) Thanks for clearing that up, I don't foresee having any difficulty getting the column references to match.

    (2) Just out of precaution I guess the slowest macro might be the best...

    (3)Thank You!

  • Re: Selectively Identify Validated Cells with Macro or Formula


    here ya go


  • Re: Selectively Identify Validated Cells with Macro or Formula


    aye curumba! we're back from power outages here in the midwest and I'm trying to modify the script to match up with the larger file... in the example file here is how the code matches up to the columns:1 = date2 = procedure code3 = beneficiary name4 = mrn6 = EOB#7 = cptin the file on my computer, here is how they match up:3 = date5 = procedure code11= beneficiary name20= mrn27= eob#34= cptand Amt (Claim Amount) is 25.I've tried it a few times to no avail, any help?BTW, your script worked EXACTLY as you said it would, I'm so grateful!

  • Re: Selectively Identify Validated Cells with Macro or Formula


    My Excel is screwy, I know your code is good but its telling me 'Run-time error: 13 Type Mismatch'... then on debug it highlights "For i = 2 To UBound(a, 1)" It seems to only work properly when I copy directly from the /code block in the forum, could you post it that way, please?

  • Re: Selectively Identify Validated Cells with Macro or Formula


    Replace the a = ??? line with this. This was all an error on my part. Sorry.

    Code
    a = Range("A1", Range("A1").End(xlToRight).End(xlDown)).Value
  • Re: Selectively Identify Validated Cells with Macro or Formula


    Okay, here is how the code looks now:

    Code
    Sub better()    'The sheet name to copy deleted duplicates    sht = "Sheet3"    a = Range("A1", Range("A1").End(xlToRight).End(xlDown)).Value    With CreateObject("Scripting.Dictionary")        .comparemode = vbTextCompare        n = 1        For i = 2 To UBound(a, 1)            temp = a(i, 1) & ";;" & a(i, 2) & ";;" & a(i, 3) & ";;" & a(i, 4) & ";;" _                & a(i, 5) & ";;" & a(i, 11)            If Not .exists(temp) Then                n = n + 1                For ii = 1 To UBound(a, 2)                    holdVar = a(n, ii)                    a(n, ii) = a(i, ii)                    a(i, ii) = holdVar                Next                    holdAmt = a(n, 6)                    a(n, 6) = Application.WorksheetFunction.Index(Sheet2.Range("B16:B992"), _                                Application.WorksheetFunction.Match(Sheet1.Range("B" & i), Sheet2.Range("A16:A992"), 0))                    If i  n Then                        a(i, 6) = holdAmt                    End If                    .Add temp, 1            End If        Next    End With    Range("A1").CurrentRegion.ClearContents    Range("A1").Resize(n, UBound(a, 2)).Select    Range("A1").Resize(n, UBound(a, 2)).Value = a    Sheets(sht).Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a    Sheets(sht).Rows("2:" & n).DeleteEnd Sub

    After running it I get "Runtime error: '1004' Unable to get the Match property of the WorksheetFunction class." On debug it highlights:a(n, 6) = Application.WorksheetFunction.Index(Sheet2.Range("B16:B992"), _ Application.WorksheetFunction.Match(Sheet1.Range("B" & i), Sheet2.Range("A16:A992"), 0))

  • Re: Selectively Identify Validated Cells with Macro or Formula


    That's cause the second sheet of the workbook you gave me doesn't contain the data that was in the first workbook

  • Re: Selectively Identify Validated Cells with Macro or Formula


    When you say 'doesn't contain the data' are you talking about what was in Sheet2 or the actual content of Sheet1? I just tried another test run with Sheet2 filled with reference data and got the same error message.

  • Re: Selectively Identify Validated Cells with Macro or Formula


    Okay, so I modified the code. I hope this doesn't make you mad, I know some people take it as a sign of disrespect, I'm really just trying to be proactive in (1) learning and (2) resolving this challenge. Here is what is different:

    Code
    a(n, 6) = Application.WorksheetFunction.Index(Sheet2.Range("B1:B977"), _                                Application.WorksheetFunction.Match(Sheet1.Range("B" & i), Sheet2.Range("A1:A977"), 0))

    But I'm still getting the same error code, as you can see I modified the range to account for shifting the data up by 16 rows in !Sheet2. I'm also attaching a new file as a sample of what I'm attempting to apply the code to. This is an exact copy of the data and fields I'm working with, obviously with faked patient names (patient confidentiality is a priority). As I type this the only thing I can think of that could possibly be holding the process up is when the macro encounters a record that does not have a Claim Amount. Maybe with a blank claim amount it doesn't know what to do/reference and thus provides an error code. I hadn't realized how many of the records have no claim amount (roughly half of 100,000 records), which is a whole separate issue I will have to address with my superiors.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!