Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

How to rank numbers uniquely in excel without duplicates?

user-image
Question ajoutée par Mike Emerson Pasaron , Safety Officer , Arabian Petrochemical Co. (PETROKEMYA)
Date de publication: 2013/06/08

The work of the desired number, and then the cells that draw after him, and mobilize action sequence

Filter values ​​and remove duplicates In Microsoft Excel, several ways to filter the unique values ​​or remove duplicate values​​: To filter the unique values​​, use the Advanced Filter Options control group and filter the Data tab.
To remove duplicate values​​, use the Remove Duplicates the Data Tools group on the Data tab.
To highlight the unique or duplicate values​​, use the setup command Conditional Formatting in the Styles group Origin tab.
Filter unique values Select the range of cells, or make sure that the active cell is in a table.
On the Data tab, in the Sort & Filter group, click Advanced.
In the Advanced Filter dialog box, do one of the following: To filter the range of cells or table displayed, click Filter the list in place.
To copy the filter results to another location, perform the following actions: Click Copy to another location.
In the Copy to box, enter a cell reference.
Or, click Shrink dialog box to temporarily hide the dialog box, select the cell in the spreadsheet and click Expand the dialog box.
Activate only check box without duplicates, and then click OK.
Unique values ​​in the selected range are copied to the new location.
Delete duplicate values When you remove duplicate values, only the values ​​of the range of cells or table are deleted.
Other values ​​outside the range of cells or table are not altered or moved.
Because you permanently delete the data, it is advisable to copy the original range of cells or table in another worksheet before deleting.
Select the range of cells, or make sure that the active cell is in a table.
On the Data tab, in the Data Tools group, click Remove Duplicates.
Perform at least one of the following: Under Columns, select one or more columns.
To quickly select all columns, click Select All.
To quickly clear all columns, click Deselect All.
If the range of cells or table column contains numbers when you only want to select a few, it may be easier to click Deselect All and select the columns under Columns.
Click OK.
Excel displays a message indicating the number of duplicate values ​​removed and the remaining number of unique values ​​or to indicate that no duplicate value has been deleted.
Click OK.
Problem: I am having problems removing duplicate values ​​in structured data plan or containing subtotals.
Apply conditional formatting to single or double values  NOTE You can not apply conditional formatting to fields in the Values ​​area of ​​a PivotTable report with unique or duplicate values.
Quick formatting Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Rules highlighted cells.
Select values ​​in duplicate.
Enter the values ​​to use and then select a format.
Advanced Formatting Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.
The Manager dialog box rules conditional formatting appears.
Do one of the following: To add a conditional format, click New Rule.
The New Rule dialog formatting appears.
To change a conditional format, do the following: Make sure the spreadsheet or the appropriate table is selected in the Show list box rules for formatting.
If you wish, you can change the range of cells by clicking Collapse dialog box in the Apply to box to temporarily hide the dialog box and then selecting the new range of cells on the worksheet.
Then select Expand the dialog box.
Select the rule and click Edit Rule.
The Edit Rule formatting dialog box appears.
Under Select a Rule Type, click Apply formatting only unique or duplicate values.
Under Edit the Rule Description, in the Apply formatting to all values ​​list, select single or double Click Format to display the Format Cells dialog.
Select the number, font, border or filling you want to apply when the cell value meets the condition, then click OK.

Mike Emerson Pasaron
par Mike Emerson Pasaron , Safety Officer , Arabian Petrochemical Co. (PETROKEMYA)

Suppose the range is A2:A10: =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1 Don't you think? :)

More Questions Like This