Getting unique count between two dates with multiple conditions

[bala04msw]

Dear forum experts,

Need your help to get VBA code for getting unique count between two dates with multiple conditions. Please find the attached model excel sheet for your kind notice.

Requirement as follows: Steps wise calculation method has been given below:

Section-1
1. Firstly, we will enter / select the start and end date from column L1 and L2.
2. Secondly, Column E date to be filtered based on the date input in L1 and L2.
3. Thirdly, Column F date to be filtered until L2 date. (no need of from date here)
4. Highlight "D" column with yellow color and release all the filter.

Section-2 - In continuation of that, 5th steps in reverse order:
5. Column F date to be filtered based on the date input in L1 and L2
6. Column E date to be filtered before the from date only (before 01-04-20)
7. Highlight "D" column with yellow color and release all the filter.
8. Now, select column "D" and check highlighted row (Sec 1 & Sec 2) and get unique number based on Column D value. (Patient ID)

[Ochimus]

Attached does what you want and puts the patient ID into L42

Please Login or Register  to view this content.

Ochimus

[bala04msw]

Dear Ochimus,

Sincere thanks for your time and guidance. I have read the code, till 8th step it is coming with flow and address the requirement hope so. But,

- 9th step to be highlight the Column D cells with yellow color.
- 10th step to be clear the filters
- 11th step to be again apply the filter and select only yellow highlighted row
- 12th step to be getting unique count by state wise as given in the given. (need an output table as given in the I1:J9).

Moreover, the data table may have huge data, hence I do not want to fix the ranges in the code.

Clarification required for:

1. why you have particularly selected "D9" in 10th step in the code.

Sorry to ask so many questions. Please do the needful.

Regards

Attached does what you want and puts the patient ID into L42

Please Login or Register  to view this content.

Ochimus

[Ochimus]

bala,

Having created the first two steps for you. you will learn more if you try things yourself first.

Select "View", then "Create Macro" and do manually what you want the sheet to do.

When you have finished select "End Macro", and you will have the Code that does what you want.

You can then use the Notes I provided in my post to work out how the Code works.

And quick note - the Administrators ask people NOT to repost entire previous Posts. If there is a specific point in it you need to highlight, just clip and post that bit.

Ochimus

[bala04msw]

Dear Mr. Ochimus,

Thanks a lot for your guidance and support. I will do as suggested. Still i am in learning stage, so that i am asking step by step process. Sorry for that. I will try at my end as you suggested.

Regards