Converting cell values to common format for cell comparison
Posted on 2011-09-04
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?