Return the correct result

[singh01]

Hello,

I am using excel 2003 and have the below sample data (from a main excel sheet with 1000's of rows).

A	B	C	D	E	F
ID	Owner	Region	Action	Date	Timestamp
1001	Shawn	APR	Closed	12/01/2015	12:52:06am
1001	Shawn	APR	opened	12/01/2015	12:43:06am
1001	Shawn	APR	created	12/01/2015	12:42:01am
1002	Isabel	AMRS	Closed	12/01/2015	1:43:44am
1002	Isabel	AMRS	opened	12/01/2015	1:34:30am
1002	Isabel	AMRS	created	12/01/2015	01:14:47am
1003	Eric	AMRS	Closed	12/03/2015	1:43:14AM
1003	Eric	AMRS	opened	12/03/2015	1:32:52AM
1003	Eric	AMRS	created	12/03/2015	1:29:59AM
1004	Kristine	EMEA	Closed	12/04/2015	4:37:43AM
1004	Kristine	EMEA	opened	12/04/2015	2:07:13AM
1004	Kristine	EMEA	created	12/04/2015	1:58:38AM

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>

the data is split by ID (column A) over several rows, the data shows who reviewed the data (col b) and the date and time (col e&f) it was reviewed.

I would simply like to show for example that for how many individual IDs are associated with each region, who owned the data, and the average time between created and closed (col D&F).

I haven't used excel in a long time, and finding it difficult to know where to begin with formulas. I have been able to count the number of IDS excluding the dupes, but not much else! Appreciate your help.

 

[Taul]

Hi,
I would suggest using a Pivot Table for 1000's of rows.
Select the range for your data including a header row
Create a Pivot table (I can't recall the command for 2003, but in 2010 its on the ribbon under Insert)

You can experiment with dragging items from the field list into various areas of the pivot table.
for a first gambit I would suggest the following:-
Report Filter = Date & Action
Column Labels = Region
Row Labels = Owner & Time Stamp
Sum Values = Sum of ID

Paul.

 

[FDibbins]

This is what I have come up with to start (IF I understand what you want)....

	H​	I​	J​	K​
1​		APR	AMRS	EMEA
2​	1001​	3​	0​	0​
3​	1002​	0​	3​	0​
4​	1003​	0​	3​	0​
5​	1004​	0​	0​	3​

I1=IFERROR(INDEX($C$2:$C$13,MATCH(0,INDEX(COUNTIF($H$1:H1,$C$2:$C$13),0,0),0)),"")
copied across
H2=IFERROR(INDEX($A$2:$A$13,MATCH(0,INDEX(COUNTIF($H$1:H1,$A$2:$A$13),0,0),0)),"")
copied down
These 2 will pull out unique entries for column and row headings

I2=COUNTIFS($A:$A,$H2,$C:$C,I$1)
copied down and across
This will show you where you have matches. You could modify this for some of the other info you wanted