| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to match up variables to a common list and confirm
Finding a word from list in column B6-b130 to match word in A6. If there is a
match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Hi, Try this,
Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Thanks! Mike,
I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
On 26 Jul, 18:46, DebKnight56
wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! Debbie, I assume you have 2 lists of data one in col A and 1 in col B and you want to check to see if the data in A appears also in B. A quick and not very pretty way is in col C put this formula in. =IF(ISNA(VLOOKUP(A6,$B$6:$B$130,1,FALSE))=TRUE,"", IF(VLOOKUP(A6,$B$6:$B $130,1,FALSE)=A6,"x","")) There are other ways Matthew |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
I have a similiar problem.
I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Thank You Matthew!,
Yes this works! ( I should have asked is there a way to erase or highlight what was confirmed so I could see what was left over, but for now this is awesome!) Cheryl - I think this will work for yours as well! Thanks for your help, Matthew and Mike, also! Debbie "Matthew" wrote: On 26 Jul, 18:46, DebKnight56 wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! Debbie, I assume you have 2 lists of data one in col A and 1 in col B and you want to check to see if the data in A appears also in B. A quick and not very pretty way is in col C put this formula in. =IF(ISNA(VLOOKUP(A6,$B$6:$B$130,1,FALSE))=TRUE,"", IF(VLOOKUP(A6,$B$6:$B $130,1,FALSE)=A6,"x","")) There are other ways Matthew |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Cheryl, I think Matthew won this one - and you should only need a minor change!
Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
=IF(COUNTIF($B$6:$B$130,A6),"x","")
"DebKnight56" wrote: Cheryl, I think Matthew won this one - and you should only need a minor change! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
I'm still playing with this but can't seem to get it to work. I am doing
something wrong, I just can't figure out what. "Toppers" wrote: =IF(COUNTIF($B$6:$B$130,A6),"x","") "DebKnight56" wrote: Cheryl, I think Matthew won this one - and you should only need a minor change! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Topper,
Hey - this one works as well, and is a whole different way! This is great! Thank you ! Debbie "Toppers" wrote: =IF(COUNTIF($B$6:$B$130,A6),"x","") "DebKnight56" wrote: Cheryl, I think Matthew won this one - and you should only need a minor change! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Try in B2:
=ISNA(VLOOKUP(A2,Sheet2!$A:$B,2,0)),"",VLOOKUP(A2, Sheet2!A:B,2,0)) Copy down. HTH "cheryl" wrote: I'm still playing with this but can't seem to get it to work. I am doing something wrong, I just can't figure out what. "Toppers" wrote: =IF(COUNTIF($B$6:$B$130,A6),"x","") "DebKnight56" wrote: Cheryl, I think Matthew won this one - and you should only need a minor change! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Cheryl,
=IF(COUNTIF(sheetname!AK$6:sheetname!AK$150,$A7)," x","") if you replace the sheetname with your sheet names, it will confirm with an "x" that the info is there - but I do not know how to actually transfer that data to your original cell - but someone else in the thread may know - these folks are great! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to match up variables to a common list and confirm
Debbie:
The following formula is what I was looking for. Love it- works perfectly. =IF(ISNA(VLOOKUP(A2,Sheet2!$A:$B,2,0)),"",VLOOKUP( A2,Sheet2!A:B,2,0)) I have a sheet that has Name and Address and another sheet that has Name and ID. I needed to add the addresses from sheet one to the ID and Name on sheet two. Yet, sheet two does not contain the entire list found on sheet one. The formula above works perfectly. Toppers gets my vote! "DebKnight56" wrote: Cheryl, =IF(COUNTIF(sheetname!AK$6:sheetname!AK$150,$A7)," x","") if you replace the sheetname with your sheet names, it will confirm with an "x" that the info is there - but I do not know how to actually transfer that data to your original cell - but someone else in the thread may know - these folks are great! Debbie "cheryl" wrote: I have a similiar problem. I want to compare a single cell in sheet 1,column A with all cells in sheet 2, column A. If a match is found, place the information found in column B of the match in column B of sheet 1. If you get your answer, it may work for me as well (with modification). Please let me know. Thanks "DebKnight56" wrote: Thanks! Mike, I appreciate your getting me closer to my goal! Now if I find A6 contained in my list of variables, in B6-B130, and put the answer "x" in AB6, then, I want to try to find A7 from that same list, B6-B130, and if it IS there, put the x in AB7 or leave it blank if it is not. Then so on...But I do not understand the part of the formula that will put the answer in the column I want - It is still going into the row of the column B that I find it on and not the row and column that I want it to go in. Please forgive the beginner semantics! Thanks, Debbie "Mike H" wrote: Hi, Try this, Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: =IF(B6=A$6,"x","") Put this formula in AB6 and drag down to AB130 You other questions can be solved with this formula with the ranges modified. Mike "DebKnight56" wrote: Finding a word from list in column B6-b130 to match word in A6. If there is a match then place "x" in column AB6 - No match leave AB6 Blank. then right on down the line: Finding a word from list in column B6-b130 to match word in A7. If there is a match then place "x" in column AB7 - No match leave AB7Blank. Finding a word from list in column B6-b130 to match word in A8. If there is a match then place "x" in column AB8 - No match leave AB8 Blank, And so on. Help new on job and short time! Thx anyone! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX - MATCH with three variables | Excel Worksheet Functions | |||
| is it possible to find common data between to colums and match | Excel Worksheet Functions | |||
| is it possible to find common data between to colums and match dat | Excel Worksheet Functions | |||
| is it possible to find common data between to colums and match dat | Excel Worksheet Functions | |||
| How do I match up two tables of data with one column in common | Excel Worksheet Functions | |||
Linear Mode
