Posted: 4/2/2011 7:56:55 PM EDT
|
I have sheet with a list of 10 customers
That is 11 rows as row 1 contains the headers I want to assign each customer a unique ID number (45-0001 to 45-0010) When I select all and sort by zip, city etc, the ID number must stay with the customer. Driving me crazy! help please? |
|
Col A is empty as it needs to assign a incremental list of ID no's. B is the business name, C is Address etc
So I need to assign all 10 (or 1000) rows a unique ID number in Col A The ID number needs to become a hard number and tagged to the row data. All I can find is fixes that will number the rows, but the number will remain the same when you sort the data, even if you select the column  or the ID no's will sort, but no longer be tagged to the correct customer.
|
|
You need to select the entire range of data to sort. If you just select the first column it will only sort that. Highlight the entire data set, starting with the numbered column, and sort it, all the other data will stay in the correct row. eta If you want to sort by a different column (eg zip code), select the entire data set, go to "data", select "sort", then select the columns(s) you want to sort by. if you've highlighted the entire data set, the entire row will move.
|
|
Quoted: 0000 select the first column, go to "format cells" - "custom" - select "0" from the list and add three more 0s in the entry box below "type", hit ok. type 1 in the first cell, 2 in the second, select those 2 cells, and grab the bottom right corner and expand the list. eta - assuming you just want a sequentially numbered list that displays leading zeros. if not, you'll most likely have to type it in, or use some sort of if-then formula.
|
|
Cell A2 type 45-000 Cell A3 type 45-001 Select A2-A3 Grab the little black box at the bottom right of the selection. Drag down. CustIDs will be filled in sequentially. After all other data is filled in, Select entire dataset. Use the "Data" tool to sort. After that, you can sort/filter based on the column headers you setup. Or you could be a boss and use Access. |
|
Quoted: OK, done Now how to make the 4 digit ID stay with the row when sorting? select the entire range of data, go to "data" - "sort" select the column(s) you want to sort and whether ascending or descending. if you've highlighted all the data, it will keep the rows intact. |
|
Quoted:
Cell A2 type 45-000 Cell A3 type 45-001 Select A2-A3 Grab the little black box at the bottom right of the selection. Drag down. CustIDs will be filled in sequentially. After all other data is filled in, Select entire dataset. Use the "Data" tool to sort. After that, you can sort/filter based on the column headers you setup. Or you could be a boss and use Access. +1. I'm not sure exactly what you are trying to do, but it appears to me that Access is what you should be using. Word=memos, documents Excel=calculations only (yes, you can do more, but...) Access=sorts, mailing lists, calculations, decisions, comparisons of entries These are generalizations, but... right tool, right job. What are you actually trying to do, and are you sure that Excel is the right tool? TRG |
|
Quoted: THANKS Uncivil! I know how to sort but it was a bitch getting the id's to stay with the row data with all col's selected. THIS WORKS!  glad to be of service. don't get to use excel too much now that i'm out of school, but the Navy sure has perfected my Powerpoint skills  |
|
TRG,
All my leads come to me in CSV format. Once I put them in excel I can Mail Merge them to labels in Word for mailings. I only needed to assign a permanent ID number to the customer. Excel works well for me, I just hit a little issue with the ID's tagged to the customer. Tks |