| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct to create a unique list
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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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 "Excel 2003 - SPB" wrote: 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
Are there any empty/blank cells in either the date range or the tail
number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
no blank cells in either columns spb "T. Valko" wrote: Are there any empty/blank cells in either the date range or the tail number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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" wrote: Are there any empty/blank cells in either the date range or the tail number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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) -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: Are there any empty/blank cells in either the date range or the tail number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
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,"ddmmy y")) works well Thanks again SPB "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: Are there any empty/blank cells in either the date range or the tail number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct to create a unique list
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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,"ddmmy y")) works well Thanks again SPB "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: Are there any empty/blank cells in either the date range or the tail number range? Well? The answer to that question can either add complexity of eliminate unneeded complexity! -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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" wrote: 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? -- Biff Microsoft Excel MVP "Excel 2003 - SPB" wrote in message ... 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$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to create a list of unique dates? | Excel Discussion (Misc queries) | |||
| Tough: Vlookup, Match, Sumproduct? To create list of persistence | Excel Discussion (Misc queries) | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
| Unique sumproduct with criteria! | Excel Worksheet Functions | |||
Linear Mode
