Converting cell values to common format for cell comparison

Posted on 2011-09-04
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 (Access MVP)
LVL 47

Author Comment

by:Dale Fye (Access MVP)
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 92

Expert Comment

by:Patrick Matthews
ID: 36481092

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

LVL 32

Accepted Solution

Rob Henson earned 500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 47

Author Comment

by:Dale Fye (Access MVP)
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 47

Author Comment

by:Dale Fye (Access MVP)
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

Expert Comment

by:Ingeborg Hawighorst
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 47

Author Comment

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

Author Closing Comment

by:Dale Fye (Access MVP)
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

825 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