Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sorting and Subtotaling Issues

  Asked By: Viveka    Date: Feb 21    Category: MS Office    Views: 773
  

I know this isn't a VBA question per se but I thought I'd ask it anyway....

To complete a report I have to import several workbooks that list customer
data into one master workbook, sort, subtotal and determine the top 10
accounts.

My problem is there are several *similar* names for the same account
Example:

Cincinnati Gas & Electric Co.
Cincinnati Gas & Electric

Crimson Coal Corp.
Crimson Coal Corp

Georgia Power Co.
Georgia Power Company

And as you know, Excel will not recognize any of those groupings above as the
same when I use the subtotal function.

The workbooks, and the data in them, come from a completely separate group at
work so I don't think I'll be able to change the source data.

There aren’t a static number of account names either. Creating a master list
of all accounts won’t work as that list grows every month, etc.

What are my options in a situation like this?

1)Sort the names alphabetically and manually review them (least appealing)
2)Use some string functions to compare one line with the other and then change
one account name so it matches? Would something like this work?
3)No idea

I’ve got a macro to do the compiling and other “data assembly” work on this
report and I’d like this to be as “hands-off” as I can make it.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Rory Anderson     Answered On: Feb 21

this kind of situation is always a challenge.
You sure you can't go back to the source data  and make sure it gets
entered consistently? Why is there some system that has the data being
entered inconsistently? You really sure you can't do anything about
that?

Next, is there some way an account number  is already assigned to each of
these records? Then you wouldn't have to worry about spelling
inconsistencies.

I can't see any way that the final word in a situation like this could
rest anywhere than on a human. Some person has to sit there and make a
decision about which ones are matches or not.

Of course, the computer can help -- maybe you create an algorithm that
says if the first bunch of characters match, and it's 75% of the total
string length, then it's a match. So 12345678 and 12345699 would match
because the first 6 of 8 chars match (75%). Something like that...?

 
Answer #2    Answered By: Scott Anderson     Answered On: Feb 21

. You need to catch your problem  at the source,
and that is the user input. By creating  a master  list, that can be
updated and edited by the user, you will be able to organize your data
later.

Personally, I am a believer in using MS-Access with MS-Excel. By placing
this data  into a single repository for user entry, and then exporting it
to Excel for reporting. Either way, your goal is to control the user
input.

Since the different names  reference the same account, we can surmise
that there is data that the two share. By creating a master list, and a
user input form, you can control how the new names are entered. The user
will have to select the account the names are associated with, and
viola! You have a key to link all your data.

 
Didn't find what you were looking for? Find more on Sorting and Subtotaling Issues Or get search suggestion and latest updates.




Tagged: