Sortng Problem with capitals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kal321 - 30 Jan 2006 15:10 GMT

Hi.
I posted this last week to the wrong group I think so appologies for the
cross post. Anyway the problem is:

I have a very large amount of data extracted from an external db of which
some entrys are all caps, and the majority of records are caps and lower
case. I want to sort so that the caps records are either all first, or all
last so that I can then go back into the external db, and correct those
records. It would be possible to go through this manually but that would take
a very long time to complete as currently it stands at six workbooks with
2000+ pages in each. I have tried using the case sensative option under the
data search but this doesnt seem to have any effect.
Anyone got any suggestions as to how to resolve this?
Thanks in advance
K
All the records are in English, and despite using teh

Reply to this Message

Don Guillett - 30 Jan 2006 15:45 GMT

Why not use a macro to change all to all caps or all to lower FIRST

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi.
> I posted this last week to the wrong group I think so appologies for the
[quoted text clipped - 14 lines]
> K
> All the records are in English, and despite using teh

Reply to this Message

Kal321 - 30 Jan 2006 16:05 GMT

This information was created from data extracted from an external db. That db
is the product of an very old in-house db and 10 years worth of work. As a
result very old records were created or imported in a capitalised form. I
want to be able to identify the capitalised records so that I can then send
staff into the databse to conduct clean up, by overlaying from a 3rd party
subscription database. If I were to change them to a lowerc case I would have
no other way of identifying which records were the ones requiring clean up.

> Why not use a macro to change all to all caps or all to lower FIRST
>
[quoted text clipped - 16 lines]
> > K
> > All the records are in English, and despite using teh

Reply to this Message

B. R.Ramachandran - 30 Jan 2006 19:34 GMT

Hi,

Let's suppose that the data are in A2. Create a helper column (say B or
so), with the following formula in row 2 (i.e., B2 or so) and autofill down
to the last row.

=IF(EXACT(A3,UPPER(A3)),ROW(A1),COUNTA(A:A)+ROW(A1))

Now select the entire range including the helper column and sort by the
helper column ascending (or descending). Records with all caps format will
bunch up first(or last).

PS: If you do a presort of column A before doing the above, the all-caps
records would be arranged alphabetically.

Regards,
B. R. Ramachandran

> Hi.
> I posted this last week to the wrong group I think so appologies for the
[quoted text clipped - 12 lines]
> K
> All the records are in English, and despite using teh

Reply to this Message

Kal321 - 30 Jan 2006 20:52 GMT

HI.
Thanks for that. I have tried this at an experimental level and all I get in
column B is numbers. At the risk of sounding really dense what am I missing?

> Hi,
>
[quoted text clipped - 30 lines]
> > K
> > All the records are in English, and despite using teh

Reply to this Message

B. R.Ramachandran - 30 Jan 2006 21:16 GMT

Hi,

Yes, the formula will create numbers in Column B. Select the entire range
(Columns A and B) --> "Data" --> "Sort" --> "Sort by" Column B --> "OK"

By the way, there was a typo in the formula I posted; the formula in B2
should be,

=IF(EXACT(A2,UPPER(A2)),ROW(A1),COUNTA(A:A)+ROW(A1)

Sorry about that.

Regards,
B. R. Ramachandran

> HI.
> Thanks for that. I have tried this at an experimental level and all I get in
[quoted text clipped - 34 lines]
> > > K
> > > All the records are in English, and despite using teh

Reply to this Message

Kal321 - 30 Jan 2006 22:01 GMT

Hello again.
Just wanted to say many thanks for all your help have got it all working
great and saved myself a lot of time as result.
Thank you so much again
Kal

> Hi.
> I posted this last week to the wrong group I think so appologies for the
[quoted text clipped - 12 lines]
> K
> All the records are in English, and despite using teh

Reply to this Message