Merge multiple sheets data into 1 sheet based on multiple criteria (List Based)

Hello,

I am looking for a help to combine 2 worksheet data for non-identical headers.

Now the macros will do the following:-

1. Look for a Code from worksheets list (Column A) into worksheets data in column D
2. Look for a Status from worksheets list (Column C) into worksheets data in column F
3. And look for a Dates from worksheet list (Start date column D & End date in column E) into worksheets data column C.

If the entire criteria match within the specified dates then copy specific columns (Excluding Column F, I, J, K & R) from worksheets data and past to worksheets statement. After copy the data then look for the below match.

From worksheets list look for the provider name into worksheets Resubmission Adjustment into column F and look for the batch no in column B which is present in worksheets “Data” in column G and worksheets “Resubmission Adjustment” in column D cell value is Resubmission Adjustment the copy those rows only.

Summary (Short Explanation)
Looking for Al Dar Hospital (Quba) for settle batch from Jan 2015 untill Dec 2015 from Worksheets “Data” then copy data to Worksheets “Settlement”
Look for Al Dar Hospital (Quba) for same batch based on batch no for the Resubmission Adjustment in worksheets “Resubmission Adjustment” then copy data to worksheets “Settlement”

Copy of the sample file is attached for your reference with the output.

Thanks for your response.

Do you need any previous.

it is a new requirement that is y I did not upload the previous one.

Yes it is a sample file. the old files you can get in my previous posts if required any history.

No problem.

I have also the same problem since 2 weeks. Just fixed yesterday

Originally Posted by hecgroups

If the entire criteria match within the specified dates then copy specific columns (Excluding Column F, I, J, K & R) from worksheets data and past to worksheets statement. After copy the data then look for the below match.

From worksheets list look for the provider name into worksheets Resubmission Adjustment into column F and look for the batch no in column B which is present in worksheets “Data” in column G and worksheets “Resubmission Adjustment” in column D cell value is Resubmission Adjustment the copy those rows only.

If this is not how you wanted, need to see your exact desired result.

Sub test()
    Dim a, e, i As Long, LastR As Range, flg As Boolean
    Dim cn As Object, rs As Object
    Dim rng As Range, x
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;MEX=1;"
        .Open ThisWorkbook.FullName
    End With
    rs.Open "Select `Date`, `Date`, Format(`Settlement To`,'yyyy/m/d'), Null, `Settlement For`, " & _
            "`Batch No`, 'Resub', Null, Null, Null, `Amount` From `Resubmission Adjustment$`", cn, 3
    Set rng = Sheets("Resubmission Adjustment").Cells(1).CurrentRegion
    Sheets("Statement").Cells.ClearContents
    a = Sheets("list").Cells(1).CurrentRegion.Value
    With Sheets("data").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 2 To UBound(a, 1)
            .AutoFilter 4, a(i, 1)
            .AutoFilter 6, a(i, 3)
            .AutoFilter 3, ">=" & a(i, 4), 1, "<=" & a(i, 5)
            If .Parent.Evaluate("subtotal(3," & .Columns(1).Address & ")") > 1 Then
                With .Offset(IIf(flg, 1, 0))
                    Set LastR = IIf(flg, Sheets("Statement").Range("a" & Rows.Count).End(xlUp)(2), Sheets("Statement").[a1])
                    Union(.Columns("a:e"), .Columns("g:h"), .Columns("l:q")).Copy LastR
                End With
                x = Filter(rng.Parent.Evaluate("transpose(if(" & rng.Columns("f").Address & _
                    "=""" & a(i, 2) & """," & rng.Columns("b").Address & "))"), False, 0)
                If UBound(x) > -1 Then
                    For Each e In x
                        rs.Filter = "[Batch No] = '" & e & "'"
                        Sheets("statement").Range("a" & Rows.Count).End(xlUp)(2).CopyFromRecordset rs
                    Next
                End If
                flg = True
            End If
            .AutoFilter
        Next
    End With
End Sub

Thanks hans,

i just got my laptop. let me review and get back to you.

We will be in regular touch from today to finish this issue.

Thank once again for your help.

It seems there is some kind of confusion in understanding.

Now the user extracting Al Mana General Hospital – Dammam for open batches for the period of January to June 2015 but after running the macro it is extracting rows from Resubmission Adjustment worksheets only. Not copying data from Worksheets(“Data”).

The script should look data in data sheets first then compare the batch number with Resubmission Adjustment worksheets batch numbers in column D before exacting data.

Copy of the sample output file is attached for your reference.

Hai Hans,

In this case the script should pop up an message that their is no status of batch found in that period.

Also can we automatically take the list one by one. Let say if i have a checkbox on that form with label Work Automatically.
I mean to say the script automatically take provider one by one is the checkbox is checked.

Can we do that.

Also if you explain your code so that if any requirement change i can do by myself.

Thank you once again or your help.

If you are talking to me, you are confusing me.

If you delete all the columns

Excluding Column F, I, J, K & R

from Statement sheet, you see the layouts is the same.
I don't know about the number of rows, that's the other story.

If you are not talking to me, just forget about my code.

Thanks hans.

Let me gothrough the file and get back to you.

Hai Hans,

I really thankful to you for this great help.

I have just small changes, actually it is not changes but a request. If possible.

1. If the status is blank (Means blank cell in column C on worksheets ("List") then it should extract all the records.
2. If there is no data found on worksheets("Data") then script should not goto reimbursement adjustment sheets to check the batches records.

3. Instead of click providers 1 by 1 can it be automatically extract the data untill the rows finished in worksheets ("List").
Why working with few providers it is OK but when is came to huge number of provider again it is manual clicking on listbox and command button.

4. If you can add the header on the listbox which will be easy for user to understand the fields.

Copy of the version is attached. the last version

I mean to say their is header in row 1 for worksheets("List") which is Code, Provider, Status, Start Date & End Date.

Can it be possible to have on the listbox for userform.

Yeah i have tried and compare the manual work with your file it is working good.

If you can make the changes which i have requested then it will be good.

Thanks once again for your help.

On initial stage i try to study your code but i did not understand the code as it has profissionalism.

Due to less knowledge of VBA macros i cannot make amendments to our script. This is the reason i came back to you.

But for your information, your previous helps which you have make for me it is helpful and i am using in my other projects.

Hai Hans,

Thanks for your advice and really helpful for me. I will follow your advice.

Also i will gothrough the file and provide your the feedback.

Hans,
Sorry for responding late to you as the same problem with y laptop.

Thanks for your valuable help the file is working fine.

If their is any changes i will try to make it by my self, incase if i need your assistance then i will come back to you.

Thanks once again.

Topic marked as solved and reputation is added.

May be i did not explain correctly.

As i said the macros should look for the data mentioned in the worksheets list then extract records from data sheets. Then after that compare the batch no. which is extracted with the RA worksheets then extract list of RA batch from their.

But that is happening here is the script is not doing the second part i.e. looking for extracted batch no. on settlement sheet and extract records from RA sheets.

See the logic is clear look for the period extract records from data sheets to settlement sheets. Then look for those extracted records batch no. and compiler with RA worksheets and pull the records.

It is the same logic which you have applied on beside link.https://www.excelforum.com/excel-pro...-and-date.html

Thanks hans,

1 more thing. When a user selecting the provider it is saying Resubmission Adjustment are 5 records whereas it is only 2. How to correct this.

Ok no problem.

At least can you tell me what link you have change just to understand the script.