software

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 tehWhy not use a macro to change all to all caps or all to lower FIRST

--
Don Guillett
SalesAid Software

quot;Kal321quot; gt; wrote in message
...
gt; Hi.
gt; I posted this last week to the wrong group I think so appologies for the
gt; cross post. Anyway the problem is:
gt;
gt; I have a very large amount of data extracted from an external db of which
gt; some entrys are all caps, and the majority of records are caps and lower
gt; case. I want to sort so that the caps records are either all first, or all
gt; last so that I can then go back into the external db, and correct those
gt; records. It would be possible to go through this manually but that would
gt; take
gt; a very long time to complete as currently it stands at six workbooks with
gt; 2000 pages in each. I have tried using the case sensative option under
gt; the
gt; data search but this doesnt seem to have any effect.
gt; Anyone got any suggestions as to how to resolve this?
gt; Thanks in advance
gt; K
gt; All the records are in English, and despite using teh
gt;
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.

quot;Don Guillettquot; wrote:

gt; Why not use a macro to change all to all caps or all to lower FIRST
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Kal321quot; gt; wrote in message
gt; ...
gt; gt; Hi.
gt; gt; I posted this last week to the wrong group I think so appologies for the
gt; gt; cross post. Anyway the problem is:
gt; gt;
gt; gt; I have a very large amount of data extracted from an external db of which
gt; gt; some entrys are all caps, and the majority of records are caps and lower
gt; gt; case. I want to sort so that the caps records are either all first, or all
gt; gt; last so that I can then go back into the external db, and correct those
gt; gt; records. It would be possible to go through this manually but that would
gt; gt; take
gt; gt; a very long time to complete as currently it stands at six workbooks with
gt; gt; 2000 pages in each. I have tried using the case sensative option under
gt; gt; the
gt; gt; data search but this doesnt seem to have any effect.
gt; gt; Anyone got any suggestions as to how to resolve this?
gt; gt; Thanks in advance
gt; gt; K
gt; gt; All the records are in English, and despite using teh
gt; gt;
gt;
gt;
gt;

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

quot;Kal321quot; wrote:

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

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?

quot;B. R.Ramachandranquot; wrote:

gt; Hi,
gt;
gt; Let's suppose that the data are in A2. Create a helper column (say B or
gt; so), with the following formula in row 2 (i.e., B2 or so) and autofill down
gt; to the last row.
gt;
gt; =IF(EXACT(A3,UPPER(A3)),ROW(A1),COUNTA(A:A) ROW(A1 ))
gt;
gt; Now select the entire range including the helper column and sort by the
gt; helper column ascending (or descending). Records with all caps format will
gt; bunch up first(or last).
gt;
gt; PS: If you do a presort of column A before doing the above, the all-caps
gt; records would be arranged alphabetically.
gt;
gt; Regards,
gt; B. R. Ramachandran
gt;
gt; quot;Kal321quot; wrote:
gt;
gt; gt; Hi.
gt; gt; I posted this last week to the wrong group I think so appologies for the
gt; gt; cross post. Anyway the problem is:
gt; gt;
gt; gt; I have a very large amount of data extracted from an external db of which
gt; gt; some entrys are all caps, and the majority of records are caps and lower
gt; gt; case. I want to sort so that the caps records are either all first, or all
gt; gt; last so that I can then go back into the external db, and correct those
gt; gt; records. It would be possible to go through this manually but that would take
gt; gt; a very long time to complete as currently it stands at six workbooks with
gt; gt; 2000 pages in each. I have tried using the case sensative option under the
gt; gt; data search but this doesnt seem to have any effect.
gt; gt; Anyone got any suggestions as to how to resolve this?
gt; gt; Thanks in advance
gt; gt; K
gt; gt; All the records are in English, and despite using teh
gt; gt;

Hi,

Yes, the formula will create numbers in Column B. Select the entire range
(Columns A and B) --gt; quot;Dataquot; --gt; quot;Sortquot; --gt; quot;Sort byquot; Column B --gt; quot;OKquot;

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

quot;Kal321quot; wrote:

gt; HI.
gt; Thanks for that. I have tried this at an experimental level and all I get in
gt; column B is numbers. At the risk of sounding really dense what am I missing?
gt;
gt; quot;B. R.Ramachandranquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; Let's suppose that the data are in A2. Create a helper column (say B or
gt; gt; so), with the following formula in row 2 (i.e., B2 or so) and autofill down
gt; gt; to the last row.
gt; gt;
gt; gt; =IF(EXACT(A3,UPPER(A3)),ROW(A1),COUNTA(A:A) ROW(A1 ))
gt; gt;
gt; gt; Now select the entire range including the helper column and sort by the
gt; gt; helper column ascending (or descending). Records with all caps format will
gt; gt; bunch up first(or last).
gt; gt;
gt; gt; PS: If you do a presort of column A before doing the above, the all-caps
gt; gt; records would be arranged alphabetically.
gt; gt;
gt; gt; Regards,
gt; gt; B. R. Ramachandran
gt; gt;
gt; gt; quot;Kal321quot; wrote:
gt; gt;
gt; gt; gt; Hi.
gt; gt; gt; I posted this last week to the wrong group I think so appologies for the
gt; gt; gt; cross post. Anyway the problem is:
gt; gt; gt;
gt; gt; gt; I have a very large amount of data extracted from an external db of which
gt; gt; gt; some entrys are all caps, and the majority of records are caps and lower
gt; gt; gt; case. I want to sort so that the caps records are either all first, or all
gt; gt; gt; last so that I can then go back into the external db, and correct those
gt; gt; gt; records. It would be possible to go through this manually but that would take
gt; gt; gt; a very long time to complete as currently it stands at six workbooks with
gt; gt; gt; 2000 pages in each. I have tried using the case sensative option under the
gt; gt; gt; data search but this doesnt seem to have any effect.
gt; gt; gt; Anyone got any suggestions as to how to resolve this?
gt; gt; gt; Thanks in advance
gt; gt; gt; K
gt; gt; gt; All the records are in English, and despite using teh
gt; gt; gt;

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

quot;Kal321quot; wrote:

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