Comparison of two lists with criteria

[BenElliott]

I have an Excel Table named "TalkNames" as follows, the full table contains 194 talks listed:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Talk Number
[/TD]
[TD="align: center"]Talk Title
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]AAAA
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]BBBB
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]CCCC
[/TD]
[/TR]
</tbody>[/TABLE]







A second table is a database of when each talk is given, where and by whom. I then extract the data from this database to show the various dates when these talks are given. This extraction is on tab "Talk List Given", thus:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Column 'A'
[/TD]
[TD="align: center"]Talk Number
[/TD]
[TD="align: center"]Times Given
[/TD]
[TD]Date 1
[/TD]
[TD]Date 2
[/TD]
[TD]Date 3
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD]1 Nov 2015
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2[/TD]
[TD]22 July 2018
[/TD]
[TD]2 Aug 2015
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]1
[/TD]
[TD]25 May 2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







I can compare the list of talks available with the list of talks given and extract the talks not given. This extract is placed on tab "Talks not given". The count of those not given at all is in cell A2 using the following formula: =SUMPRODUCT(--ISNA(MATCH(TalkNames[Talk Number],'Talk List Given'!$B$3:$B$195,0)))
the list is produced in column 'A' from row 5 downward using the formula: =IF(ROWS($A$5:$A5)>$A$2,"",INDEX(TalkNames[Talk Number],AGGREGATE(15,6,(ROW(TalkNames[Talk Number])-ROW($A$2)+1)/ISNA(MATCH(TalkNames[Talk Number],'Talk List Given'!$B$3:$B$197,0)),ROWS($A$5:A5))))

I am struggling with adding a criteria to this extract. I would like to add to the list of talks not given the numbers of talks that have not been given for a specified period of time (Currently 3 years but selectable from a named list of 1, 2, 3 or 4 years) as given in column 'C' "Date 1" on tab "Talk List Given".

Is it possible to add such a criteria or is there another was of obtaining this list?

Your thoughts and pointers toward the solution would be greatly appreciated.

Ben

 

[Col Delane]

Hi Ben

I've only had a quick look at your post, so may not offer the particular solution you're seeking, however, it might make it easier to achieve your desired result if you:

1. convert 'Talk List Given'!$B$3:$B$195 into an Excel Table (named TalksGiven)
2. expand the existing "TalkNames" table by adding the following columns to the right of 'Talk Title' column, with these formulas therein:
   
   1. Talks Given =Countifs( TalksGiven[Talk Number], TalkNames[@[Talk Number]])
      
   2. No Talks Given =TalkNames[@[Talks Given]]=0 (will return TRUE or FALSE)
      
3. Above the heading of 'No Talks Given' column, or elsewhere, enter the following formula to count the number of talks NOT delivered:
   • =countifs(TalkNames[No Talks Given],TRUE)

Item 2 creates a single table that is combined master list of the talks available (first two columns) and a summary of the "performance" without duplicating the talk ID which should make it easier to extract various statistics.


Hope that helps in some way.

 

[Aladin Akyurek]

In A5 control+shift+enter:

=IFERROR(INDEX(TalkNames[Talk Number],SMALL(IF(ISNA(MATCH(TalkNames[Talk Number],'Talk List Given'!$B$3:$B$197,0)),ROW(TalkNames[Talk Number])-ROW(INDEX(TalkNames[Talk Number],1,1))+1,ROWS($A$5:A5))),"")

is a lot more easier to expand with additional conditions than an array-processing AGGREGATE formula, but I gather you dislike array-processing formulas requiring control+shift+enter.

FYI...

ROW(INDEX(TalkNames[Talk Number],1,1))

means/refers to the row number of the first cell of the range TalkNames[Talk Number].