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?