Question about Microsoft Computers & Internet

1 Answer

About formula i have large number of entires in one column in different rows, if a enrty repeat in any row how i guess that the entry is repeated

Posted by maqsud_ahmad on

Ad

1 Answer

Anonymous

  • Level 1:

    An expert who has achieved level 1.

    Corporal:

    An expert that hasĀ over 10 points.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 10 Answers

One way of finding (and removing) duplicate entries is to sort the column and put a simple formulate in a temporary column next to that column; for example - if column A has duplicates, insert a column (B) and starting in B2 put if(A2=A1,"DUP",""). Select B2 and scroll down to the bottom of your spreadsheet. Press <ctrl>-D to extend the formula in B2. Wherever there is a duplicate you'll see "DUP" in column B. If you want to remove the duplicates copy column B and Edit / Paste Special... with "values" selected (to wipe out the formula). You can then sort the spreadsheet on column B and remove rows with DUP in column B.

If you can't delete the duplicate rows and the order is important first include a column that captures the order - same trick except put row() in that column, copy / paste special the values and then you can re-sort after doing the above to have both the DUPs marked and the original order.

Hope that helps.

Posted on Aug 11, 2008

Ad

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Cell freeze 3 rows together at a time.


Freeze a Row in Microsoft Excel
Microsoft Excel 2010 can freeze, or lock, a top row as you scroll down the worksheet.
For example, you may need to keep the top row of column titles visible at all times.
The "View" tab on the command ribbon contains the "Freeze Panes" button in the "Window" group.
A single row or a range of rows can lock through the "Freeze Top Row" or "Freeze Panes" options.

Open the Excel worksheet.
Click the top row heading.
The row heading displays a number just left of the first column of cells. The selected row appears shaded.


Click the "View" tab on the command ribbon.
Click the "Freeze Panes" button in the "Window" group.
A list of options appears.

Click the "Freeze Top Row" option.
A black horizontal line appears on the worksheet.
This line indicates the locked row that stays on the screen as you scroll down the worksheet.

http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP010342542.aspx?CTT=1
Freeze or lock rows and columns
also
Use Freeze Panes in Excel
Scrolling down to look at a number and then scrolling up to make sure the number you looked at is under the header you expected is not an efficient way to view a spreadsheet.
The Freeze Panes feature of Excel allows you to freeze the labels of your data in place while you review the data.
Follow the instructions in Section 1 to freeze the top row or the left column.
Freeze multiple rows, multiple columns, or rows and columns, by following the instructions in Section 2.Freeze the Top Row or Left Column
1
Open the Excel spreadsheet.
2
Navigate to the "View" tab on the top menu.


3 Click on "View," then click on "Freeze Panes." A drop-down menu opens.

4

Select the "Freeze Top Row" option to freeze the top row.

5

Select the "Freeze Left Column" or "Freeze First Column" option to freeze the left column.

6

Freeze the top row by using the keyboard and sequentially pressing the keys "ALT, W, F, R." Ignore Steps 3 through 7 if using this choice.

7

Freeze the left column using the keyboard by sequentially pressing the keys "ALT, W, F, C." Ignore Steps 3 through 7 if using this choice.

8

Unfreeze panes by repeating Steps 3 through 5 and selecting "Unfreeze Panes" or sequentially press the keys "ALT, W, F, F."

Freeze Rows and Columns, Multiple Rows, Multiple Columns, or Multiple Rows and Columns
9

Open the Excel spreadsheet.

10

Freeze column(s) and row(s) at the same time by selecting the cell to the right of and below the location you want to freeze.

11

Freeze multiple rows only by selecting the cell in the left (first) column below the rows you want to freeze.

12

Freeze multiple columns only by selecting the cell in the top row to the right of the columns you want to freeze.

13

Navigate to the "View" tab on the top menu.

14

Click on "View," then click on "Freeze Panes." A drop-down menu opens.

15

Select the "Freeze Panes" option. You have now frozen the columns or rows, or columns and rows you designated.

16

Freeze panes using the keyboard by sequentially pressing the keys, "ALT, W, F, F." Ignore Steps 5 through 8 if using this choice.

17

Unfreeze panes by repeating Steps 5 through 7 and selecting "Unfreeze Panes" or sequentially press the keys, "ALT, W, F, F."



http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP001217048.aspx
Freeze or lock rows and columns
http://office.microsoft.com/en-us/excel-help/demo-hide-or-unhide-rows-and-columns-HA010241040.aspx
Hide or show rows and columns

Aug 14, 2013 | Microsoft Office Computers & Internet

Tip

Deleting Rows & Columns from the table


You can also remove columns or rows from the table. Once a row or column is deleted, it can be undeleted by using Undo command. You can delete the columns or rows or cells by using one of the following ways.
By Table Menu
To delete a row or column by Table menu, follow these steps.
  • Place the insertion point in the column or row that is to be deleted.
  • Click Table menu and then select "Delete" from Table menu, a submenu of Delete is displayed.
  • Select Columns or Rows or Cells command to delete the selected element (column or row or cell) from the table.
By Popup Menu
To delete a row or column by popup menu, follow these steps.
  • Select the column's you want to delete.
  • Right click the mouse, a popup menu is displayed.
  • Select "Delete Columns" command, the selected columns will be deleted.
OR
  • Place the insertion point in the column or row that is to be deleted.
  • Right click the mouse, a popup menu is displayed.
  • Select "Delete Cells" command, "Delete Cells" dialog box is appeared.
  • Select "Delete entire row" to delete a row or select "Delete entire column" to delete a column etc. from the dialog box and click "Ok" button.

on Jan 29, 2010 | Computers & Internet

1 Answer

If I have a multi-row s/s that has multiple pages, how do I get the row title for columns to appear on each page?


Hi, I believe you're asking about 'freezing panes'...

In Microsoft Excel 2007:
  1. Select the place on the spreadsheet where you want the row titles to appear
  2. Click on the View tab
  3. Click on Freeze Panes button
  4. Select Freeze Panes
Or, you could be asking about defining print rows to appear at the top of each page:

  1. Select the Page Layout tab
  2. Click Print Titles
  3. Enter the Rows to Repeat at Top (e.g., 1:3 for the first 3 rows)
Hope that helps!

Feb 01, 2011 | Microsoft Excel for PC

1 Answer

HOW TO PLAY SUDOKU ? WHAT ARE THE RULES ?


"The objective is to fill a 9×9 grid with digits so that each column, each row, and each of the nine 3×3 sub-grids that compose the grid (also called "boxes", "blocks", "regions", or "sub-squares") contains all of the digits from 1 to 9".

Wikipedia link.

Compare the first two photos on Wikipedia page and note how the remaining boxes are filled. The rule is that no number should be repeated in a single row or column.

Aug 08, 2010 | Computers & Internet

1 Answer

How to get all balance sheet entries tally to excel


This is a very handy process when you're totaling or subtotaling columns. On the cell that you want the 'total' in type '=sum(column letter row number),(column letter row number). The first 'column letter row number' is where you want the first cell to be started in the total factor and the second 'column letter row number) is the last cell you want added in the total factor. The help (?) section is good at explaining formulas. Hope this helps, keep this process handy if you use Excel much because it'll be helpfull each time you subtotal or total columns.
Bob

Sep 23, 2009 | Computers & Internet

2 Answers

Ranking values based on the number of times they appear/repeat


=SUM(IF((Ax:Ay="xxxxx"),1,0) Ax=starting cell Ay=ending cell "xxxxx"=text"

Jun 08, 2009 | Microsoft Excel for PC

1 Answer

Works 8 Word Process set Repeating Page Column Headers


Choosing the single cell below rows you want to repeat on each page and to the right of the column you want to repeat and then freezing that cell will cause those rows and that column to repeat on each page as you print. works fine on preview. However I still can't get the machine to print.

Ralph R. McKibben.

Mar 21, 2009 | Microsoft Works 8.0 for PC

1 Answer

Input data


If you want to transfer your data into SAS, SPSS, or some other program, follow these guidelines:
The cells in Row 1 should contain the column's eventual data set name. Each name should be a relatively short and unique acronym that clearly identifies the data. It should begin with a letter and contain only letters, numbers, or an underscore ( _ ) where spaces would naturally fall. Avoid using special characters such as $, &, @, in variable names. Since each row represents the values from one subject, the first column(s) should contain one or more variables that give each subject a unique identifier. They become especially important if you need to merge two or more data files.
In Excel, data formats are defined for a range of cells rather than for a complete column. For this reason it is important that each entire column, including cells with missing or uncollected data, have one, and only one, format. Actually, you do not need to format the entire column, only the portion you will eventually use. Highlight that portion and select the appropriate format from the Format/Cells option. Do not select formats that will enter commas, dollar signs, or other visual enhancements. Numeric, text, and date formats (e.g. mm/dd/yy is often a good choice) are probably the only formats you'll ever need.
The "Split" option (under the "Window" pull-down menu) keeps the row of variable names and the columns of identifiers in view, whatever range of cells in the worksheet you may need to review. First place the cursor at the most extreme upper left-hand corner where data entry begins (e.g., the intersection of Row 2 and the column in the upper left-hand corner where data appear) and then select "Split" from this menu. For any row or column of the worksheet you move to, you'll know exactly which variables you are observing (column names) and their associated ID values (rows).
For versions of Excel later than 4.0, one file can contain multiple worksheets. By default, the tabs at the bottom of these sheets are supplied names ("sheet1," "sheet2," etc.). You can change these names by clicking this space with your mouse and entering a new name. Use the same conventions for first-row variable names: use a short acronym of the page contents that begins with a letter, use only letters or numbers, and enter the underscore ( _ ) where a space naturally falls.

Jan 05, 2009 | Sage Instant Accounts 8.0 (013604ug)

1 Answer

Excel formula


Assuming that all of your data is in a single row number 4 and between columns N and PF

Try:
{=OFFSET(N4,0,MATCH(TODAY(),N4:PF4,0)+1,1,1)}

The MATCH function looks up the value of today() in the range N4 to PF4 and returns the number of columns offset from the beginning of the range. (The 0 here does an exact match)

The OFFSET function returns a value from a cell a specified number of columns from a reference cell, in this case N4, which is the first column that contains the search data. We need to add on to this value to skip the Interest column.

Regards,
Daryl

Jan 25, 2008 | Computers & Internet

2 Answers

Duplicacy in excel sheet


Since you are searching the data by the phone number , first select all the data in the spreadsheet and sort it in ascending order by the phone number.
Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data
Select the entire data and do an auto filter
In column F filter the data by Duplicate and delete all these rows
What remains should be unique data

Dec 19, 2007 | Computers & Internet

Not finding what you are looking for?
Computers & Internet Logo

Related Topics:

155 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

Ekse

Level 3 Expert

13434 Answers

Lee Hodgson
Lee Hodgson

Level 3 Expert

4810 Answers

efs_perpends
efs_perpends

Level 3 Expert

1997 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...