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 datais
sorted by column A, paste it into C1 and fill down:
=IF(B1="Steve",IF(COUNTIF($A$1:$A$13,A1)=COUNTIF(OFFSET(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 butis not the only entry
--
Anne Murray
:
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
:
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 accountin
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 -
