Extraction of data using arrays

[Plu2o]

OK here goes. I will try and explain this the best I can.

I have a large database for equipment fault tracking. I want to filter the data to a selected few (easy done) but then I want to calculate the total time the for each particular type of fault on each piece of equipment. This is easily done in pivot charts however i need to show the results for the fault type even if there were none recorded and I also need to be able to control the colours on the chart. I have tried using array formulas however I need around 500 so excel doesnt like that. Does anyone have any other suggestions?

 

[Akashwani]

Hi,

Welcome to MrExcel.

I'm not really sure what it is you want to do!!
If you can provide a sample of your data and the expected results you will stand a better chance of getting a suitable answer, which this may not be....

Sample Data..

Excel Workbook
	A	B	C	D	E	F
1	ID Number	Name	Time	Animal	Transport	Date
2	AA10000	tom	1:10:00	cat	Bike	01/01/2011
3	AA10001	****	2:10:00	dog	Car	02/01/2011
4	AA10002	Harry	1:15:00	Horse	Bus	03/01/2011
5	AA10003	Sally	10:00:00	Mouse	train	04/01/2011
6	AA10004	Mary	2:20:00	fish	Tram	05/01/2011
7	AA10001	Jim	5:40:00	pig	Plane	01/01/2011
8	AA10003	Jill	3:20:00	Panda	Tut Tut	07/01/2011
9	AA10007	Jo	4:10:00	Tiger	Coach	08/01/2011
10	AA10008	Joe	15:00:00	Lion	Carriage	09/01/2011
11	AA10009	Tim	6:25:00	Bull	Boat	10/01/2011
12	AA10010	Stan	1:45:00	cow	trike	01/01/2011
13	AA10000	tom	8:00:00	cat	Bike	12/01/2011
14	AA10004	Dom	1:20:00	dog	Car	13/01/2011
15	AA10000	Harry	2:00:00	Horse	Bus	04/01/2011
Sheet1

Result

Excel Workbook
	A	B	C	D
1	9	Unique List	Amount	Sort By
2	*	AA10000	11:10:00	3
3	*	AA10001	7:50:00	4
4	*	AA10002	1:15:00	9
5	*	AA10003	13:20:00	2
6	*	AA10004	3:40:00	7
7	*	AA10007	4:10:00	6
8	*	AA10008	15:00:00	1
9	*	AA10009	6:25:00	5
10	*	AA10010	1:45:00	8
11	*	*	*	*
12	*	*	*	*
Sheet2

The formulas in B2:D2 need to be copied down as far as your expect results to show (the number of rows displayed in A1).

Here is a sample (VBA free) file for you to look at with a greater range of data....
Plu2o.xls

I hope this helps point you in the right direction.

Good luck.

Ak

 

[Plu2o]

Thanks Akashwani.

I appreciate your time taken to answer this.
I managed to have a moment of clarity very late last night and found I could use the GETPIVOTDATA function
Basically I have created a pivot table showing duration and count for each piece of equipment with the delay cause across the top. I have then used formula
GETPIVOTDATA("Count of Duration",'Random Delays Pivot'!$A$3,"Cause",F$3,"Equipment",$A5)
This pulls the data relating to the equipment number and only looks at that particular delay.

Thanks for your time as it is interesting to see other methods of approach