need help with formula

[Joefried]

hi please help

i need help with the following info

bring me results from sheet2 A:A when you find 0.01 or more on that row in B
but i need one answer per line and do not duplicate the same answer twice

Best Regards
Joe

 

[T. Valko]

hi please help

i need help with the following info

bring me results from sheet2 A:A when you find 0.01 or more on that row in B
but i need one answer per line and do not duplicate the same answer twice

Best Regards
Joe

Can you post some sample data and show us what result you expect?

 

[Joefried]

Hi and thanks for looking at my question

these is what i have


2705730962 $0.00
2904352269 ($9.95)
2904352269 ($0.10)
2805062125 ($0.10)
2805013406 $0.10
2805013406 $0.00
2705730964 $0.10
2705730964 $0.00
2904327435 ($10.08)
2904330043 ($0.10)
2904330043 ($0.61)
2805085086 ($0.10)
2705730962 $0.10
2705730962 $0.00
2805013406 $0.10
2805013406 $0.00
2904313230 $0.10

and this is what i want


2805013406
2705730964
2705730962
2805013406
2904313230

again look in sheet2 row B for an amount that's more then 0.01 and when you find it bring me back results from row A but don't duplicate

thanks

 

[T. Valko]

Hi and thanks for looking at my question

these is what i have


2705730962 $0.00
2904352269 ($9.95)
2904352269 ($0.10)
2805062125 ($0.10)
2805013406 $0.10
2805013406 $0.00
2705730964 $0.10
2705730964 $0.00
2904327435 ($10.08)
2904330043 ($0.10)
2904330043 ($0.61)
2805085086 ($0.10)
2705730962 $0.10
2705730962 $0.00
2805013406 $0.10
2805013406 $0.00
2904313230 $0.10

and this is what i want


2805013406
2705730964
2705730962
2805013406
2904313230

again look in sheet2 row B for an amount that's more then 0.01 and when you find it bring me back results from row A but don't duplicate

thanks

Ok, you have a duplicate in the expected results.

Try this...

In the formulas I use the following named ranges:

• Range1
• Refers to: =Sheet1!$A$2:$A$18
• Range2
• Refers to: =Sheet1!$B$2:$B$18

Book1
	A	B	C	D
1	Range1	Range2	_	Uniques
2	2705730962	$0.00	_	2805013406
3	2904352269	($9.95)	_	2705730964
4	2904352269	($0.10)	_	2705730962
5	2805062125	($0.10)	_	2904313230
6	2805013406	$0.10	_
7	2805013406	$0.00	_
8	2705730964	$0.10	_
9	2705730964	$0.00	_
10	2904327435	($10.08)	_
11	2904330043	($0.10)	_	_
12	2904330043	($0.61)	_	_
13	2805085086	($0.10)	_	_
14	2705730962	$0.10	_	_
15	2705730962	$0.00	_	_
16	2805013406	$0.10	_	_
17	2805013406	$0.00	_	_
18	2904313230	$0.10	_	_
Sheet1

Enter this array formula** in D2:

=INDEX(Range1,MATCH(TRUE,(Range2>0.01),0))

Enter this array formula** in D3 and copy down until you get blanks:

=IF(SUM((Range2>0.01)*(COUNTIF(D$2:D2,Range1)=0)),INDEX(Range1,MATCH(1,(Range2>0.01)*(COUNTIF(D$2:D2,Range1)=0),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of Range1.

 

[Joefried]

for some reason i cant get the first part to work which is the following info that i entered

=INDEX(C9:C1000000,MATCH(TRUE,E9:E1000000>0.01,0))
and the answer is Value

 

[T. Valko]

for some reason i cant get the first part to work which is the following info that i entered

=INDEX(C9:C1000000,MATCH(TRUE,E9:E1000000>0.01,0))
and the answer is Value

If you have a million rows of data this will be slow as heck to calculate!

Maybe you should be doing this with a real database?

Did you enter the formula as an array?

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

 

[FKoenig]

Pivot table?

 

[Joefried]

thank you
you were right and it didn't work because i forgot to do the ALT CTRL ENTER

however its not giving me a Unique answer it will reaped it and its basically coping from C:C every line

 

[T. Valko]

thank you
you were right and it didn't work because i forgot to do the ALT CTRL ENTER

however its not giving me a Unique answer it will reaped it and its basically coping from C:C every line

Here's a small sample file that demonstrates this.

zzzJoefried.xls 16kb

http://cjoint.com/?AHtwBiSxDD3

 

[snoopyhr]

had to remove , was not working well , sorry