sumproduct to create a unique list

[Excel 2003 - SPB]

Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2ata!$F$3000<>"")*1/COUNTIF(Data!$F$2ata!$F$3000,Data!$F$2ata!$F$3000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2ata!$F$3000,MATCH(TRUE,ISERROR(MATCH(Data!$F$2ata!$F$3000,$V$129:$V129,0)),0),1))

The V129 is where I want to start the list I estimate I will need place ment
for about 10 aircraft max

 

[Barb Reinhardt]

Let's say your aircraft names are in A1:A100,Dates are in B1:B100. I'm
assuming you are looking for "Aircraft Name" for March 2007.

=SUMPRODUCT(--(A1:A100="Aircraft
Name'),--(Year(B1:B100)=2007),--(Month(B:B100)=3))

HTH,
Barb Reinhardt

 

[T. Valko]

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail number
range?

The date range of interest, do you want just a specific month or do you want
the specific month of a specific year?

 

[Excel 2003 - SPB]

unique tail numbers by month only, The rest of the report used a cell ref for
the date
the previous post did not work, thanks for any suggestions.
SPB

 

[T. Valko]

Are there any empty/blank cells in either the date range or the tail
Well?

The answer to that question can either add complexity of eliminate unneeded
complexity!

 

[Excel 2003 - SPB]

no blank cells in either columns
spb

Well?

The answer to that question can either add complexity of eliminate unneeded
complexity!

 

[Excel 2003 - SPB]

Recap
no blank cells in either coulmn
based on a cell address of J4 Need the results from a choosen month
to create a unique list of aircraft used in that month

SPB

 

[T. Valko]

Ok...

Both of these formulas are array formulas**.

Use a separate cell to get the total count of tail numbers that meet the
criteria.

Date = named range for your date column
TN = (Tail Number) named range for the acft tail numbers

Count of distinct tail numbers for the month of 5 (May):

=COUNT(1/FREQUENCY(IF(MONTH(Date)=5,MATCH(TN,TN,0)),ROW(TN)-MIN(ROW(TN)+1)))

Assume the above formula is entered in cell G1.

Formula to extract distinct tail numbers:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(Date)=5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN)-MIN(ROW(TN))+1,ROW(TN)-MIN(ROW(TN))+1)),ROWS($1:1))),"")

If the first row of your ranges is set and will not change we can shorten
this a little. For example, your posted formula uses references to ranges
that start in row 2. In the above formula we calculate an offset from the
starting row so that this formula can be used by anyone. In other words,
it's generic. We can shorten it a little by hardcoding this offset rather
than calulating it. So, if the data ranges start on row 2 we can this
version:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(date)=5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN)-1,ROW(TN)-1)),ROWS($1:1))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Excel 2003 - SPB]

Away for a few days
Works great and I am starting to understand it a little, used a simular
formula in another area of the spreadsheet and changed the date ref to
--(TEXT(Login!$E$2:$E$2972,"ddmmyy")=TEXT($A8,"ddmmyy")) works well
Thanks again

SPB

 

[T. Valko]

You're welcome. Thanks for the feedback!