2 columns 1 is wrong

barmace

New Member
Joined
Aug 29, 2002
Messages
1
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
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,"")
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
=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:p></o:p></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o:p></o:p></span>[/b]
This message was edited by Joe Was on 2002-08-30 22:00
 

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top