Converting cell values to common format for cell comparison

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?
LVL 50
Dale FyeAsked:
Who is Participating?
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
Dale FyeAuthor Commented:
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.
Patrick MatthewsCommented:

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Dale FyeAuthor Commented:

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.
Dale FyeAuthor Commented:

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.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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.
Dale FyeAuthor Commented:
Did not realize this was still open.  Please reopen so that I can grant points as appropriate.
Dale FyeAuthor Commented:
did not fully address the question of converting formats.  I resolved this issue, but used some of the ideas provided here.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.