|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi,
I have a complicated question for help. I hope that I make it sound ok.
First thing to remember is that this sheet is linked to another, which
is in-turn link to another...ive been filtering data!
so what i have is:
[Quoted Text] >From B3:B200 a list of brands
>From column F2 to to Z2 I have a list of shop names and then their respective sales in F3:Z200
What I want is for it to idenitfy where there are only sales in one
store (so where there are it will say one)...that is easy I can do
that! but the next bit is where I am stuck.
The brands can be the same, so there could be more than 1 brand
matching in column B (B3:B200), and there could be sales in different
shops, e.g. B52 could be the same as B114, but they could have sales
values in different shop cells, eg. H52 and X114. Thus, I would want
this to tell me that this is false. However, if it were to say that B52
and B114 (which are the same) are selling in H52 and H114 respectively,
then it would return a 1 value.
Thus, what I should have is, in say column AX (AX3:AX200) either a 1 or
a False value.
I am stuck because there could be one brand, there could be two, or
three,
or even up 10 of the same brand, but they might be selling in different
shops, i want to identify which are only selling in one.
If there is anyone out there, that understood that! lol, please please
I would really appreciate your help!
Greg.
|
|
What is wrong with the suggestion that I gave yesterday?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"GregA" <g_r_a_a[ at ]hotmail.com> wrote in message
news:1157801654.229291.13610[ at ]p79g2000cwp.googlegroups.com...
[Quoted Text] > Hi,
>
> I have a complicated question for help. I hope that I make it sound ok.
>
>
>
> First thing to remember is that this sheet is linked to another, which
> is in-turn link to another...ive been filtering data!
>
>
> so what i have is:
>
>
>
> >From B3:B200 a list of brands
> >From column F2 to to Z2 I have a list of shop names and then their
respective sales in F3:Z200
>
> What I want is for it to idenitfy where there are only sales in one
> store (so where there are it will say one)...that is easy I can do
> that! but the next bit is where I am stuck.
>
>
> The brands can be the same, so there could be more than 1 brand
> matching in column B (B3:B200), and there could be sales in different
> shops, e.g. B52 could be the same as B114, but they could have sales
> values in different shop cells, eg. H52 and X114. Thus, I would want
> this to tell me that this is false. However, if it were to say that B52
> and B114 (which are the same) are selling in H52 and H114 respectively,
> then it would return a 1 value.
>
>
> Thus, what I should have is, in say column AX (AX3:AX200) either a 1 or
>
> a False value.
>
>
> I am stuck because there could be one brand, there could be two, or
> three,
> or even up 10 of the same brand, but they might be selling in different
>
> shops, i want to identify which are only selling in one.
>
>
> If there is anyone out there, that understood that! lol, please please
> I would really appreciate your help!
>
>
> Greg.
>
|
|
I think the sumproduct doesn't reply the answer that ?I would like
E.g. the function you gave me was:
=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUMN(F$3:Z$3)))=1,1)
However, if I have three of the same brands in column b, and they are
all selling in shop a in column f, it should return a 1 value, but with
this formula it returns a false value. Thus, this formula doesn't
appear to be taking account of multiple items in column b3:b200.
If you have any other suggestions, i would like to hear from you. I am
currently working on these two options, but they are only returning
FALSE values for every cell once activated with the ctrl+shift+enter
process:
=IF(COUNTDIFF(IF((B$3:B$200=B3)*(F$3:Z$200)>0,F$2:Z$2,""),,"")=1,1)
=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUMN(F$3:Z$3)))=1,1)
Many Thanks, Greg.
|
|
Not me pal, the answer I gave was
=IF(SUMPRODUCT(($B$3:$B$200=$B3)*($F$3:$Z$200))=1,"Only one","More than
one")
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"GregA" <g_r_a_a[ at ]hotmail.com> wrote in message
news:1157808550.950861.65020[ at ]m73g2000cwd.googlegroups.com...
[Quoted Text] > I think the sumproduct doesn't reply the answer that ?I would like
>
> E.g. the function you gave me was:
>
>
=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUM
N(F$3:Z$3)))=1,1)
>
> However, if I have three of the same brands in column b, and they are
> all selling in shop a in column f, it should return a 1 value, but with
> this formula it returns a false value. Thus, this formula doesn't
> appear to be taking account of multiple items in column b3:b200.
>
>
> If you have any other suggestions, i would like to hear from you. I am
> currently working on these two options, but they are only returning
> FALSE values for every cell once activated with the ctrl+shift+enter
> process:
>
> =IF(COUNTDIFF(IF((B$3:B$200=B3)*(F$3:Z$200)>0,F$2:Z$2,""),,"")=1,1)
>
>
=IF(COUNT(1/FREQUENCY(IF((B$3:B$200=B3)*(F$3:Z$200)>0,COLUMN(F$3:Z$3)),COLUM
N(F$3:Z$3)))=1,1)
>
>
>
> Many Thanks, Greg.
>
|
|
|