MAN am I frustrated...

[jlgriffiths]

...and I'm hoping I can even explain what I'm trying to do! I have a very large file, and each record has an ID such as 121, 121A, 121B and so on. I would like to strip the letters off each one, leaving just the number, and then combine each record.

That I have no idea how to do, but here's the way tricky part. Each record is a geographical area and has population info as well as percentages. Some of the percentages are based on real-world data such as gender and ethnicity, but some other percentages are not calculations based directly on population data. For instance 121A will have a population of 1234 people and a 'customer index' of let's say 45.5%. 121B will have a population of 55 people and a 'customer index' of 25%! Is there any way to combine all my 121s into one record, preserving the accuracy of all the percentages?

I know this is confusing, but I'd appreciate any insight that anyone has into this. Thanks!

[The Shadow]

TextToNum() will strip off the letters.

As far as combining percentages, it sounds like you need something like a weighted average:

New Population = Population1 + Population2

New Index = ((Population1 * Index1) + (Population2 * Index2)) / New Population

That would give a greater weight to the larger population, but moving it toward the smaller population's index in proportion to their relative sizes.