How to extract list of individuals from master document between two dates

[bala04msw]

Dear forum,

I need your help to come out from this technical issue. I want to extract list of individuals details from master document between two dates. i have created a table and tried out formula also. But it shows #VALUE Error. Kindly help me and do the needful.

I have tried the following formulas

=IF(ROWS($A$5:$A5)>$C$2,"",INDEX(MR!B6:B916,SMALL(IF(TEXT(MR!$B$6:$B$916,"yyyymmm")=RGT!$B$1&RGT!$B$2,ROW(MR!$B$6:$B$916)-ROW(MR!B6)+1,ROWS(RGT!$A$5:A5))))

=IF(ROWS($A$4:A4)>$C$2,"",INDEX(MR!$B$6:$B$916,SMALL(IF(MR!$B$6:$B$916>=$A$2,IF(MR!$B$6:$B$916<=$B$2,ROW(MR!$B$6:$B$916)-ROW(MR!$B$6)+1)),ROWS($A$4:A4))))

=IFERROR("INDEX(MR!$B$6:$B$916,AGGREGATE(15,6(ROW(MR!$B$6:$B$916)-ROW(MR!$B$6)+1)/((MR!$B$6:$B$916<=RGT!$B$2)*(MR!$B$6:$B$916>=RGT!$A$2)),ROWS(RGT!$A$4:A4)))","")

Regards
Bala

[Glenn Kennedy]

Try this array formula, copied across and down. In your example, there are NO matching values, so in the sheet, I changed the date range so that some answers are returned.

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Glenn Kennedy]

Thanks for the Reputation...

[bala04msw]

Dear Glenn,

I would like to get your consultation in continuation of your previous guidance. In continuation of that extracted individual details, i tried to get count of individual by multiple criteria and i framed COUNTIFS Formula also. But always says "you have entered too few arguments" notification. If i do it for single criteria, i get the result.

For example: How many of the NEW REGISTRATION ->having COOKING -> are MALE

Regards

[Glenn Kennedy]

=COUNTIFS(E:E,"Male",F:F,"Cooking",G:G,"New") should do it.

[bala04msw]

Dear Glenn,

Thanks for your patience and clarifying my doubt. But i wanted to be in different sheet and i have tried with reference sheet also. could you please help me on this?

I have inserted new sheet "SR" - Summary Report.

Regards

[Glenn Kennedy]

Something like this.
=COUNTIFS(RGT!H:H,SR!B2,RGT!F:F,SR!B3,RGT!E:E,SR!B4)

However, in your dropdowns you have an extra space after INDIVIDUAL that you'll have to remove. This can't simply be copied across because of your merged cells (best avoided at all costs!!).

[bala04msw]

Dear Mr. Glenn,

Thanks once again for all your prompt response and support. Today i have come for 2 technical guidance. Could you please teach me on this? The requirements as below:

Requirement of Guidance: 1 - How to get the reference of duplicate value:

1. I have a master sheet in excel, which includes individual person's personal details (Age, Gender, Occupation, etc.,) in column followed by their name.
2. The master sheet is updated by day to day basis, those who are visited to counseling center.
3. Each and every time i have to enter each person's personal details (Age, Gender, Occupation, etc.,),if they visit.

My requirement is: "While i will enter the name of the person, i need to get their personal details automatically in the respective followed column.

Requirement of Guidance: 2 - How to set remove duplicate value in future entries:

1. In continuation of updating master sheet. i have set individual person's data extraction sheet separately. For example: If i want to know only about Mr. X and his number visit and counseling details. I have a field includes only unique value of individual(name) and if i select Mr. X name, the table set, which i have created will show the details of him.
2. To set this sheet, i need to get unique value (No. of individual visited to clinic) of individual. I know, it is very ease to remove duplicates from the available list. But...

My requirement is: I want to get automatic remove duplicate option for future entries. For example: Mr. X has already visited 3 times to counseling center. If Mr. X will visit to counseling center his 4th time next month, i should not get add Mr. X's name again in the drop down list.

Kindly do the needful for the above required support.

Thanks in advance

Bala

[Glenn Kennedy]

You really need to start a new thread. n Also, a sample worksheet would be helpful.

[bala04msw]

Dear Glenn,

Thanks for your reply. Could you please let me know, how i can send a new thread to you directly? is it possible? orelse i can post my thread in open forum only.

Thanks

Bala