Merge multiple sheets data into one worksheet based on multiple criteria and date

Hello,

I am looking for an help on below explanation. Previous topic HERE. and below is the explanation in my previous topic.


I am looking for a help to combine 2 worksheet data for non-identical headers.
Click Search provider button on ribbon and enter search criteria (For example Quba) and click search. Below listbox1 will give the list of provider per year. When a user click any of the months (For example Al Dar Hospital (Quba) - 2015) the textbox grab that clicked text i.e. Al Dar Hospital (Quba) – 2015.

Now what I am requesting here is when I click Export Data button then VBA Macros will look for that text value on both the worksheets i.e. Data & Resubmission Adjustment and copy entire rows on below conditions.

1. From Worksheet data copy the entire row where the text value match.
2. From Worksheet “Resubmission Adjustment” look for the text value and look for batch no. In column B which is present in Worksheets “Data” in column G and Worksheet “Resubmission Adjustment” in column D cell value is Resubmission Adjustment copy only those rows.

After matching all the criteria and copy the rows and past it to the worksheet(“Statement”).

Addition to the above I need to add one option which is date.

after doing the above procedure if the use enter the date for example 01-01-2015 until 01-05-2015 the script extract the data for that specific provider for this period only.

To understand the request better I am attaching the sample file (Demo).

Thanks for your response.

The requirement is typing but if can be possible can we make both calendar and typing.

At last the script will read the textbox dates of period and extract the data right.

Sorry for incomplete information.

Concept is the same as before. If the period falls in both the sheets it should extract records from both sheets.

for example:

If the period is March 2014 to November 2014 then the rows copies from Data worksheets and if the same period the batches listed in Resubmission Adjustment worksheets then should copy that rows.

For example Al Dar Hospital (Quba) period of March to November 2014 their are 2 batches within the same period which is NB20150929-4945 & NB20141120-6039 also been added to statement worksheets. Means extract row from Resubmission Adjustment worksheets and past to statement worksheets.

So the main data source is from Data worksheets only.

I how this will clear your doubt.

Once again sorry for incomplete information.

What I need to know is clear and simple: WHAT DATE (column) DO YOU WANT ME TO FILTER ON?
The dates from column C in Data worksheets. Months date will be fixed first day of every month.


If you say both worksheets, does this mean only the rows of data within the date range that exist in both worksheets?

As I explained the dates if period from March 2014 to November 2014 then rows data from Data worksheets and if the same period for same provider the batches listed in Resubmission Adjustment worksheets then should copy that rows.

For example Al Dar Hospital (Quba) period of March to November 2014 their are 2 batches within the same period which is NB20150929-4945 & NB20141120-6039 for the same provider within that period also added to statement worksheets.

Copy of manual work as demo is attached for your reference to know what will be the output.

See what ever the batches from resubmission is falling in that specific period it would extract.

Also this date option is optional. Means if the user select provider from listbox 1 and did not enter the date then it should extract whole year if it should extract the period which is mentioned in textboxes.

For example if I select Al Dar Hospital (Quba) - 2014 on listbox1 and enter the period of March to November 2014 so it should extract data from march 2014 to November 2014 including resubmission batches within this period.

If I just leave the textboxes blank then it should extract whole year.

That is the dummy data.

The actual format (Custom) will be mmmm-yyyy but actual cell value will be dd-mm-yyyy (first day of every month).

For example 01-03-2014 until 01-11-2014 this will be the date in textboxes and the data will be extract for from March to November 2014.

See the attached sample to understand better.

Thanks Keeb for your details explanation. I know this is the texting (Demo data) which is not having proper date format buy when I change manually to dd-mm-yyyy date format.

I think this format will be easy for you to work on date format.

Copy of the data is attached.

Hai Keeb,

Thank for your all efforts which you have put into this request.

I will thoroughly check the file and out result then get back to you to provide you a feedback.

Thanks once again.

Hello Keeb,

We are almost there. Their still more thing to understand.

Referring to my first post we are working on combining data from 2 worksheets into 1.

Validation will be look for a hospital in data sheets and look for the same hospital in Resubmission Adjustment sheets. After find the same hospital look for text value (Resubmission Adjustment) in column D in worksheets Resubmission Adjustment. If found extract all the data into a new worksheet.

Now the file is including the period if user select resubmission batches.
For example: User search provider and select for year 2014 the batches listed below. User need not to select the resubmission batches. These batches have to be included in the period which is in text box as an example march 2014 to November 2014 there are 2 batches of resubmission included in this period.

Instead of going for long explanation I will summarized the request.

Full Year
1. Search for a provider
2. Select that provider
3. Without selecting resubmission batches (This is only for viewing purpose).
4. Click on export button. (This will extract entire data for that selected year.

Partially Period
1. Search for a provider
2. Select that provider
3. Without selecting resubmission batches (This is only for viewing purpose) if exists.
4. Enter start date and end sate in the text boxes.
5. Click on export button. (This will extract entire data for that selected year and period mentioned in the text boxes.

I hope this will get you the better idea of output. If not I am attaching the both samples (Full Year & Specific Period)

Topic Marked As Solved And Reputation Added.