PDA

View Full Version : Sleeper: Compare parts of a spreadsheet



austenr
08-05-2005, 01:49 PM
Hi everyone,

Have not had time or reason to post much until now. I have a need to compare two sections of a spreadsheet and highlight the differences. I have attached a sample spreadsheet as a sample. The problem is that the key number may not always be on the same row. I need a way to search for the number that is in column I and match it to column B. If the number is not found in column B then everything in the adjoining cells from I to the right should be highlighted. If the number in Col I is found in Col B, then only highlight cells in column I thru M that do not match Columns B thru F. It will become more clear when you look at the sample spreadsheet. Thanks. :banghead:

Ken Puls
08-05-2005, 04:40 PM
Hey Austen,

Try the attached version of your file. Uses a combination of vlookups and conditional formatting. (No VBA required!)

The only trick with this is trying to make the format portable so that you can apply it to all ranges without changing anything. (Reason for the Column(B1) argument in the second format.)

You may also want to name a range for your lookup fields to make it easier to adjust.

Hope it helps!

austenr
08-06-2005, 10:06 AM
Thanks Ken,

Question. I am going to be using this as a template for a wide range of rows. For example side 1 could be 1150 rows and side be could be 900 rows. The next time it could be the opposite. Will this work on the fly or do you have to adjust the formula every time you use new data. This is going to be distributed to co workers most of whom know nothing about vlookup or formulas period. Is there a way to let them define the data area by highlighting the areas to be used.

This works fine for me personally the way it is because I know how to adjust the formulas to make it fit my data selection. Good idea using non VBA code.

After some playing around with the formula I guess an explaination would be of some value. If you want to you can PM me with the details. This is what I want just need to feel comfortable with the formulas. Thanks again. :thumb:yes

Ken Puls
08-06-2005, 11:00 PM
Hi Austen,

My recommendation on this would be to apply a named range to the lookup table. (So instead of using a lookup range of, say B1:B250, it uses something more dynamic.

It's late here, (brain not working,) so need to figure out how to adjust the named range. Could be via VBA or could be elsehow as well... I seem to recall that Dave Hawly had an example of how to make a dynamic named range in his previous excel newsletetters at ozgrid... maybe April/May? If you can't get there by the time I get back to the site, I'll try and hunt down a method for you.

Cheers!

austenr
08-07-2005, 10:08 AM
Ken,

To make this easier could you please format the left side for me down to row 20000 and do the same for the right side? Thanks really appreciate it.

Desert Piranha
08-07-2005, 02:09 PM
Hi,
Try here for dynamic range.

http://www.contextures.com/xlNames01.html

this help?
Dave

Hi Austen,

My recommendation on this would be to apply a named range to the lookup table. (So instead of using a lookup range of, say B1:B250, it uses something more dynamic.

It's late here, (brain not working,) so need to figure out how to adjust the named range. Could be via VBA or could be elsehow as well... I seem to recall that Dave Hawly had an example of how to make a dynamic named range in his previous excel newsletetters at ozgrid... maybe April/May? If you can't get there by the time I get back to the site, I'll try and hunt down a method for you.

Cheers!

Ken Puls
08-07-2005, 02:19 PM
Thanks, Dave! That's what I was thinking about, yes!

Austen, I'm going to do the really easy part here and give you these two forumlas. Open the conditional formatting on cell I5. Change:
Formula1 to =ISNA(VLOOKUP($I5,$B$5:$F$20000,1,FALSE))
Formula2 to =I5<>VLOOKUP($I5,$B$5:$F$20000,COLUMN(B65535)-1,FALSE)

Using the format painter, copy the formats to cells I5:F20000

Now, this will only work for those ranges. If you do want to make the formula dynamic, follow the link that Dave provided. One caveat I will give you on that though... if you have a (completely) blank cell in your data, the dynamic range doesn't represent properly. (The COUNTA function does not count it.)

HTH,

austenr
08-08-2005, 06:51 AM
OK Slightly confused. First of all I have never used this tool before. The other thing is do the formulas you gave me go on the left side of the spreadsheet also? The reason I am asking is on the original file you set up there were no formulas in columns B thru F. Thanks

Ken Puls
08-08-2005, 08:56 AM
Hi Austen,

Okay, the light is slowly going on here... You want the same highlighting type thing to occur if formulas in the first few columns don't have a match in the right side as well as what I already set up?

Which tool haven't you used? Format Painter or Conditional Formatting?

Format Painter is the little paintbrush icon. Go to the cell that has the format you want, click the paintbrush once, then click the cell you want the format on. If you want to do multiple cells, you'd double click the format painter, but you'll have to click it to off afterwards or it will keep applying formats.

For conditional formatting, you can pretty much look at it like this: When (your formula here) = TRUE then apply the format. So the trick is to make the formula work first, as it doesn't give you much feedback in the conditional format box. I sometimes do this in a cell:

=if([enter conditional format here],"success","failure")

Once I get success, I cut that formula into the format painter. You just need to watch the references a bit, that's all.

Honestly, the best way to learn it is by fooling around with it. Check the formats on the right side that I put in. You'll pick it up quick.

And yes, you will need to put them in on the left. Sorry 'bout that!