|
Filtering and Hilighting Data
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 |
Filtering and Hilighting Data
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 |
Filtering and Hilighting Data
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 |
Filtering and Hilighting Data
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. |
Filtering and Hilighting Data
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? |
Filtering and Hilighting Data
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? |
Filtering and Hilighting Data
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. |
Filtering and Hilighting Data
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? |
Filtering and Hilighting Data
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. |
Filtering and Hilighting Data
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. |
Filtering and Hilighting Data
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. |
| All times are GMT +1. The time now is 04:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com