Sortng Problem with capitals
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 |
Sortng Problem with capitals
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. "Don Guillett" wrote: Why not use a macro to change all to all caps or all to lower FIRST -- Don Guillett SalesAid Software "Kal321" wrote in message ... 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 |
Sortng Problem with capitals
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 "Kal321" wrote: 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 |
Sortng Problem with capitals
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? "B. R.Ramachandran" wrote: 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 "Kal321" wrote: 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 |
Sortng Problem with capitals
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 "Kal321" wrote: 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? "B. R.Ramachandran" wrote: 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 "Kal321" wrote: 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 |
Sortng Problem with capitals
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 "Kal321" wrote: 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 |
All times are GMT +1. The time now is 03:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com