Still about Excel?

Question

How to delete the same value at the colomn? manually i can do it, but not for thousand records.
Example:
A
Car
Car
Car
Bus
Bus
Motorcycle
Bicycle
Bicycle
....and so on

And the output is:
A
Car
Bus
MotorCycle
Bicycle
....

Thx

Answer 1

I think you're going to have to do this the hard way.

say your column of data is in column A, and cell A1 is the header. So A2 would be the 1st item in your list - Car in your example.

first, SORT the list by column A. this puts all the Cars, Bicycles etc next to each other.

now put the following formula in cell B2:
=IF(A2=A1,"duplicate","")
..then copy that down the rest of the B column.

now delete every line where "duplicate" appears in the B column and you should be left with distinct values in column A

if you really have "thousands" of lines - you can copy and paste values in column B, then sort by column B to easily delete all the "duplicate"s at once.

Answer 2

Hello there! Oh how fun it is to tinkle with macros. That's what you need, is to create a macro. It's quite simple. I can tell you but it's already done hehs at this site...

http://www.cpearson.com/excel/deleting.htm

NOTE: Remember, these macros delete entire rows from your worksheet. They do not delete individual cells.

You can use the following code snippet, modifiy it to suit your needs to delete individual cells ...

Sub DeleteThem()
'This will do this for Column A down to row 10000
Dim i as Integer
For i = 1 to 10000
If Range("A" & i).Value = "--" Then
Range("A" & i).Delete Shift:=xlUp
Next i
End Sub

Hope this helps!

Answer 3

Here is how to get rid of extra records. You have to use advance filter. I am copying several examples. but in your particular example if you click Data/Filter/Advance Filter/Copy to another location
List range (select the entire list you want to filter)
Skip criteria range for now
Copy to (select another sheet or move over to the right a couple of columns to watch this work.
Click Unique records only
Click "OK"

Here are the other instructions, but you can get them from Help/Filter

About filtering
Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column. Microsoft Excel provides two commands for filtering lists:

AutoFilter, which includes filter by selection, for simple criteria
Advanced Filter, for more complex criteria
Unlike sorting, filtering does not rearrange a list. Filtering temporarily hides rows you do not want displayed.

When Excel filters rows, you can edit, format, chart, and print your list subset without rearranging or moving it.

AutoFilter

When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered list.

Unfiltered list

Filtered list

Microsoft Excel indicates the filtered items with blue.

You use custom AutoFilter to display rows that contain either one value or another. You can also use custom AutoFilter to display rows that meet more than one condition for a column; for example, you might display rows that contain values within a specific range (such as a value of Davolio).

Advanced Filter

The Advanced Filter command can filter a list in place like the AutoFilter command, but it does not display drop-down lists for the columns. Instead, you type the criteria you want to filter by in a separate criteria range above the list. A criteria range allows for more complex criteria to be filtered.

Examples of complex criteria

Multiple conditions in a single column

If you have two or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.

Salesperson
Davolio
Buchanan
Suyama

One condition in two or more columns

To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.

Type Salesperson Sales
Produce Davolio >1000

One condition in one column or another

To find data that meets either a condition in one column or a condition in another column, enter the criteria in different rows of the criteria range. For example, the following criteria range displays all rows that contain either "Produce" in the Type column, "Davolio" in the Salesperson column, or sales values greater than $1,000.

Type Salesperson Sales
Produce
Davolio
>1000

One of two sets of conditions for two columns

To find rows that meet one of two sets of conditions, where each set includes conditions for more than one column, type the criteria in separate rows. For example, the following criteria range displays the rows that contain both "Davolio" in the Salesperson column and sales values greater than $3,000, and also displays the rows for salesperson Buchanan with sales values greater than $1,500.

Salesperson Sales
Davolio >3000
Buchanan >1500

More than two sets of conditions for one column

To find rows that meet greater than two sets of conditions, include multiple columns with the same column heading. For example, the following criteria range displays sales that are between 5,000 and 8,000 in addition to sales that are less than 500.

Sales Sales
>5000 <8000
<500

Conditions created as the result of a formula

You can use a calculated value that is the result of a formula as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list. For example, the following criteria range displays rows that have a value in column C greater than the average of cells C7:C10.

=C7>AVERAGE($C$7:$C$10)

Notes

The formula you use for a condition must use a relative reference to refer to the column label (for example, Sales) or the corresponding field in the first record. All other references in the formula must be absolute references, and the formula must evaluate to TRUE or FALSE. In the formula example, "C7" refers to the field (column C) for the first record (row 7) of the list.
You can use a column label in the formula instead of a relative cell reference or a range name. When Microsoft Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore this error because it does not affect how the list is filtered.

When evaluating data, Microsoft Excel does not distinguish between uppercase and lowercase characters.