+ Reply to Thread
Results 1 to 44 of 44

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

  1. #1
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I will see if I can help you again.
    Have downloaded the file and will read your question.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    Thanks for your response.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    There are no macros in your attached file.

  5. #5
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

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

  6. #6
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    Do you need any previous.

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

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Just to inform you that I have not forgotten you, but I am installing a new system and my Office installation is not up-and-running.
    The moment it all works I will see what I can do for you.
    Regards,
    Hans

  8. #8
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    No problem.

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

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Yes, well, it takes time
    You mention the Worksheet 'Settlement' in your post but is it not 'Statement' ?

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I did not do anything with the specific columns
    You can probably edit that yourself.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Can you explain what you mean here?

    If the*entire criteria match*within the specified dates then*copy*specific columns (Excluding Column F, I, J, K & R)
    Which are the specific columns? the exclusion is that all the columns after and including K? R is empty

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

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

    Quote Originally Posted by hecgroups View Post
    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.
    Please Login or Register  to view this content.

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    If I look at the Quba hospiptal there are no resubmissions
    But it's the OP that decides
    Attached Images Attached Images

  14. #14
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  15. #15
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I am sorry to correct you.
    The request in List is for status Open and there is no status Open in the Data sheet for that Hospital,
    so the code is doing exactly as you requested.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    It is the dates that do not coincide
    There is nothing open in 2015

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

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

    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.

  19. #19
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    There are some comments in the code that explain what the variables are for.
    I added a message box that will show the results of that run when completed.
    I will see if I have the time to explain all the code.
    My guess is that if you read through the code it will not be that difficult to understand.

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I minor addition informing the user if there is data present after selecting the provider
    Attached Images Attached Images
    Attached Files Attached Files

  22. #22
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    Thanks hans.

    Let me gothrough the file and get back to you.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    A different approach, no need of the list.
    Try it and let me know

  24. #24
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

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

  26. #26
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I'll see what I can do,
    My thoughts are that this is quite clear and explains itself but ... I am not the user
    About processing all, I'll see what I can do, have you tried anything? You've got the code too
    Attached Images Attached Images

  28. #28
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    That is not what I meant, I asked if YOU tried to program something.
    We here are to help and some of us go the next step and develop because we like it, but in my case I also expect that the user at least shows an effort to evolve.
    I know that one to get a finger you want the entire hand and more.
    I will of course try and help you and I hope you get my point.

  30. #30
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Okay, no problem, I'm working on it right now, hope to have something completed later today.
    About VBA, it is not that difficult
    You should see it as writing a letter or a book
    Just write down in words what you want to do
    Select the worksheet
    select someting
    if the value is this or that tehn do this else do something else
    etc, etc.

    Then translate the steps to VBA code, respecting the syntax will get you very far.

    Record a macro and carry out the steps you want to do.
    Stop the recording, open the VBA editor and look at what has been 'created'
    The read it out loud and you will see that it is not all the difficult.
    That is the way I started (may years ago) and I never took any lessons, just kept trying, I often struggled and failed, but tried again.
    You need one very important element and that is time.
    Don't be afraid to make mistakes and always use a backup of the original file.

  32. #32
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I did my best.
    The Userform will show what you want to do

    I have take into account that there is no status so ALL is exported.
    If DATA then also no Resubmision
    Test it and let me know.
    The module that actually processes the request is no longer in the Userform VBA module

  33. #33
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  34. #34
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  35. #35
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Glad to hear that the file is working.
    You know where to find me.

  36. #36
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    Dear Hans,

    I try hard not to get back to you but the script is so advance that I have too.

    After reviewing the file I came to know that the bathes from worksheets Resubmission Adjustment are included which is not fall in that period.
    Explanation:

    Gama hospital period from Jan to Jul 207 there is are only 2 RA batches but your script is giving 5 RA batches.

    I have done some manual work to show what is excepting in output result.

    Try to edit this line but it dosen't work

    Please Login or Register  to view this content.
    Last edited by hecgroups; 08-10-2018 at 07:36 AM.

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    I did not take the dates into consideration, I was under the impression that the date filter only applied to the DATA worksheet and the other just the Resubmission Adjustment, so all for the selected provider with the status Resubmission Adjustment are copied.
    The result is thus correct as far as the settings but not as goes for the instruction.
    To be clear, the date should also apply for the Resumbussion Adjustments?

  38. #38
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Which column do I take into account?Is that Column E 'Settlement To' ?

  39. #39
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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

  40. #40
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Here's the corrected file, hope this is correct now.
    Like I said, my incorrect assumption.

  41. #41
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    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.

  42. #42
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    Not according to your version (see your own corrected Settlement) it is the same as the new Settlement

  43. #43
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    900

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

    Ok no problem.

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

  44. #44
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

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

    No link.
    Just the code in the section for the Resubmission Adjustment sheet
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria and date
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-03-2018, 04:19 AM
  2. [SOLVED] Need macro to copy data from 1 sheet to multiple sheets based on certain criteria
    By JudyF in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 11-08-2017, 10:34 AM
  3. [SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-05-2016, 10:32 AM
  4. list data on summary sheet from multiple sheets, based on parameters
    By tillyman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2015, 10:06 AM
  5. Simultaneously sorting data in ranges on multiple sheets based on criteria on one sheet
    By Chris Greensmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2015, 06:40 AM
  6. [SOLVED] VBA Code to copy different data from one sheet to multiple sheets based on criteria
    By eharwood in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-08-2014, 02:22 PM
  7. Replies: 0
    Last Post: 02-28-2013, 07:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1