Solved

Converting cell values to common format for cell comparison

Posted on 2011-09-04
9
273 Views
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:

'xxx.yyy

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?
0
Comment
Question by:Dale Fye (Access MVP)
[X]
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
9 Comments
 
LVL 48

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.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36481092
fyed,

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

Patrick
0
 
LVL 33

Accepted Solution

by:
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

=VLOOKUP($A1,shtDump!$A:$B,2,FALSE)

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:

=MATCH($A1,shtRolling!$A$A,0)

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:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

2007 and later:

=ISERROR(VLOOKUP(...),0)

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

Thanks
Rob H
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 36504360
Patrick,

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

Rob,

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.
0
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
ID: 36506040
Partick,

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.
EE-Example.xlsm
0
 
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.
0
 
LVL 48

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.
0
 
LVL 48

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.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

688 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