Question about Business & Productivity Software

1 Answer

Excel functions In excel, I have row A labled 1 to 10 ending at A10. Further down in my spreedsheet I have 5 rows (J - N) of numbers consisting of 5 varible numbers from 1 - 10, never repeating in each row. I want to write a fromula in rows B - F to take the data from rows J - N, and place it in the proper colums labled 1 - 10. i.e If row J has the numbers 1,3,4,8,9 these numbers should appear in B1, B3, B4, B8, & B9. If row K is numbered 2,5,6,8,9 they should appear in C2, C5, C6, C8, C9. I thought I had it with the IF function, I was close, but not close enough.

Posted by on

1 Answer

  • Level 1:

    An expert who has achieved level 1.


    An expert that hasĀ over 10 points.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 16 Answers
Re: excel functions

Here's one way to do it. Your formulas would look like this:

=IF(OR(J1=1, J2=1, J3=1, J4=1, J5=1),1,0)

=IF(OR(J1=2, J2=2, J3=2, J4=2, J5=2),2,0)

=IF(OR(J1=3, J2=3, J3=3, J4=3, J5=3),3,0)


If you don't want the zeroes to display, format the cells with a custom number format--type: 0;-0;;@

Posted on Nov 15, 2007

Add Your Answer

0 characters

Uploading: 0%


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


3 Points

Related Questions:


How to find no. of rows and columns in Worksheet.

Hello everybody, this would be my first tip on Number of people might not be aware how many rows and columns are there in Microsoft Worksheet.
This is how you can find out.
1. Select A1 cell in the worksheet
2. Now press Ctrl + down arrow from your keyboard, that will take you to the bottom of the row. You can find the number on the left side.
3. Again select A1 cell in the worksheet and press Ctrl + left arrow from your keyboard, that will take you to the last column of the worksheet. Now to number, just type "=column() " , without quotations, that will give you the number of the column.
Microsoft Worksheet columns is number from A to Z, again from AA to AZ, again from BA to BZ and so on till it reached IV in Excell 2003 and earlier version.
Microsoft Excel 2003 and old version has 16,777,216 cells per worksheet (65,536 rows * 256 columns).
Excel 2007 has 17,179,869,184 cells per worksheet (1,048,576 rows * 16,384 columns).

on Jul 27, 2010 | Microsoft Excel for PC

1 Answer

How to delet empty rows in excel

I am assuming you are using Office version 2003 or newer.
Towards the left border of the sheet there are row numbers; Left click on any number selects the entire row.
Once selected, right click on the row number or anywhere on the selected row and delete from the menu.

To select multiple rows:
1. Hold down the left mouse button and drag till you reach the desired end row. (row selection to be done by clicking on row numbers and not the actual rows)
2. Select a cell (left click), then the combination ctrl+shift+rightArrow. This will select the entire row (only if it is empty). Now use shift+downArrow to select one row at a time or ctrl+shift+downArrow to select all adjacent blank/non-blank rows.

If row number are not visible:
In Office 2007 goto "View" tab. Select "Headings" from the "Show/Hide" group. In other Office versions, use the menu navigation to reach this point.

Jul 11, 2014 | Microsoft Office Business & Productivity...

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.
Freeze or lock rows and columns
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
Open the Excel spreadsheet.
Navigate to the "View" tab on the top menu.

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


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


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


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.


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.


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

Open the Excel spreadsheet.


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.


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


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


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


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


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


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


Unfreeze panes by repeating Steps 5 through 7 and selecting "Unfreeze Panes" or sequentially press the keys, "ALT, W, F, F."
Freeze or lock rows and columns
Hide or show rows and columns

Aug 14, 2013 | Microsoft Office Business & Productivity...

1 Answer

What is the meaning of rows?

In Microsoft Excel, "rows" refer to data cells grouped together horizontally across a single line. The numbers on the far left of the screen refer to a row number. Similarly, the letters at the top of the workbook page refer to the columns.

Here is a picture to help:

In this example, there are 3 rows. All the cells in Row 1 contain the words "Row 1" and are color-coded orange. Likewise, All the cells in Row 2 are pink, and those in Row 3 are blue. Do note, however, that even though the boxes under the letter "F" aren't color-coded, they are still in the respective rows. Each color-coded line could extend for an infinite number of boxes, and each box of the same color would be part of that row.

If you find this solution helpful, please leave a positive rating!

Jun 22, 2011 | Microsoft Office Excel 2007 Full Version...

2 Answers

How to number a column. I can't remember the short formula, something like a1+1= which would make the number two pop up in the cell underneath.

You can number rows in a column by entering a number in cell A1 (usually the number 1 but youcan start with any number) and the formula (=A1+1) in the next row. The result there will be 2. Copy that formula down the rows you want to number and they will be numbered 3, 4, 5, etc. Each row adds 1 to the previous row so if you do anything that disrupts the sequence (like inserting a row between two others) you will have to copy the formulas down again to restore the sequence. You can also use the Edit-Fill-... menu command to put a series of numbers into rows. Put the starting number in th efirst row. Highlight it and the rows that you want to number and select Edit-Fill-Series... Those numbers will not change if you insert columns or move the formulas.
Or you can use the formula =ROW(A1) in any cell to return the number of that row. (The result of =ROW(A1) is the number 1 in cell A1, the result of =ROW(A2) is the number 2 in cell B2, etc. In this case inserting rows will not affect the numbering (i.e. row A5 will always be numbered 5 even if the data in it is moved down.)

Sep 18, 2009 | Microsoft Excel for PC

1 Answer

I have a year planner in excel in which first row consist of month jan,feb.... and in the second row days are there like 1, 2,3..... and on the very first column name of employees is there so my task is to...

There is always multiple solutions, but here is one...

Add a row (that can be hidden later if you want) where you have the full date (01/01/09). For this example it will be row "C." Then use Conditional Formatting with the function:


See if this helps.

Jan 03, 2009 | Microsoft Excel for PC

1 Answer


lookup value = value searched

table array = database

topmost row of lookup array must contain the data IDs and all IDs must be sorted in ascending order.

row index number = row number containing data to be shown; first row = 1

hlookup(x,tablearray,y) will look for x on the first row of the lookup table and return the value in the cell on the yth row

if formula cannot file exact x, it will look for the value closest to. but not greater than x

Nov 13, 2008 | Business & Productivity Software

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 | Business & Productivity Software

2 Answers

M.S. Excel , vlookup formulla tell me use

I love vlookup!

Suppose you have 1 worksheet with song numbers and titles in Row 1, Cols A:B:

Song# Title
123 Love Me Tender
234 Blue Suede Shoes
345 Dixie

Another worksheet has song number and performer in Row 1, Cols A:B

Song# Performer
123 Elvis Presley
234 Carl Perkins
456 Cher

Notice there is NO performer for song number 345 in the 2nd worksheet.

Now in the 1st work sheet, cell C2 insert this LOOKUP function: =LOOKUP(A2,Sheet2!A:B)

Copy that cell to row 3 and row 4 in Col C. You should get a Performer for all songs even though there is not a song number 345 in the performer worksheet.

Help me out Mr. VLOOKUP.

Insert this VLOOKUP function in cell C2 of the first worksheet: =VLOOKUP(A2,Sheet2!A:B,2,0)

Copy that cell to row 3 and row 4 Col C. You should get the performer names for the 1st 2 songs, but not for 345 Dixie. The result should be #N/A.

That means VLOOKUP could not find a DIRECT match for song 345 in the second worksheet.

That is why I prefer VLOOKUP over LOOKUP.

I have found this explaination of the VLOOKUP parameters helpful:

1. Needle (A2)
2. Haystack (Sheet2!A:B)
3. RELATIVE Col containing result (2)

Hope this helps.

Oct 10, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Business & Productivity Software Logo

Related Topics:

140 people viewed this question

Ask a Question

Usually answered in minutes!

Top Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers


Level 3 Expert

18257 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides