Office & Productivity Software forum

General discussion

Excel sheet compare, formatting issue and other formula help

by drue66 / July 17, 2006 4:33 AM PDT

Hello,

I'm running Microsoft Office Excel 2003 and trying to do a spreadsheet comparison. Basically I want to compare several different columns from one spreadsheet to another, I'm almost positive that this can be done but can't remember how. Can anyone point in the right place? The other formula I need to create is to take a date format plus 1. For ex: I would like to have a column with dates entered 06/05/2006 and I would like to have the column next to it take that date and add 1 so that my result would be 06/06/2006. Lastly, I'm having some strange formatting issues when I'm trying to use v-look up. I have a report that I run from two different places that gets exported to excel and when I try to use v-lookup it just won't work there is some formatting issue when these reports export even though they contain the same information. Also, I have tried reformatting columns and tried copying and pasting the entire sheets as values only. I would appreciate any help anyone could offer.

Thanks,
Andrea

Discussion is locked
You are posting a reply to: Excel sheet compare, formatting issue and other formula help
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Excel sheet compare, formatting issue and other formula help
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
wow...been saving up
by entrecon / July 17, 2006 5:43 AM PDT

You can post as you find them and not all at once if you want...

1. Take a look at two features "Compare and Merge..." and "Compare Side by Side"

2. Put the date in the cell (i.e. A1). In the next cell put in a forula for that cell +1 (i.e. =A1+1).

3. VLookup assumes that the data is sorted based on the first column. So a column sorted out of order (i.e. 5,4,1,6,8) will not work. The column must be in the correct order (i.e. 1,4,5,6,8). If you do not put "False" as the last value of the vlookup formulas as opposed to leaving it blank or putting in "True", it will find the closest match. You need to give more detail on the type of results you are getting back here for more help.

Collapse -
Re: excel problems

Andrea,

1. Comparing.
Make a column like =[file]sheet!cell next to the one to compare, like =[original.xls]sheet2!A1 (assuming they are in the same folder. Just open the two files and point and click to get the formula rigth. Then use =if in a third column to compare, like =if(a1=b1,"","different"). Drag down by the copy handle.

2. Dates.
Assuming the original date is in A1, enter =A1+1 in the other cell. Format as date if it isn't done automatically. This is really elementary.

3. Formatting issues in vlookup.
Please tell more.

Kees

Collapse -
more specific compare issue
by drue66 / July 19, 2006 6:05 AM PDT
In reply to: Re: excel problems

Thanks for the response below, I appreciate it. I'm trying to compare two different spreadsheets that are not identical. So, I'm wondering is there anyway to match a unique identifier and if it finds a match then to compare the information in that row?

Also, as far as the formatting issue for v-lookup. I'm taking to exports from different programs, I've made sure that the info I'm looking up is in the first the first column and since I am dealing with numbers I have made sure that they are in numerical order but it still gives the #N/A error when I input the formula and I can't figure out why since i know there is a match. Does that make it any more clear?

Collapse -
Re: more info
by Kees Bakker / July 19, 2006 8:06 AM PDT

1. That's VLOOKUP, ain't it? If the information you're looking for is sorted on a key (to be able to compare the rows) in at least one of the spreadsheets. Or unless the row keys are identical, then you can just reference the identical cell in the other spreadsheet in your IF-command. If rows keys are random in both spreadsheets, you'll have to sort at least one.

2. That seems like it should work, as long as it are integers and they are sorted in at least one of the spreadsheets and they both are really numbers, not one numeric and the other text (just looking like numbers) and your formulas are OK. And we can't see anything of that unless you put your spreadsheet on your website and publish a link to it here. I'm sure entrecon will find your vlookup-problem if he has access to your spreadsheet.

Kees

Collapse -
more info
by drue66 / July 19, 2006 9:13 AM PDT
In reply to: Re: more info

Ok, I think I've gotten somewhere with a combination of vlookup and if statements I've gotten it to work, I'm just trying to figure out now how to cutout some steps. Thanks again for the tips, they were very helpful and I might be back for some fine tuning tips!

:)Andrea

Collapse -
vlookup/data types
by cledwards / July 19, 2006 4:56 PM PDT
In reply to: more info

Vlookup doesn't assume/require that your table is sorted on it's key if you are looking for an exact match. In other words, if you enter FALSE as the Range_Lookup argument, it doesn't matter how your data is sorted EXCEPT that the function will return the first EXACT match it finds.

Imported data/vlookup can't find:

This sounds like a data type issue. When you import data from a database, you don't always get the data type you expect for each field.

Not cell formatting, but the underlying type of data. Changing a cells format does not alter the data type.

Make sure your lookup values are the same data type as your extracted data. You can use Advanced Filter-Unique Values to get a list of your lookup values.

Or if you aren't working with leading zeros, you can use a vba line like Columns(1) = Columns(1).Value to get xl to convert to consistent data types. Paste Special-Values doesn't do the trick in these cases. In fact, if you try that, it will make it obvious that the data type isn't what it should be - you'll get a smart tag telling you you have a number stored as text or something.

You may also be able to eliminate data type issues when you query using calculated fields. If you can, this is definitely the way to go.

Collapse -
Combining vlook up and if in one formula
by drue66 / July 20, 2006 9:39 AM PDT
In reply to: vlookup/data types

In response to fixing my formatting issue, I saved each export as a text file and reopened as xls and everything seemed to be fine then.

On another note, I'm wondering how/if I can combine a vlook formula with an if statement in one cell. I want to say find this value in this sheet -> if A2 in sheet1 = A2 in sheet 2 than no change if A2 is different from A2 in sheet2 then there is a change. Is this possible? Does it make sense? I figured out how to do this but I had to write 6 different formulas for each column and I'm wondering how I can consolidate a bit.

Thanks,
Andrea

Collapse -
Re: if and vlookup
by Kees Bakker / July 20, 2006 5:38 PM PDT

Andrea,

In sheet1:
=if(a2=sheet2!a2,"change","nochange")
That's exactly your example question: "if A2 in sheet1 = A2 in sheet 2 than no change if A2 is different from A2 in sheet2 then there is a change."
Somehow the vlookup-part disappeared going from subject line to example question.

If you pose a better and clearer question (note the interpunction also) you might get a better answer.

Kees

Collapse -
if and vlookup -hopefully more clearly stated :)
by drue66 / July 21, 2006 7:55 AM PDT
In reply to: Re: if and vlookup

Okay, so I will try to explain better what I'm trying to do. I have two sheets that in column 1 have a unique identifier and then columns to the right contain the information I need to compare to see if there has been a change. So what I have accomplished in multiple step is the following:
Let's use sheet 1 as my sheet where I'm inputting all formulas and compiling all data. I have written a formula with vlookup to find the matching unique identifier in sheet 2 and return the information in column 2 - now part of my problem is that I need the information in column 2-6 so I have to repeat this formula across the row for each column of information that I want returned. I don't know how to simplify this end of it.

The next step, now that I have all my data on one sheet is compare the corresponding columns on my spreadsheet. So if I want to compare the data in a2 to a6 and a3 to a7 I again write formulas for each column. Does this make any more sense?

Now, I realize that I could compare a column or cell in sheet 1 to a column or cell in sheet 2 but since there is some extra data and the columns and cells don't necessarily correspond on both sheets this is why I have added in the vlookup portion.

Sorry, I hope this is a little more clear. Thanks for trying to help.

Collapse -
Combine if and vlookup.
by Kees Bakker / July 22, 2006 5:07 AM PDT

Drue,

Much clearer now. Functions can be 'nested', so you can put
=if(a2=vlookup(a1,sheet2!...),"same","different")
in a7. I leave the details of what ... should be to you, because it's exactly the formula you already have it in a7 at the moment.

See if you can get it working.

You might like to study the workings of f4 (to make references not fully relative, but partially or even fully absolute) in combination with the copy handle (the plus appearing in the lower right corner of a cell) to copy the formula right and down. With the right location of $'s in the formula, you only need to write it once (in a7), then copy it with your mouse to a8 to a11, then copy it to all columns with your mouse again. So 'writing formulas' can be replaced by 'writing 1 formula'.

Hope this helps.


Kees

Collapse -
So close...
by drue66 / July 25, 2006 5:57 AM PDT

Okay, this was great. I've almost got exactly what I need. Thank you for the f4 tip, that is a great function. Now, the only thing I'm missing is how I can compare a different column than what I'm doing the vlookup on. So let me make this as clear as I can.

so here is the basic layout of both spreadsheets:
A2 B2 C2 D2 E2 F2
A3 B3 C3 D3 E3 F3
(you get the idea...)

Now this is the fomula I have written:
=IF($A2=VLOOKUP($A2,[NMCinvcompare2.xls]NMCinvcompare!1:65536,2,FALSE),"no change","change")

So the only problem I'm encountering now is when it does the comparison and reports back change or no change, it seems to be comparing only the A2 columns. How can I get it to compare B2 to B2 and C2 to C2 etc... within the formula I have above. Or can you tell from the formula above exactly what it is comparing and reporting back on?
Does that make sense?

Collapse -
Close indeed.
by Kees Bakker / July 25, 2006 7:14 AM PDT
In reply to: So close...

Drue,

Put this formula in G2 and change the text in "no change in column A" and "change in column A". I would prefer the last string to be emptuy, just "", but that's everybody's choice. Then copy the formula to column H and change the text 'column A' to 'column B', or whatever these column MEANS (like 'name'). Note you've got to remove the $ in the formula before copying, or $a2 will stay $a2, while you want it to become b2, of course. Same for I (referring to C) etc.
Then drag and drop (with the copy handle) G2:L2 down as far as you need. Done.

Hope this helps.


Kees

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

GIVEAWAY

Turn up the volume with our Apple Byte sweeps!

Two lucky winners will take home the coveted smart speaker that lets Siri help you around your connected house. This sweepstake ends Feb. 25, 2018.