Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross tab query
I have a worksheet with 2 columns.
First column contains a date (several duplicates possible, even in hundreds). Second column contains a code (4 or 5 different values). Duplicates possible for first column + second column combination. For example, for a given date, many records possible with same code value. How could I write a formula to give me per code value the "unique" number of dates found between 2 given date values. Meaning, as parameter I can pass three arguments viz. code value start date value end date value In return I want the number of unique dates (including start and end date) to be returned. All help appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross tab query
Criterias
In D1: holds code value In D2: holds start date In D3: holds end date =SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0)) ctrl+shift+enter, not just enter "DKS" wrote: I have a worksheet with 2 columns. First column contains a date (several duplicates possible, even in hundreds). Second column contains a code (4 or 5 different values). Duplicates possible for first column + second column combination. For example, for a given date, many records possible with same code value. How could I write a formula to give me per code value the "unique" number of dates found between 2 given date values. Meaning, as parameter I can pass three arguments viz. code value start date value end date value In return I want the number of unique dates (including start and end date) to be returned. All help appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross tab query
I did not understand the last 2 "Date" parameters. What does that refer to?
Should it refer to the column that contains all the dates? "Teethless mama" wrote: Criterias In D1: holds code value In D2: holds start date In D3: holds end date =SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0)) ctrl+shift+enter, not just enter "DKS" wrote: I have a worksheet with 2 columns. First column contains a date (several duplicates possible, even in hundreds). Second column contains a code (4 or 5 different values). Duplicates possible for first column + second column combination. For example, for a given date, many records possible with same code value. How could I write a formula to give me per code value the "unique" number of dates found between 2 given date values. Meaning, as parameter I can pass three arguments viz. code value start date value end date value In return I want the number of unique dates (including start and end date) to be returned. All help appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross tab query
Yes, it is the column. I tried it out and it gives the correct results.
Thanks for your help. "DKS" wrote: I did not understand the last 2 "Date" parameters. What does that refer to? Should it refer to the column that contains all the dates? "Teethless mama" wrote: Criterias In D1: holds code value In D2: holds start date In D3: holds end date =SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0)) ctrl+shift+enter, not just enter "DKS" wrote: I have a worksheet with 2 columns. First column contains a date (several duplicates possible, even in hundreds). Second column contains a code (4 or 5 different values). Duplicates possible for first column + second column combination. For example, for a given date, many records possible with same code value. How could I write a formula to give me per code value the "unique" number of dates found between 2 given date values. Meaning, as parameter I can pass three arguments viz. code value start date value end date value In return I want the number of unique dates (including start and end date) to be returned. All help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Query with a twist.... (Cross Posted) | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |