Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

609 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