Regarding Arrays
 

Hi
Here is the excel data how my data is distributed.

Incident Description Status Date logged
313 XXXXX Review December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

Regarding Arrays
 

Use AutoFilter:

1. click on the headercell containing "Status" and pull-down:

Data Filter Autofilter

This will result in pull-downs for each header cell

2. click on the Status pull-down and select "review"
3. click on the Dat oull-down and select the desired date.
--
Gary''s Student - gsnu200819


"Raj" wrote:

Hi
Here is the excel data how my data is distributed.

Incident Description Status Date logged
313 XXXXX Review December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

Regarding Arrays
 

You can get a count of those records with this formula:

=SUMPRODUCT((C1:C1000="Review")*(TEXT(D1:D1000,"dd mmm yy")="09 Dec
08"))

Change the ranges to suit.

You can also "see" the records by applying filters to the status and
date columns. Then you could highlight the visible records and <copy
them to another sheet to produce an extract.

Hope this helps.

Pete

On Dec 10, 2:07*pm, Raj wrote:
Hi
Here is the excel data how my data is distributed.

Incident * * * * Description * * * * *Status * * * Date logged * *
313 * * * * * * * * *XXXXX * * * * * * Review * * *December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

Regarding Arrays
 

.. the no.of incidents which are in review status logged on December 9

Try something like this, normal ENTER:
=SUMPRODUCT((C2:C100="Review")*(TEXT(D2:D100,"ddmm myyyy")="09Dec2008"))

The above presumes data in col D (date logged) is cleaned up as real dates

With col D "as-is", you could try:
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2 :D100,"GMT",""),"ddmmmyyyy")="09Dec2008"))
which removes the "GMT"


or perhaps even:
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2 :D100,CHAR(10)&"GMT",""),"ddmmmyyyy")="09Dec2008") )
which removes both the preceding hard return & "GMT"

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Raj" wrote:
Here is the excel data how my data is distributed.

Incident Description Status Date logged
313 XXXXX Review December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

Regarding Arrays
 

Hi Max
I am unable to do this if i use the Formula u have give its giving the Value
N/A
Here is the exact sheet format
A B C D E
F G H
31490 XXXX XXXX XXX XXXX Review XXXX December 9, 2008 05:00 PM GMT+01:00

I have placed all the data in the Sheet named Requests. Now i want to pull
the data to Summary Sheet with the Count total incidents which are in review
Status (Colmun F) which are logged on December 9(Column H)

Please do helpme to resolve



"Max" wrote:

.. the no.of incidents which are in review status logged on December 9

Try something like this, normal ENTER:
=SUMPRODUCT((C2:C100="Review")*(TEXT(D2:D100,"ddmm myyyy")="09Dec2008"))

The above presumes data in col D (date logged) is cleaned up as real dates

With col D "as-is", you could try:
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2 :D100,"GMT",""),"ddmmmyyyy")="09Dec2008"))
which removes the "GMT"


or perhaps even:
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2 :D100,CHAR(10)&"GMT",""),"ddmmmyyyy")="09Dec2008") )
which removes both the preceding hard return & "GMT"

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Raj" wrote:
Here is the excel data how my data is distributed.

Incident Description Status Date logged
313 XXXXX Review December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

Regarding Arrays
 

In Requests,
Use a helper col I to clean-up col H

In I2, copied down:
=IF(H2="","",IF(ISERROR(SEARCH("GMT",H2)),H2,SUBST ITUTE(H2,MID(H2,SEARCH("GMT",H2),99),"")+0))

Then in your Summary sheet, use:
=SUMPRODUCT((Requests!F2:F100="Review")*(TEXT(Requ ests!I2:I100,"ddmmmyyyy")="09Dec2008"))
to return the desired count

P/s: Remember to press the YES button below ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"Raj" wrote:
Hi Max
I am unable to do this if i use the Formula u have give its giving the Value
N/A
Here is the exact sheet format
A B C D E
F G H
31490 XXXX XXXX XXX XXXX Review XXXX December 9, 2008 05:00 PM GMT+01:00

I have placed all the data in the Sheet named Requests. Now i want to pull
the data to Summary Sheet with the Count total incidents which are in review
Status (Colmun F) which are logged on December 9(Column H)

Please do help me to resolve