2 columns 1 is wrong

[barmace]

This for my Brother not for me. He just expects me to know the answer (SEE THE IMPOSSIBLE what brother really wants)

I have a spreadsheet I export a second column. This is not the problem.. here is the problem

column 1 is master column it has all the names I have
example (400 names)

Column 2 is imported names.
example (320 names)

ok here is the problem
I want colum 3 to show what is missing in column 2 compaired to column 1
example (80 people missing)

I've been told you can do it with Conditional Formatting. I have no idea how to do it

ok here comes the extra credit (THE IMPOSSIBLE)

I want column 2 to actually show the names that are missing by moving the cell down putting in the name and highlighting it.

 

[Todd Bardoni]

I would just enter a vlookup in another column and then in the next column a conditional function like the following:
=if(c1=b1,"b1,"")

 

[Al Chara]

Did you try a search on this board? This question has been asked and answered a ton of times.

With that said, take a look at the following page. What you want is all the way at the bottom of the page.
http://www.cpearson.com/excel/duplicat.htm

 

[parry]

Hi Barmace, tbardoni is right that a vlookup can be done to find whether the name in column 2 exists in column 1.

If you want to highlight the name in column A that doesn’t appear in Column B do the following :-

1) select cell c1 and enter this formula
=IF(ISERROR(VLOOKUP(A1,B:B,1,FALSE))=TRUE,"",VLOOKUP(A1,B:B,1,FALSE))
2) select the cell again and double click the fill handle (little box in bottom right corner of cell) and this will copy the formula down
3) Select all of column A (you can just click on the letter A to select the whole column)
4) From the menu select Format-Conditional Formatting
5) Change the drop down box from "Cell Is" to "Formula Is" and enter this formula =IF(C1<>A1,1,0)
6) Now click the format button and choose the font colour or pattern you want to highlight names which are in Column A but not in Column B
7)Click Ok

Im not exactly sure what you mean by the impossible task J Let me know a bit more detail and I can give you some code that will do this. Do you mean you want column B to have the same names as Column A, but highlight the ones that were missing (ie name is in Column A but not in B) so both column A and B will now be the same number of rows?

Cheers
Parry


---Edit--
Oops posted my reply before seeing Al's. That link is very good Al and I would suggest Barmace you use that instead of my suggestion.

cheers
Parry
This message was edited by parry on 2002-08-30 21:48

 

[Joe Was]

=IF(B1=C1,"",IF(VLOOKUP(B1,$B$1:$C$4,2,FALSE)=B1,"",B1))

Where the key list is in column "B" and the partial list is in "C", both lists are contained in Range: $B$1:$C$4
Note: change to your range.
"2" is column "C" the second column in your range, as if the range you indicate is the whole sheet, "C" on a normal sheet is column "3" but in your two column range "B&C" "C" is the second column.
"False" to only search for an identical match, "True" would be to find and match the closest item.
If the value in column "B" is found in cloumn "C" put a blank in the column that contains the formula, if the value in column "B" is not found in column "C" then list the value from column "B" not found in column "C."

The First part of the formula above will filter to see if Row values "B&C" match if they do match skip the search. This step speeds the code up. Just copy the formula down any column starting in the row that list "B" starts in and adjust the search range to match your two column list. JSW

_________________<INPUT type="JSW" value=" Excel On... Coder's!" ID=Text1><spanstyle='font-size:36.0pt;color:#FFCC00'>JSW<o></o></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o></o></span>[/b]
This message was edited by Joe Was on 2002-08-30 22:00