IF statement help

S

spillott

I have 2 differnent lists of names each with their corresponding sales.
What I want to do is output a new list that has paired the people in the
different lists that have similar sales (+/-500):confused:
 
M

Max

spillott said:
I have 2 different lists of names each with their corresponding sales.
What I want to do is output a new list that has paired the people in the
different lists that have similar sales (+/-500):confused:
Click to expand...

Perhaps something along these lines ..

Assuming we want the list of names with sales: 1000 +/-500,
ie sales between 500 to 1500 ..

Copy & paste the 2 lists sequentially one below the other in a new sheet,
then use autofilter > custom* on the sales col header

*Click Data > Filter > Autofilter > Custom,
then make the settings in the Custom Autofilter dialog:
Show rows where:
is greater than or equal to: 500
And
is less than or equal to: 1500
Click OK

The desired list will be filtered out
Just copy & paste it elsewhere as may be required

Adapt the custom autofilter settings to suit ..
 
S

spillott

I don't have a central value (1000) that I want to be plus or minus from
which is the issue I cannot get around. The data ranges in both lists
from 0 - over 20 million so I would need an infinite number of central
values with limits of +/- 500. What I am trying to do is have all the
data in 1 list compare to all the data in the other list and output the
pairs that are similar wheather they are both at $5 or $5 000 000.

Any help would be greatly appreciated,

Thanks:(
 
M

Max

spillott said:
I don't have a central value (1000) that I want to be plus or minus from
which is the issue I cannot get around. The data ranges in both lists
from 0 - over 20 million so I would need an infinite number of central
values with limits of +/- 500. What I am trying to do is have all the
data in 1 list compare to all the data in the other list and output the
pairs that are similar wheather they are both at $5 or $5 000 000.
Click to expand...

Not very sure (hang around for better insights from others) ..

Assuming the 2 lists are within say, A1:B10 in sheets named as: X, Y
A1:A10 = names, B1:B10 = numbers
Numbers are assumed unique numbers within B1:B10 in either X or Y

Try this in a new sheet: Z (say)

In A1:
=IF(B1="","",X!A1&"-"&INDEX(Y!A:A,MATCH(X!B1,Y!B:B,0)))

In B1:
=IF(ISNUMBER(MATCH(X!B1,Y!B:B,0)),X!B1,"")

Select A1:B1, copy down to B10

Col A returns the concat string of the pairs of names with matching numbers
(X - Y strings), while col B returns the corresponding matching numbers
 
Top