I have a list of names on sheet 1 col B4:B100 and another list of names on sheet2 on col E7:E100, is there is a formula that can luck if the name on sheet 2 col E7:E100 is on sheet 1 col B4:B100 and if the name from sheet 2 col E is not on sheet 1 col B to add the name from sheet 2 col E in to sheet 1 col B, thanks in advance for any help.
Here's a simple procedure that you can use
In Sheet2,
Put in F7: =IF(E7="","",IF(COUNTIF(Sheet1!$B$4:$B$100,E7),"","x"))
Copy down to F100 to flag the cases as "x"
Apply Filter on F6:F100, choose: "x", then copy the filtered cells n paste special as values into Sheet1 col B
Clean up by deleting F7:F100. Done.
--------------------------------
Success? Wave it, whack the little star at the bottom left of my response
Hi Max, Singapore, this formula work perfect to choose the names from sheet 2 col E that are not in sheet 1 col B, what hapens is that i continiously add names on sheet 2 col E, what i looking for is that, when i add a name in sheet 2 col E and that name is not already in sheet 1 col B, then automaticaly add that name on sheet 1 col B, thanks in advance.
If no other responder drops by here, suggest that you try posting in Programming group for a vba solution
Ok Max, Singapore, thanks for your supportOriginally Posted by Max, Singapore
If you just want the names to match then why not just use Sheet 1 as your input and never manually add names to Sheet 2? Sheet 2 would then just reference the list in Sheet 1. You could protect Sheet 2 so that people could not change the list on that sheet.
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
HI Pauleyb,thanks for your help, but i have to manually enter names on sheet 2 and the names that i enter in sheet 2 to automatically enter on sheet 1 if there is no match in sheet 1
Okay, how about this:
=INDEX(Sheet2!$A:$A,MIN(IF(COUNTIF(A$1:A1,Sheet2!$A$1:$A$40)=0,ROW(Sheet2!$A$1:$A$40),ROWS(Sheet2!$A$1:$A$40)+ROW(Sheet2!$A$1:$A$40))))
This should be input in cell Sheet1:A2, and then, since it is an array formula, use ctrl-shift-enter (not just enter) to enter it in the cell. Then drag this formula down as far as you need it. This assumes:
Your list in Sheet 1 starts at cell A2 (note that you do need an empty cell just above it, that is why I did not start at A1).
Your input list in Sheet 2 starts at A1 and extends down to A40 (adjust as necessary)
Note that once it gets to the end, it will return a 0. If you prefer a blank instead of a 0, you would have to use an IF statement to see if the result is 0 or not and then choose the desired output. I left this out since it would make the equation longer and you may miss the intent of the equation.
Hi Pauleyb, i really appreciate your effort, i try this formula but returns in sheet 1 col A the first name that is on sheet 2 col A1 as i copy down on sheet 1 col A2:A, to be specific, my list on sheet 1 starts on col B4 and right now i have names from B4:B60, then on sheet 2 i manually enter names on col E starting on E7:E63, the next name i will type will be in sheet 2 E64, what i looking for is when i type (enter) a name on sheet 2 col E, is to look if there is no match on sheet 1 B4:B and if there is no match to enter the name on sheet 1 col B, hope this make sense, thanks in advance for any help.
That equation should do it. Read my assumptions on where the lists are. You just need to adjust the cells to match your inputs. Here is a sample spreadsheet to show proof of concept.
Hi Pauleyb, i adjusted to my needs and does work perfect, thanks very much for your time and effort to help me with this, really appreciated
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks