|
|
 Question 1 - Dynamically copy unique combination of cells froma database to a range and then analyse the data
In the attached workbook, you will see my data in range A12:F32 of worksheet 2. As you will notice, the only inique filed is the Unit ID. What i want to do is add the quantities for every combination of Plant name, state, fuel type and mover type. While i can easily sum up the quantities by using the Sum(if(( array formula, my main problem is to dynamically copy the unique combination of the 4 fields mentioend earlier to a seperate range and then sum the quantities. From: Kanwaljeet
Workaround Please find herein my workaroud to the problem. My solution is in range A1:E9 of the workbook. The sum of quantities is given in range E2:E9 of the workbook. Also, i have not used an array formula as mentioened in your question because array formulas add to file bloat and adversely affect the speed of execution. Hope this helps.
Question 2 - Count unique items in a Pivot table In the attached workbook, i have the base data in range A1:D10 of the worksheet named "Original data". My objective is to know the number of sales persons per office location. I creatd a pivot table from the input data (Please refer worksheet named "Original Solution") to obtain the answer but the solution seems incorrect because the figure in cell C5 should be 1 and not 2 i.e. there is only one sales person (appearing twice inthe base data) for Office Location A. Please help me out. From: Nitin Agarwal Workaround Please find herein my workaroud to the problem at hand. My workround is in worksheet named "Ashish's solution". In the original data sheet, i have added formulas in columns E and F and and have included these 2 columns in the pivot table range. Then in the data area of the pivot table, i just dragged the countif field. Hope this helps.
|
|