Converting cell values to common format for cell comparison

Posted on 2011-09-04
Medium Priority
Last Modified: 2012-05-12
Let me start out by saying I'm only marginally proficient with Excel, most of my work is done in Access and SQL Server.  However, I have a client that has an Excel issue that they asked me to look at.  

It's a relatively simple issue:  each month they get a worksheet of data from their accounting software that lists each of their accounting codes and the amount spent that month.

They copy this worksheet (I'll refer to it as shtDump) into another workbook, then one of their employees spends several days transferring data to a new column in shtRolling.  This employee reviews every account code in the two spreadsheets.  Where there is a match, she just copies the amout from shtDump to the new column of shtRolling.  Where there is no match, she inserts a new row in shtRolling (sequentially), adds the accounting code in column1, enters a zero in all of the previous columns and fill's in the column for the current month.  Where there is an account code in shtRolling but not in shtDump, she simply adds a zero to the new column in shtRolling.

So, my task is to automate this process.  The problem is that in shtDump, all of the account codes use the format:


where the value is preceeded by a single quote.  In shtRolling, some of the cells do not appear to have the single quote, but when I click in the cell, the single quote appears.  When I do an equality comparison, these cells are not "found", and they don't sort properly either.  When I try to format the cells in shtRolling, they are formatted as text.

Is there conversion function I can use or some other technique to ensure that these cell value comparisons will work correctly?
Question by:Dale Fye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Author Comment

by:Dale Fye
ID: 36480763
For the moment, the syntax I am using is:

if val(replace(shtRolling.cells(intRowRolling, intColRolling), "'", "")) = val(replace(shtDump.cells(intRowDump, intColDump), "'", "")) Then

This is working properly but is rather clunky.  Am still hoping someone will recommend something that is a bit more elegant.
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36481092

Any chance of posting a structurally similar file with dummy or obfuscated data?

LVL 33

Accepted Solution

Rob Henson earned 1500 total points
ID: 36483613
I do similar with a download from a SAP P&L account on a monthly basis.

The process of copying the values from one sheet to the other can easily be made more efficient by using a vlookup formula, with the following assumptions:

shtDump has codes in column A and values in column B
shtRolling has codes in column A


This will return the value of the code in A1 from column B of shtDump or a #NA error if not found.

The formula can be copied down the column in shtRolling for the extent of your data. When a new period occurs, copy the formulae across a column and then copy, paste values the previous period.

In the shtDump sheet have a check column that the code is in the shtRolling:


This will return the row number on which the code was found or a #Value error if not.

Apply a filter to this column and you can then show only the erroneous entries so that the relevant codes can then be inserted into shtRolling copying down the lookup formula from the row above when new rows are inserted.

If there is a situation where a code that has been used in previous periods is not in the current period, the lookup formula will need an error check so that it does not return the #NA.

Depending on version of excel:

2003 or prior:


2007 and later:


Think that covers it. If you have any further questions, let us know.

Rob H

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 48

Author Comment

by:Dale Fye
ID: 36504360

Did not get a chance to cut examples of the worksheets.  Will do that this afternoon.


Have looked at both VLookup() and Match() but because the values in those cells don't match (some have the ' preceeding the value, some don't), those functions are not working.  I know this problem has to do with treating numbers as text and visa versa, but this is a bit annoying.

For now, the use of Val(Replace(....)) seems to be working.  Will post a sample workbook this afternoon.
LVL 48

Author Comment

by:Dale Fye
ID: 36506040

Here is a sanitized file, with my code.

I'm certain the code is not very elegant, but with my extremely limited knowledge of the Excel object model, it's the best I could come up with.

I've got several other pieces that I'm woking on WRT this file (writing formulas and conditional formatting that I need to extend over multiple columns or rows) but I'll post each of those questions separately.  And refer back to this one, if necessary.

Is there anyway with Excel to shrink the file size (like Compact in Access).  This spreadsheet originally had about 25 tabs and lots of data, was over 1.2 Meg.  But 529K seems awfully large for the limited amount of information that is left in the file.
LVL 50
ID: 37056180
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
LVL 48

Author Comment

by:Dale Fye
ID: 37056181
Did not realize this was still open.  Please reopen so that I can grant points as appropriate.
LVL 48

Author Closing Comment

by:Dale Fye
ID: 37056189
did not fully address the question of converting formats.  I resolved this issue, but used some of the ideas provided here.

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question