Listing from given range matching values

[prasaddn]

Hi All,


I have table data as show below with 3 column of information like agentname, program name and date. In the next column I have place holder for entering program name and date.


Now, the given date and program, I need the list of agent names in last column.


My data looks like this:

AgentName  ProgramName   Date           EnterProg       Prog 3         List

Agent 3    Prog 3       3/16/2012       EnterDate       3/16/2012      0

Agent 2    Prog 3       3/16/2012

Agent 4    Prog 4       3/16/2012

Agent 2    Prog 5       3/16/2012

Agent 3    Prog 5       3/11/2012

Agent 1    Prog 1       3/11/2012

Agent 4    Prog 5       3/11/2012

Agent 1    Prog 3       3/11/2012

Agent 1    Prog 1       3/10/2012

Agent 2    Prog 2       3/10/2012

Agent 3    Prog 1       3/1/2012

Agent 4    Prog 1       3/1/2012

Agent 3    Prog 4       3/1/2012

I tried using the formula like this in List col:

=INDEX($D$198:$D$210,SMALL(SUMPRODUCT(($C$2:$C$14=$E$2)*($B$2:$B$14=$E$1)*(ROW($A$2:$A$14))),1))

=INDEX($D$198:$D$210,SMALL(SUMPRODUCT(($C$2:$C$14=$E$2)*($B$2:$B$14=$E$1)*(ROW($A$2:$A$14))),2))

=INDEX($D$198:$D$210,SMALL(SUMPRODUCT(($C$2:$C$14=$E$2)*($B$2:$B$14=$E$1)*(ROW($A$2:$A$14))),3))


result is always 0 or #num, when i use the evaluate function, I realise that sumproduct is able to find the rows where my criteria meets, but before I list the smallest on them, it gets summedup and so the array for small() will have only one value and not the list of row numbers matching.


I can always go for vba code and PT, but want to learn formula based only, I know with some formulaes indirect(), address() or transpose () this can be done.


Looking for some help in this area.


Regards,

Prasad DN

 

[prasaddn]

continuation:


AgentNames are listed above as Agent 1, Agent 2, Agent 3 etc

ProgramName are listed above as Prog 1, prog 2, prog 3 etc


Regards,

Prasad DN

PS: I am not able to paste data like table format

 

[SirJB7]

Hi, prasaddn!

Give a look at this file: http://dl.dropbox.com/u/60558749/Listing%20from%20given%20range%20matching%20values%20%28for%20Prasaddn%20at%20chandoo.org%29.xlsx

And don't ask me where I found it, because it was here and I don't remember.

Regards!

PS: For table formatting embed copied range within back-ticks `

 

[MBegg]

Can do a simpler version using index / match countif:


{=INDEX($A$2:$A$14,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$14),0))}


OR you can work a little harder and get them in alphabetical order...


{=INDEX($A$2:$A$14,MATCH(MIN(IF(COUNTIF($H$1:H1,$A$2:$A$14)=0,1,MAX((COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1)*2))*(COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1)),COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1,0))}


See example file below. Note both of these are array formulas (ctrl + shift + enter).


http://dl.dropbox.com/u/7313338/range%20matching.xlsx

 

[MBegg]

Sorry, think I missed the conditions you were trying to put on. Simple version:


{=INDEX($A$2:$A$14,MATCH(1,IF($B$2:$B$14=$E$1,1,0)*IF($C$2:$C$14=$E$2,1,0)*(COUNTIF($F$1:F1,$A$2:$A$14)=0),0))}


Sorted alphabetically:


{=INDEX($A$2:$A$14,MATCH(MIN(IF(IF($B$2:$B$14=$E$1,1,0)*IF($C$2:$C$14=$E$2,1,0)*(COUNTIF($H$1:H1,$A$2:$A$14)=0),1,MAX((COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1)*2))*(COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1)),COUNTIF($A$2:$A$14,"<"&$A$2:$A$14)+1,0))}


Updated example file...


http://dl.dropbox.com/u/7313338/range%20matching.xlsx

 

[prasaddn]

Thank you MBegg!


Dear Pablo, as usual, I am not able to access to your file, if your formula is different from that of MBegg, please do send to my email ID (hope you have it )


Thank you Pablo.


Regards,

Prasad DN

 

[SirJB7]

Hi, prasaddn!

Just sent by mail.

One question: you can't access my dropbox public folder but for example MBegg's one yes, or you can't access any dropbox folder?

Regards!

 

[prasaddn]

Thank you again,


Due to firewall issues, I cant access any of the fileshare sites.


MBegg's formula is working fine. Well,I have received your file, but the formula is not changing results when i change the date or program. It is just giving unique agent names list.


{=INDEX(A$2:A$14,SMALL(IF(MATCH(A$2:A$14,A$2:A$14,0)=ROW(A$2:A$14)-1,ROW(A$2:A$14)-1,""),ROW()-1))}


Formula does not check the condition of program name and date that are in E1 and E2.


Just wanted to keep you informed.


Regards,

Prasad DN

 

[prasaddn]

I am just trying to learn the CSE formula or how to store the array result in rows. etc.


Regards,

Prasad DN