[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel 2010 sorting issue

Posted on 2014-08-05
6
Medium Priority
?
115 Views
Last Modified: 2014-08-18
Hi All


            After sorted by specific name, and in the index field when i try to press CTRL + left click the cross and drap down, suppose it will accumulate the number such as CE-CM1 , CE-CM2, any idea ?




sorting problem
0
Comment
Question by:piaakit
  • 3
  • 2
6 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40243024
Hi,

If you want an Index that goes from CE - CM1 to CE - CM100 for example not affected by the sorting use a formula (in M2) and fill down

="CE - CM"&ROW(B2)-1

or they can be resorted

Home / Editing / Fill / Series / Autofill OK

Regards
0
 

Author Comment

by:piaakit
ID: 40243879
any other way to fix, the one above dont work
0
 

Author Comment

by:piaakit
ID: 40243889
if i put 1 instead of CE-CM1, it can drag 1 - 10
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40243943
Normally you can drag

CE-CM1 and the number will also add up

Regards
0
 

Author Comment

by:piaakit
ID: 40243979
but i have tried it can not add up after i created filter
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40248020
The "drag and fill" or aufofill function will not work on filtered data.  Period.

If you want to create a sequence of new index numbers from the filtered data you'll have to use another method.  Here's one way you can achieve that:

1) Unfilter your data
2A)  If there are no values in the INDEX column (M) then enter this formula in cell M2 and copy down:
=IF(P2="COURIER (EXTERNAL)","CE - CM"&COUNTIF($P$2:P2,"COURIER (EXTERNAL)"),"")
You'll see the index show up for the COURIER (EXTERNAL) rows, incremented by one each time.
3A) Select all the cells in column M, copy, and paste the values in their place.

2B) If there are currently values in the INDEX column (M) then insert a temporary column in column N and add this formula in cell N2 and copy down:
=IF(Q2="COURIER (EXTERNAL)","CE - CM"&COUNTIF($Q$2:Q2,"COURIER (EXTERNAL)"),IF(M2="","",M2))
(Note that the formula is looking in column Q instead of P since this inserts a new column in N)
3B) Select all the cells in column N, copy, and paste the value in column M.
4B) Delete column N.

Note that you could replicate this process for the other three Delivery Arrangement categories if you wanted to increment them as well.  just replace the text strings being searched and the Index abbreviation in the formula.  Example file attached showing both formulas before copy and paste values.

Regards,
-Glenn
EE-AutoNumber.xlsx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question