How to extract list of unique individuals from the master data set - Reg.

[bala04msw]

Hello good morning everyone,

Kindly help me in generate only unique list from the data set. The model data set have been attached for your reference. The data set is a dynamic data entry sheet, which has list of persons to visit counseling center and aims to cover daily visitors.

Requirement as below:

1. Need to find out only unique individuals from the data set. I have created a helper column, which shows result of number of visit by each individual. Based on that, i have done the count value also. . I have used this formula "=COUNTIFS($G$2:$G$28,1)" in "K2 column".

2. Now, i want to generate only those unique individual list between two dates in the table "I5:M5". I have tried the following formula in "I5" column.

=IFERROR(SMALL(IF(C2:C32=Helper,IF(B2:B32>=I2,IF(B2:B32<=J2,ROW(B2:B32)-1))),ROW(B1)),"")

Kindly help me.

Thanks in advance

[oeldere]

i4 =if(and(B4>=$K$4,B4<=$L$4),"Yes","") and drag down

result G4 =IF(AND(H4=1,I4="yes"),"yes","") and drag down

G1 =SUBTOTAL(103,$G$4:$G$34)

and filter in G3 on Yes.

See the attached file.

[bala04msw]

Dear Mr. oeldere,

Thanks for your efforts and support.

Since "H" Column is there and i am getting my desired result (Unique count) in M4. Then what is the purpose of "G" and "I" Column. More over my basic requirement is extracting list of those unique count in the table, which is in the attachment K to O column.

Kindly help me for that.

Regards

[oeldere]

why not use the built in filter in Excel (as offered in my solution).

[AliGW]

why not use the built in filter in Excel (as offered in my solution).

I think because the question is how to EXTRACT a list, not FILTER a list.

Sorry, but I don't have time for this today. However, looking at the OPs file, there is no INDEX function in the formula he has tried to use, so that will need remedying. If nobody else chips in before I'm back, I'll have a look later. Is this list meant to be sorted? And if so, how - by name or by date?

[bala04msw]

Dear Ms. Ali,

Thanks for your reply and guidance. I need it by name only.

Thanks

[oeldere]

@AliGW

Hi Ali, thanks for your comment.

I don't read in the question, it had to be an extract of the data.


But as you already been noticed, a lot of forummembers ask for a solution THEY think is the best on their problem.

Adding a differant approach is often an eye-opener.

The differant approach is often, on the long term, a better solution.

I think a filter on the data, if the example represents the real data, is a good (maybe better) solution.

[AliGW]

But as you already been noticed, a lot of forummembers ask for a solution THEY think is the best on their problem.

Adding a differant approach is often an eye-opener.

Yes, I understand that, but the OP had already rejected your suggestion of a filter once, so I think he wants to do it his way. Have you looked at his file and the layout of the data? Anyway, I must dash!

[Pete_UK]

Put this array* formula in I5:

=IFERROR(INDEX(C$2:C$32,SMALL(IF((G$2:G$32=1)*(B$2:B$32>=$I$2)*(B$2:B$32<=$J$2),ROW(B$2:B$32)-1),ROW(B1))),"")

then copy down.

*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

[TudyBTH]

Hi,
Try this in I5 and drag to right and down, it is array formula, use Ctrl+Shift+Enter

Please Login or Register  to view this content.

does not require helper column
se attached

[bala04msw]

Dear Mr. Pete,

Thanks for your support and guidance. This formula only returns name and not other details, as i structured in the table. Hope, i have to change the index value.

Great and looking forward to learn from you lot.

Regards

[bala04msw]

Dear Mr. Tudy,

Thanks a lot and it works well. I want to learn from you lot. how the formula has been framed logically? Could you please refer some books to learn array formula.

Million thanks.

Bala

[AliGW]

@Bala - I thought you wanted your extracted list in name order?

[Pete_UK]

Sorry, I thought you only wanted the name. Put this array* formula in I5 instead:

=IFERROR(INDEX(B$2:B$32,SMALL(IF(($G$2:$G$32=1)*($B$2:$B$32>=$I$2)*($B$2:$B$32<=$J$2),ROW($B$2:$B$32)-1),ROW(B1))),"")

then you can copy across and down as required. Use the Format Painter icon to copy the formats from B2:F2 to the new table.

Hope this helps.

Pete

[TudyBTH]

Hi Bala,
I think the best way to learn is the exercises. On this fornum you find the most and get assistance if you do not understand something. Just ask.

[bala04msw]

Dear Ms. Ali,

thanks for your reply. Yes i wanted to be in name order and Mr. Pete and Mr.Tudy supported me in this requirement.

Looking forward your support and guidance.

Regards

[Pete_UK]

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[bala04msw]

Dear Mr. Pete,

I am really sorry for this, i already given the reputation as well as marked as SOLVED. since new to this forum, few options are really unknown to me. Please do not mind and i will cautiously do it in next time.
Your guidance also extremly helped me during this process.

Regards

[AliGW]

Dear Ms. Ali,

thanks for your reply. Yes i wanted to be in name order and Mr. Pete and Mr.Tudy supported me in this requirement.

Looking forward your support and guidance.

Regards

I have tried both and neither sorts the list alphabetically, which is what I thought you wanted. With Tudy's formula they are in date order. As long as you are happy with this, then that's fine!

[bala04msw]

Ms. Ali,

Yes i am happy with Mr. Tudy's formula and both Mr. Pete's formula. Both are working and meets my requirement. Thanks for your concern and guidance.

Bala