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

**hecgroups** · 01-19-2018, 03:25 AM

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).

**Keebellah** · 01-19-2018, 03:51 AM

Good morning,
I will see what I can do for you. Read your PM and will continue here on the post.
Looks simple but ... most simple things result a little more complicated.
Do you want a calendar like pop-up or just type in the dates?

**hecgroups** · 01-19-2018, 03:59 AM

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.

**Keebellah** · 01-19-2018, 04:09 AM

Yes the dates in the textboxes will determine the period

**Keebellah** · 01-19-2018, 05:13 AM

Before I start on this.
The dates you want to filter.

Is that the worksheet named 'Resubmission Adjustment' column Date
or one of the Dates in DATA but then which column?

**hecgroups** · 01-19-2018, 12:16 PM

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.

**Keebellah** · 01-19-2018, 02:31 PM

I am sure that you understand what you are explaining but I honestly do not understand which date (based on which column) you want to make the selection.
If you say both worksheets, does this mean only the rows of data within the date range that exist in both worksheets?

What I need to know is clear and simple: WHAT DATE (column) DO YOU WANT ME TO FILTER ON?

**hecgroups** · 01-19-2018, 11:21 PM

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.

**Keebellah** · 01-20-2018, 04:11 AM

Okay so if O understand correctly you want to be able to filter on the Billing Month?
In the userform the resubmition batch do you want to select all or just one? of this just for information?

**hecgroups** · 01-20-2018, 04:47 AM

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.

**Keebellah** · 01-20-2018, 05:16 PM

Okay, but if you want to select the dates (in Column C) the cells in this coloumn do NOT contain date values but text like MARCH 2014 APRIL 2014 etc.
But a selection in ListBox2 if you have two but only want one then what?

**hecgroups** · 01-21-2018, 12:13 AM

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.

**Keebellah** · 01-21-2018, 04:43 AM

As you state correctly, the

Actual format (Custom) will be mmmm-yyyy

but this is something you have done by hand by you worksheets do not have correct date formats in any way.
If I check the cell formats in all the date cells are not formatted as date all are format of the type General which in Excel causes a lot of issues when it comes down to comparing dates and especially if the system settings are different for each user which in my case is dd-mm-yyyyy and since you have NOT set correct format your 03-11-2014 (if you state this is March 11 2014) is interpreted by me as November 3 2014.
This is taking up a lot of my time and I don't think I can help you with these discrepancies and not knowing what is to be filtered.

I have attached three screenshots of the different worksheets and a module you can use to select dates which you should implement

DATA has not format is plain text !

Resubmission ... column C are the dates as displayed here on my system

Statements is you manual modification

I have attached the zipped vba modules you need to import in your file and the code to add to your userforms and the VBA Project Module


'*  global variables
Global g_sDate  As String
Global g_bForm  As Boolean


'* in the Userform use this to select a date 
Private Sub TbxStartDate_Enter()
    g_bForm = True
    frmCalendar.Show_Cal
    Me.TbxStartDate.Value = g_sDate
End Sub

Private Sub TbxEndDate_Enter()
    g_bForm = True
    frmCalendar.Show_Cal
    Me.TbxEndDate.Value = g_sDate
End Sub

The red marked code code you place in Module1, the other part in the USerform1 and give it the name of the textboxes you use for the date

I hope you can make it work.

**Keebellah** · 01-21-2018, 06:45 AM

I inserted the code in the version you attached, so give it a try
You will have to complete the code to use the dates, but since you know best which columns you want to filter I am sure you can do this.

**hecgroups** · 01-21-2018, 12:37 PM

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.

**Keebellah** · 01-21-2018, 04:13 PM

I will see if I can work something out, but since you now have the calendar in my last answer I am sure you can use that to work on it too.

**Keebellah** · 01-23-2018, 10:07 AM

I might suspect that you have found it more complicated than you thought. There are many conditions to take into account.
I have tried my best. If the month names are still in Dutch just make sure you refresh the calculations and they will be in your system's language settings again.
Hope it works for you in this way

**hecgroups** · 01-24-2018, 10:54 PM

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.

**hecgroups** · 01-25-2018, 11:18 PM

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)

**hecgroups** · 02-03-2018, 04:19 AM

Topic Marked As Solved And Reputation Added.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

transfer of specific data Based On Multiple Conditions from worksheet to Multiple sheets

Select a cell at random based on multiple selection criteria across multiple sheets.

[SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria

Compare and Merge Data from Multiple Sheets in a worksheet

Search multiple sheets for value based on criteria and copy data accordingly

Excel 2007 : Countifs to gather data from multiple sheets, based on multiple criteria

Copy data from multiple sheets based on certain criteria!!

Bookmarks

Bookmarks

Posting Permissions