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).


[TABLE="width: 430"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Owner[/TD]
[TD]Region[/TD]
[TD]Action[/TD]
[TD]Date[/TD]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Shawn[/TD]
[TD]APR[/TD]
[TD]Closed[/TD]
[TD]12/01/2015[/TD]
[TD]12:52:06am[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Shawn[/TD]
[TD]APR[/TD]
[TD]opened[/TD]
[TD]12/01/2015[/TD]
[TD]12:43:06am[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Shawn[/TD]
[TD]APR[/TD]
[TD]created[/TD]
[TD]12/01/2015[/TD]
[TD]12:42:01am[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Isabel[/TD]
[TD]AMRS[/TD]
[TD]Closed[/TD]
[TD]12/01/2015[/TD]
[TD]1:43:44am[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Isabel[/TD]
[TD]AMRS[/TD]
[TD]opened[/TD]
[TD]12/01/2015[/TD]
[TD]1:34:30am[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Isabel[/TD]
[TD]AMRS[/TD]
[TD]created[/TD]
[TD]12/01/2015[/TD]
[TD]01:14:47am[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Eric[/TD]
[TD]AMRS[/TD]
[TD]Closed[/TD]
[TD]12/03/2015[/TD]
[TD]1:43:14AM[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Eric[/TD]
[TD]AMRS[/TD]
[TD]opened[/TD]
[TD]12/03/2015[/TD]
[TD]1:32:52AM[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Eric[/TD]
[TD]AMRS[/TD]
[TD]created[/TD]
[TD]12/03/2015[/TD]
[TD]1:29:59AM[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]Kristine[/TD]
[TD]EMEA[/TD]
[TD]Closed[/TD]
[TD]12/04/2015[/TD]
[TD]4:37:43AM[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]Kristine[/TD]
[TD]EMEA[/TD]
[TD]opened[/TD]
[TD]12/04/2015[/TD]
[TD]2:07:13AM[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]Kristine[/TD]
[TD]EMEA[/TD]
[TD]created[/TD]
[TD]12/04/2015[/TD]
[TD]1:58:38AM[/TD]
[/TR]
</tbody>[/TABLE]


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)....
[Table="width:, class:grid"][tr][td] [/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]APR[/td][td]AMRS[/td][td]EMEA[/td][/tr]

[tr][td]

2​

[/td][td]

1001​

[/td][td]

3​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td]

3​

[/td][td]

1002​

[/td][td]

0​

[/td][td]

3​

[/td][td]

0​

[/td][/tr]

[tr][td]

4​

[/td][td]

1003​

[/td][td]

0​

[/td][td]

3​

[/td][td]

0​

[/td][/tr]

[tr][td]

5​

[/td][td]

1004​

[/td][td]

0​

[/td][td]

0​

[/td][td]

3​

[/td][/tr]
[/table]
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