Extract Unique Records Requires Re-Calculation !!!

[bisho]

Hi,</SPAN>

I have 2 questions, the first one is: in the below example I am trying to extract the unique records from A to D, the formula is correct and it does the job only if I recalculate the column D, if I drag down I get only “Red”, if I recalculate I get the correct values !!!! any idea why ?? is there any better formula to use for this purpose ??</SPAN>

Sheet1

*	A	B	C	D	E
1	Original	*	Unique
2	Red	10	*	Red	37
3	Blue	0	*	Red	37
4	Red	5	*	Red	37
5	Yellow	7	*	Red	37
6	Green	13	*	Red	37
7	Blue	0	*	Red	37
8	Green	18	*	Red	37
9	Yellow	11	*	Red	37
10	Red	22	*	Red	37
11	Black	6	*	Red	37
12	Purple	9	*	Red	37

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 78px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 80px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula D2 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$12),0)),"")} E2 =IF(D2<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D2),"") D3 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D2,$A$1:$A$12),0)),"")} E3 =IF(D3<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D3),"") D4 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D3,$A$1:$A$12),0)),"")} E4 =IF(D4<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D4),"") D5 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D4,$A$1:$A$12),0)),"")} E5 =IF(D5<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D5),"") D6 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D5,$A$1:$A$12),0)),"")} E6 =IF(D6<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D6),"") D7 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D6,$A$1:$A$12),0)),"")} E7 =IF(D7<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D7),"") D8 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D7,$A$1:$A$12),0)),"")} E8 =IF(D8<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D8),"") D9 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D8,$A$1:$A$12),0)),"")} E9 =IF(D9<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D9),"") D10 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D9,$A$1:$A$12),0)),"")} E10 =IF(D10<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D10),"") D11 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D10,$A$1:$A$12),0)),"")} E11 =IF(D11<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D11),"") D12 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D11,$A$1:$A$12),0)),"")} E12 =IF(D12<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D12),"") <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4 </SPAN>



After recalculation the results are like below and here is my second question, how can I extract the unique records except the ones that their SUMIFS is zero ( I mean do not extract “Blue” in this example)</SPAN>

Thanks.</SPAN>

Sheet1

*	A	B	C	D	E
1	Original	*	Unique
2	Red	10	*	Red	37
3	Blue	0	*	Blue	0
4	Red	5	*	Yellow	18
5	Yellow	7	*	Green	31
6	Green	13	*	Black	6
7	Blue	0	*	Purple	9
8	Green	18	*	*	*
9	Yellow	11	*	*	*
10	Red	22	*	*	*
11	Black	6	*	*	*
12	Purple	9	*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 78px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 80px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula D2 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$12),0)),"")} E2 =IF(D2<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D2),"") D3 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$12),0)),"")} E3 =IF(D3<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D3),"") D4 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D3,$A$2:$A$12),0)),"")} E4 =IF(D4<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D4),"") D5 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D4,$A$2:$A$12),0)),"")} E5 =IF(D5<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D5),"") D6 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D5,$A$2:$A$12),0)),"")} E6 =IF(D6<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D6),"") D7 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D6,$A$2:$A$12),0)),"")} E7 =IF(D7<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D7),"") D8 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D7,$A$2:$A$12),0)),"")} E8 =IF(D8<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D8),"") D9 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D8,$A$2:$A$12),0)),"")} E9 =IF(D9<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D9),"") D10 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D9,$A$2:$A$12),0)),"")} E10 =IF(D10<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D10),"") D11 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D10,$A$2:$A$12),0)),"")} E11 =IF(D11<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D11),"") D12 {=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($D$1:D11,$A$2:$A$12),0)),"")} E12 =IF(D12<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D12),"") <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4 </SPAN>

 

[Fazza]

hi

Maybe the simplest options is a pivot table - it requires no formulas or code. A query table might similarly be an option. Set up the pivot table from a new file. Assuming you have current headers MyColor & MyValue and the source data table is named MyData the SQL would be like below. From the new file ALT-D-P & follow the wizard, choosing external data source at the first step. At the last step choose the option to edit in MS Query. The absolute value > 0.0001 is to avoid rounding errors for zero sums that are non-zero due to floating point errors. Use the same SQL if you choose a query table - ALT-D-D-N & follow the wizard.

HTH

SELECT MyColor, SUM(MyValue) AS [MyValue]
FROM MyData
GROUP BY MyColor
HAVING ABS(SUM(MyValue))>0.0001