Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-Fill Data in alphabetical order?
Hi,
I'm working for LCD company. I'm looking for the easier way to input 'panel ID's. Here is the example. 4NJ8CD001AA 4NJ8CD001AB 4NJ8CD001AC ..... Even though I thought Auto-filling function should work, Unfortunately, it didn't seem to work. Instead, it generate the data like this. 4NJ8CD001BA 4NJ8CD002BA 4NJ8CD003BA 4NJ8CD004BA 4NJ8CD005BA 4NJ8CD006BA 4NJ8CD007BA Does anyone know how to solve my problem? Please, help me. It's annoying to input thousands of panel ID one by one. -- Thanks for your concerns! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-Fill Data in alphabetical order?
To generate this series:
4NJ8CD001AA 4NJ8CD001AB 4NJ8CD001AC Put in any starting cell, say in B2: ="4NJ8CD001"&CHAR(INT((ROWS($1:1)-1)/26)+65)&CHAR(MOD(ROWS($1:1)-1,26)+65) Copy down as far as required -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "midsummer" wrote: Hi, I'm working for LCD company. I'm looking for the easier way to input 'panel ID's. Here is the example. 4NJ8CD001AA 4NJ8CD001AB 4NJ8CD001AC .... Even though I thought Auto-filling function should work, Unfortunately, it didn't seem to work. Instead, it generate the data like this. 4NJ8CD001BA 4NJ8CD002BA 4NJ8CD003BA 4NJ8CD004BA 4NJ8CD005BA 4NJ8CD006BA 4NJ8CD007BA Does anyone know how to solve my problem? Please, help me. It's annoying to input thousands of panel ID one by one. -- Thanks for your concerns! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-Fill Data in alphabetical order?
It is always a good idea to tell us about your layout so that when we give
you formula solutions, you can use them directly. Since you didn't tell us what column the "panel ID's" are in, I guessed at Column D. You also didn't tell us what row the "panel ID's" start in, so I guessed at Row 2. Manually put the first "panel ID" in D2, then put this formula in D3 and copy it down as far as you need to... =IF(MID(D2,7,3)="999",IF(RIGHT(D2)="Z",LEFT(D2,6)& "001"&CHAR(CODE(MID(D2,10,1))+1)&RIGHT(D2),LEFT(D2 ,6)&"001"&MID(D2,10,1)&CHAR(CODE(RIGHT(D2))+1)),LE FT(D2,6)&TEXT(MID(D2,7,3)+1,"000")&RIGHT(D2,2)) Obviously, if my guess of D2 as the first "panel ID" was incorrect, then change all the D2 references to whatever cell you put the first "panel ID" in. Note that this formula will handle up to 999ZZ as the last 5 characters in your "panel ID"... if you can surpass that value, then you will need a different formula or approach (but you would have to tell us the increment order for the remainder of the characters). -- Rick (MVP - Excel) "midsummer" wrote in message ... Hi, I'm working for LCD company. I'm looking for the easier way to input 'panel ID's. Here is the example. 4NJ8CD001AA 4NJ8CD001AB 4NJ8CD001AC .... Even though I thought Auto-filling function should work, Unfortunately, it didn't seem to work. Instead, it generate the data like this. 4NJ8CD001BA 4NJ8CD002BA 4NJ8CD003BA 4NJ8CD004BA 4NJ8CD005BA 4NJ8CD006BA 4NJ8CD007BA Does anyone know how to solve my problem? Please, help me. It's annoying to input thousands of panel ID one by one. -- Thanks for your concerns! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to auto arrange in alphabetical order-excel sheet | New Users to Excel | |||
Alphabetical order possible? or not | Excel Discussion (Misc queries) | |||
sort rows in alphabetical order and move the corresponding data | Excel Worksheet Functions | |||
Changing the auto fill order from ascending to descending | Excel Discussion (Misc queries) | |||
Using a macro to sort your data in alphabetical order? | Excel Discussion (Misc queries) |