K_style

I have question about creating Macro with comparing 2 sheets...
let me know if you are an expert of Excel..

Thanks !

0wn3r

The only thing I don't know how to do in Excel is macros..which I'm sure aren't hard it's just that I've never tried. I can do almost everything I need to with formulas.

__________________
97 240sx se

K_style

Then let me ask you this..

I have sheet1 and sheet2, contains pretty much same everything but few cells with different values. I want to compare those two sheets and highlight the differences. no calculation just simple comparison.

I wanted to do Conditional Formatting but it won't allow to do in with different worksheet.

I googled it and came up with VBA code to make that happen but I want to know if there is other way.

blueshark123

I knew a girl that they asked her if she knew how to create macro in excel on a interview she went on is lol she didnt end up with the job to say the least

K_style

haha... LOL

I am in same position.. it is a test for new job.. I know how to create Macro but this is a very complicate situation I am in..
If I had MS excel 2010, I could compare two difference worksheet with Conditional formatting + making new rules" but this ancient excel 2003 won't let me do it, either 2007.
Thanks !

blueshark123

tell them to upgrade that shit lol

K_style

Tell... who ? my future employer ??? LOL

K_style

Arrrghh !!!! Fu*k excel 2003 and 2007 !!!

axiomatik

Why can't you create a section of worksheet 1 that is simply equal to worksheet 2, and then use conditional formatting between the two sections?

__________________

K_style

Because it is a test that I have to do without copying sheet2 to sheet1's table..

so sad. isn't it?

blueshark123

Ill ask my brother if he can help since he went to school for computer science and knows excel well

0wn3r

They should give me the job because I can do it.

__________________
97 240sx se

K_style

Ok I will ask them...

could you explain how you would do it?

K_style

I pretty much wrote VBA code to do it but, I am still trying to figure out how I could do it using Excel feature

0wn3r

Fine, but if you get the job just send half of your first year's salary into my paypal. kthx.

Excel is an exceptionally amazing tool that also blows. This is one of the rare times in Excel that you need to setup a table for your data (know how to do that?). Now, without having a damn clue what data you're comparing between the two sheets I'll just make an example...

Say you have this in Sheet 1:

A1: A, B1: 1
A2: B, B2: 2
A3: C, B3: 3

Now in Sheet 2 you have:

A1: A, B1: 1
A2: C, B2: 257
A3: B, B3: 2

From what I've gathered, you want to say in one of those sheets that "Hey, the data in Sheet 1 shows that the value "C" in column A has text/value of "3" in column B. However in Sheet 2 "C" actually has a text/value of "257", then highlight the cell that's different.

I guess it doesn't matter wtf you want to highlight with the conditional formatting, but let's say you want to highlight the value/text of "3" in Sheet 1 to say that it doesn't align with the value of "257" in the other.

You're going to want to set up a table with the data. So on Sheet 2 highlight everything from A1 through B3 and give it a name (to the left of the function button)...let's say you call it "KStylesData" If you expect the sheet to grow in size of the records or data, then I'd play it safe and pick something much further down than B3 (like B100 or something).

After you've defined a range with a name/table, then go back to Sheet 1 and go into your Format > Conditional Formatting option.

In the drop down, select "Formula Is...". In the field to the right of that, you're going to input a formula. This is the dumb confusing part here that's causing you an issue because you can't use a reference to other sheets than the one you're already in, so that's why you create a table. It will say:

=NOT(B1=(vlookup(A1,KStylesData,2,false)))=TRUE

So in case you need it, what the formula is saying is "If the text/value in cell B1 is NOT equal to the text/value of what you're going to lookup in the KystlesData table which happens to be in Sheet2, get me the 2nd column's text/value and put in a conditional format if they are not the same. If they WERE the same, it would return a FALSE and you can make another conditonal format for that if you wanted. Let's say you pick a red font for something that doesn't equal and you had put that conditional formatting like I said in Sheet 1 column B. You should see is something like this:

Sheet 1:

A1: A, B1: 1
A2: B, B2: 2
A3: C, B3: 3

Sheet 2:

A1: A, B1: 1
A2: C, B2: 257
A3: B, B3: 2

...because A = 1 on both sheets, B = 2 on both sheets, but C = 3 on Sheet1 while C = 257 on Sheet2. So your conditional formatting in Sheet1 column B highlights that cell in red.

Skippy dude yo. That make sense?

p.s. On a normal vlookup I usually recommend avoiding using a table like that. This is an exception. You'll typically want to use entire columns. So rather than vlookup(A1,KyStylesData,2,false) you'd put in vlookup(A1,Sheet2!A:B,2,false). Last I checked Excel's help section says to use a table name which is a load of crap. Using entire columns allows you to add or remove data in column A and B as you see fit without having to modify the range.

Also, I don't think that conditional formatting has a check on your formula to say whether or not you typed it in wrong like it normally would. So if you hit ok and it doesn't work like you thought it should, DOUBLE CHECK what you typed in because I'm pretty sure it will allow almost anything...except for referencing other sheets of course :P You could probably get away with using a COUNTIF if you wanted to instead, but I usually prefer the functionality of vlookups if I have a choice.

__________________
97 240sx se

LimeLite Racing

Wtf? You're getting ANOTHER job?! Lol you da man.

__________________
Drift.

K_style

Upgrading within company... LOL



I am working on VBA.... I will take a look at the Vlookup way you told me..

LimeLite Racing

Oh nice!!! How are you liking it over there?

__________________
Drift.

K_style

BTW.... how come you never call me...

I am so~ thirsty with beer these days... dont you miss "Ruby~nator"??

LimeLite Racing

Hahaha sorry man! Just busy at work I swear we're gonna go grab beers in the very near future. Like tomorrow afternoon maybe? Yes I definitely miss the rubinator!!!!!!!!!! Hahaha

__________________
Drift.

LimeLite Racing

If you're off tomorrow I'd be down to max it to downtown Mcminnies if you want.

__________________
Drift.

K_style

all the way to Downtown?? Imbrie hall one is nice too

0wn3r

What I did there takes about 5 seconds to do in Excel...but ok. ...keep trying to go the VBA route I guess. Macro completely unecessary.

And you're welcome.

__________________
97 240sx se

K_style

Oh yes, of course...
Thank you and thank you for your effort and help.
I have to create Macro either write VBA code or record Macro with using formula. that was a task..