Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A -- B -- C
1 -- a --
1 -- a --
1 -- b -- X
1 -- a --
2 -- c --
2 -- b -- X
2 -- c --
3 -- e --
3 -- e --
3 -- b -- X
3 -- e --
3 -- e --


So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. If a "mistake" value is in the first row of a given account number, this
will not work
2. If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change?
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray


"NPell" wrote:

Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A -- B -- C
1 -- a --
1 -- a --
1 -- b -- X
1 -- a --
2 -- c --
2 -- b -- X
2 -- c --
3 -- e --
3 -- e --
3 -- b -- X
3 -- e --
3 -- e --


So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks

On Apr 2, 4:03*pm, FinRazel wrote:
Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray



"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 Tom
123 Steve X
123 Tom
123 Tom

456 Jim
456 Steve X
456 Jim
456 Steve X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 Steve
789 Steve
789 Steve
Then this would be ok, because Steve is the only value.


Hope this helps..
Im going to try that formula you suggested. Thankyou

On Apr 2, 4:10*pm, NPell wrote:
On Apr 2, 4:03*pm, FinRazel wrote:





Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.

On Apr 2, 4:26*pm, NPell wrote:
On Apr 2, 4:10*pm, NPell wrote:





On Apr 2, 4:03*pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?

Hey NPell, I would have replied earlier, but I didn't see your messages.
So, I set up your columns like this:
A B
123 Tom
123 Steve
123 Tom
123 Tom
456 Jim
456 Steve
456 Jim
456 Steve
789 Steve
789 Steve
789 Steve

This function should meet all of your criteria, but only if the data is
sorted by column A, paste it into C1 and fill down:

=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(O FFSET(OFFSET(B1,(-1*COUNTIF($A$1:A1,A1))+1,0),0,0,COUNTIF($A$1:$A$13 ,A1),1),"Steve"),"","X"),"")

To break this unrully function down:

C1=COUNTIF($A$1:A1,A1)
counts repeats in column A (array grows with fill down)

D1=COUNTIF($A$1:$A$13,A2)
counts duplicates in column A (constant array)

E1=COUNTIF(OFFSET(OFFSET(B1,(-1*C1)+1,0),0,0,D1,1),"Steve")
counts "Steve"s in array whose placement depends on C1 and D1

F1=IF(B1="Steve",IF(D1=E1,"","X"),"")
marks cell if "Steve" appears but is not the only entry

--
Anne Murray


"NPell" wrote:

On Apr 2, 4:26 pm, NPell wrote:
On Apr 2, 4:10 pm, NPell wrote:





On Apr 2, 4:03 pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. If a "mistake" value is in the first row of a given account number, this
will not work
2. If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change?
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A -- B -- C
1 -- a --
1 -- a --
1 -- b -- X
1 -- a --
2 -- c --
2 -- b -- X
2 -- c --
3 -- e --
3 -- e --
3 -- b -- X
3 -- e --
3 -- e --

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 Tom
123 Steve X
123 Tom
123 Tom

456 Jim
456 Steve X
456 Jim
456 Steve X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 Steve
789 Steve
789 Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?

On Apr 3, 9:10*pm, FinRazel wrote:
Hey NPell, I would have replied earlier, but I didn't see your messages.
So, I set up your columns like this:
A * * * * * * *B
123 * * Tom
123 * * Steve
123 * * Tom
123 * * Tom * *
456 * * Jim
456 * * Steve
456 * * Jim
456 * * Steve *
789 * * Steve
789 * * Steve
789 * * Steve

This function should meet all of your criteria, but only if the data is
sorted by column A, paste it into C1 and fill down:

=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(O FFSET(OFFSET(B1,(-1*COUNT*IF($A$1:A1,A1))+1,0),0,0,COUNTIF($A$1:$A$1 3,A1),1),"Steve"),"","X"),"")

To break this unrully function down:

C1=COUNTIF($A$1:A1,A1)
* * * * * * * * * counts repeats in column A (array grows with fill down)

D1=COUNTIF($A$1:$A$13,A2)
* * * * * * * * *counts duplicates in column A (constant array)

E1=COUNTIF(OFFSET(OFFSET(B1,(-1*C1)+1,0),0,0,D1,1),"Steve")
* * * * * * * * *counts "Steve"s in array whose placement depends on C1 and D1

F1=IF(B1="Steve",IF(D1=E1,"","X"),"")
* * * * * * * * *marks cell if "Steve" appears but is not the only entry

--
Anne Murray



"NPell" wrote:
On Apr 2, 4:26 pm, NPell wrote:
On Apr 2, 4:10 pm, NPell wrote:

On Apr 2, 4:03 pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?- Hide quoted text -

- Show quoted text -

This works amazingly, thankyou. I had formulas stretching over 4
Columns feeding off each other - and it didnt work properly, lol - but
this is amazing...

Although - im gonna test you here - can you tweak it or add something
that says if they are all "Steve" under the same account number, then
it doesnt show the X?

Thanks alot for what you have done already, its fantastic.

On Apr 4, 11:52*am, NPell wrote:
On Apr 3, 9:10*pm, FinRazel wrote:





Hey NPell, I would have replied earlier, but I didn't see your messages.
So, I set up your columns like this:
A * * * * * * *B
123 * * Tom
123 * * Steve
123 * * Tom
123 * * Tom * *
456 * * Jim
456 * * Steve
456 * * Jim
456 * * Steve *
789 * * Steve
789 * * Steve
789 * * Steve

This function should meet all of your criteria, but only if the data is
sorted by column A, paste it into C1 and fill down:

=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(O FFSET(OFFSET(B1,(-1*COUNT**IF($A$1:A1,A1))+1,0),0,0,COUNTIF($A$1:$A$ 13,A1),1),"Steve"),"","X"),"")

To break this unrully function down:

C1=COUNTIF($A$1:A1,A1)
* * * * * * * * * counts repeats in column A (array grows with fill down)

D1=COUNTIF($A$1:$A$13,A2)
* * * * * * * * *counts duplicates in column A (constant array)

E1=COUNTIF(OFFSET(OFFSET(B1,(-1*C1)+1,0),0,0,D1,1),"Steve")
* * * * * * * * *counts "Steve"s in array whose placement depends on C1 and D1

F1=IF(B1="Steve",IF(D1=E1,"","X"),"")
* * * * * * * * *marks cell if "Steve" appears but is not the only entry

--
Anne Murray

"NPell" wrote:
On Apr 2, 4:26 pm, NPell wrote:
On Apr 2, 4:10 pm, NPell wrote:

On Apr 2, 4:03 pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C..

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?- Hide quoted text -

- Show quoted text -

This works amazingly, thankyou. I had formulas stretching over 4
Columns feeding off each other - and it didnt work properly, lol - but
this is amazing...

Although - im gonna test you here - can you tweak it or add something
that says if they are all "Steve" under the same account number, then
it doesnt show the X?

Thanks alot for what you have done already, its fantastic.- Hide quoted text -

- Show quoted text -

Im sorry, i take that back - it does that already. I just didnt tweak
the formula correctly for the right cell references. My fault.
Can I ask for another tweak though?? For it to search for anything
containing "Steve" .. ie. "Steve Smith" or "Steven"... I tried putting
* either side of it, but it didnt work. Is this possible?

On Apr 4, 12:11*pm, NPell wrote:
On Apr 4, 11:52*am, NPell wrote:





On Apr 3, 9:10*pm, FinRazel wrote:

Hey NPell, I would have replied earlier, but I didn't see your messages.
So, I set up your columns like this:
A * * * * * * *B
123 * * Tom
123 * * Steve
123 * * Tom
123 * * Tom * *
456 * * Jim
456 * * Steve
456 * * Jim
456 * * Steve *
789 * * Steve
789 * * Steve
789 * * Steve

This function should meet all of your criteria, but only if the data is
sorted by column A, paste it into C1 and fill down:

=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(O FFSET(OFFSET(B1,(-1*COUNT***IF($A$1:A1,A1))+1,0),0,0,COUNTIF($A$1:$A $13,A1),1),"Steve"),"","X"),"")

To break this unrully function down:

C1=COUNTIF($A$1:A1,A1)
* * * * * * * * * counts repeats in column A (array grows with fill down)

D1=COUNTIF($A$1:$A$13,A2)
* * * * * * * * *counts duplicates in column A (constant array)

E1=COUNTIF(OFFSET(OFFSET(B1,(-1*C1)+1,0),0,0,D1,1),"Steve")
* * * * * * * * *counts "Steve"s in array whose placement depends on C1 and D1

F1=IF(B1="Steve",IF(D1=E1,"","X"),"")
* * * * * * * * *marks cell if "Steve" appears but is not the only entry

--
Anne Murray

"NPell" wrote:
On Apr 2, 4:26 pm, NPell wrote:
On Apr 2, 4:10 pm, NPell wrote:

On Apr 2, 4:03 pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?- Hide quoted text -

- Show quoted text -

This works amazingly, thankyou. I had formulas stretching over 4
Columns feeding off each other - and it didnt work properly, lol - but
this is amazing...

Although - im gonna test you here - can you tweak it or add something
that says if they are all "Steve" under the same account number, then
it doesnt show the X?

Thanks alot for what you have done already, its fantastic.- Hide quoted text -

- Show quoted text -

Im sorry, i take that back - it does that already. *I just didnt tweak
the formula correctly for the right cell references. My fault.
Can I ask for another tweak though?? For it to search for anything
containing "Steve" .. ie. "Steve Smith" or "Steven"... I tried putting
* either side of it, but it didnt work. Is this possible?- Hide quoted text -

- Show quoted text -

I did it.. Just put a COUNTIF in there instead of the (B1="Steve) ..

If I could ask maybe that this formula could be modified - or a new
one produced - to search for if the Account number has different names
instead of one consistant name?? This is probably an easier thing than
I have asked for before. But Im not sure i could do it personally. If
I can then i will re-post... again, lol.

On Apr 4, 12:27*pm, NPell wrote:
On Apr 4, 12:11*pm, NPell wrote:





On Apr 4, 11:52*am, NPell wrote:

On Apr 3, 9:10*pm, FinRazel wrote:

Hey NPell, I would have replied earlier, but I didn't see your messages.
So, I set up your columns like this:
A * * * * * * *B
123 * * Tom
123 * * Steve
123 * * Tom
123 * * Tom * *
456 * * Jim
456 * * Steve
456 * * Jim
456 * * Steve *
789 * * Steve
789 * * Steve
789 * * Steve

This function should meet all of your criteria, but only if the data is
sorted by column A, paste it into C1 and fill down:

=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(O FFSET(OFFSET(B1,(-1*COUNT****IF($A$1:A1,A1))+1,0),0,0,COUNTIF($A$1:$ A$13,A1),1),"Steve"),"","X"),"")

To break this unrully function down:

C1=COUNTIF($A$1:A1,A1)
* * * * * * * * * counts repeats in column A (array grows with fill down)

D1=COUNTIF($A$1:$A$13,A2)
* * * * * * * * *counts duplicates in column A (constant array)

E1=COUNTIF(OFFSET(OFFSET(B1,(-1*C1)+1,0),0,0,D1,1),"Steve")
* * * * * * * * *counts "Steve"s in array whose placement depends on C1 and D1

F1=IF(B1="Steve",IF(D1=E1,"","X"),"")
* * * * * * * * *marks cell if "Steve" appears but is not the only entry

--
Anne Murray

"NPell" wrote:
On Apr 2, 4:26 pm, NPell wrote:
On Apr 2, 4:10 pm, NPell wrote:

On Apr 2, 4:03 pm, FinRazel wrote:

Using the data you have listed, paste this function into C3.

=IF(AND(A3=A2,B3<B2),IF(B3<B1,"X","-"),"-")

Fill down to obtain your expected results.

However, there are several problems with this function:
1. *If a "mistake" value is in the first row of a given account number, this
will not work
2. *If you have two "mistake" values in consecutive rows, this will not work.

Tell me more about the function - Is b always the same, or does it change? *
Do you need to know how many b's in 1, how many in 2, etc?
--
Anne Murray

"NPell" wrote:
Hello,

Im looking for a formula to filter data and see if it contains
something in another column.

IE:
A * -- * B * -- * C
1 * -- * a * *--
1 * -- * a * *--
1 * -- * b * *-- *X
1 * -- * a * *--
2 * -- * c * *--
2 * -- * b * *-- *X
2 * -- * c * *--
3 * -- * e * *--
3 * -- * e * *--
3 * -- * b * * -- *X
3 * -- * e * *--
3 * -- * e * *--

So,
Account Number = "1" ... Name = "a" ... theres a "b" in there by
mistake (this is not a hidden value, i know what im looking for with
these "b"s) ... so an X is produced, or something, in column C.

It is also done for the "b" showing in a Account Number "2" and
Account Number "3"

Is there anything like VLOOKUP or COUNTIF, or something, maybe
combining the both - that could help?

Thanks- Hide quoted text -

- Show quoted text -

B is always the same, its almost like searching for something. I'll
try and be more specific.

123 * * * Tom
123 * * * Steve * *X
123 * * * Tom
123 * * * Tom

456 * * * Jim
456 * * * Steve * *X
456 * * * Jim
456 * * * Steve * *X

Steve is the wrong name here, because there are other names that
correspond with a number that Steve is on.

If it was;

789 * Steve
789 * Steve
789 * Steve
Then this would be ok, because Steve is the only value.

Hope this helps..
Im going to try that formula you suggested. Thankyou- Hide quoted text -

- Show quoted text -

Unfortunately the formula you posted didnt work, it did something but
it wasnt accurate - thankyou though.

Maybe if i write some steps on what i want it to do?

1. Search Column F for numbers that are the same (maybe a duplicates
formula?)
2. Within those duplicates rows - Search Column J for the word
"Steve". Then highlight either by conditional formatting, or an X in a
new column.

This would be a good starting point, but if possible this step could
be added..
3. If ALL duplicates = Steve, then this is not highlighted / marked.- Hide quoted text -

- Show quoted text -

Right.
Ive got Column N looking for if Column J contains 'Steve'...
[ =IF(COUNTIF(J2,"*Steve*"),"Yes","No") ]
Ive got Column M looking for if its a duplicate in Column F....
[ =IF(COUNTIF($F$2:$F$40,F2)1,"X","") ]
Ive got Column O checking if both N and M are TRUE....
[ =AND(N2="Yes",O2="X") ]

Whats a good way of merging these formulas to make one?
And also maybe use something like Anne used before to distinguish if
they are ALL Steve accounts, or there is just a lost Steve account in
amongst them all.

Thanks all if you can help?- Hide quoted text -

- Show quoted text -

This works amazingly, thankyou. I had formulas stretching over 4
Columns feeding off each other - and it didnt work properly, lol - but
this is amazing...

Although - im gonna test you here - can you tweak it or add something
that says if they are all "Steve" under the same account number, then
it doesnt show the X?

Thanks alot for what you have done already, its fantastic.- Hide quoted text -

- Show quoted text -

Im sorry, i take that back - it does that already. *I just didnt tweak
the formula correctly for the right cell references. My fault.
Can I ask for another tweak though?? For it to search for anything
containing "Steve" .. ie. "Steve Smith" or "Steven"... I tried putting
* either side of it, but it didnt work. Is this possible?- Hide quoted text -

- Show quoted text -

I did it.. Just put a COUNTIF in there instead of the (B1="Steve) ..

If I could ask maybe that this formula could be modified - or a new
one produced - to search for if the Account number has different names
instead of one consistant name?? This is probably an easier thing than
I have asked for before. But Im not sure i could do it personally. If
I can then i will re-post... again, lol.- Hide quoted text -

- Show quoted text -

Another tweak??
Can I get it to highlight all the rows using Conditional formatting or
something that have a Steve in amongst the accounts??
.. I must be really annoying now but if you can help, then great.

I had a longer post, but it may not have gone through.

Try this for the first question,

IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(OFFSET(OFFSET(B1 ,(-1*COUNTÂ*Â*Â*Â*IF($A$1:A1,A1))+1,0),0,0,COUNTIF($A $1:$A$13,A1),1),"Steve"),"","X")

and for the second question, Highlight the row including "Steve", and open
the conditional formatting window. Put ="Steve" in the conditions field, and
choose your formatting.


--
Anne Murray


"NPell" wrote:

If I could ask maybe that this formula could be modified - or a new
one produced - to search for if the Account number has different names
instead of one consistant name?? This is probably an easier thing than
I have asked for before. But Im not sure i could do it personally. If
I can then i will re-post... again, lol.- Hide quoted text -

- Show quoted text -

Another tweak??
Can I get it to highlight all the rows using Conditional formatting or
something that have a Steve in amongst the accounts??
.. I must be really annoying now but if you can help, then great.