Question about Business & Productivity Software

1 Answer

Formulae problem I am busy creating a sheet which I use to import figures, sort them and then transfer them to a single column which I copy and paste to another file. My problem arises due to the fact that some positions are blank and when the info is tranfered the correcsponding amount for those positions don't come up as "0"s. This is the formulae that I am using to transfer 2 columns of info across to another sheet which should fill in the missing numbers but doesn't show them as "0" =LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B) Can you help me?

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
  • 17 Answers
Re: Formulae problem

The problem doesn't say this explicitly, but it looks like you're using Excel. This answer depends on that assumption being correct.

Are you saying that some of your inputs are blank? And LOOKUP fails to map them to 0? Or are you saying that some of your values come back from LOOKUP as blanks? (They shouldn't; lookup should return either a value from the lookup table or an error.) Or is it the case that some input values get mapped to blank by your LOOKUP function?

Assuming that you're getting blanks back from LOOKUP where you wanted zeros -- for whatever reason -- here is something you can try.

It looks like the value you're looking up is in cell A3. Let's say your formula (the result of the LOOKUP) is in B3. In C3, you can put the formula =IF(ISBLANK(A3), 0, A3). This function tests if A3 is blank. If it is, it returns a 0. If it's not, you get A3 again. (But A3 has to really be blank -- spaces will count as non-blank.)

Then, you can copy data out of column C instead of column B, and it should have zeros in place of blanks.

Posted on Jan 06, 2008

Add Your Answer

0 characters

Uploading: 0%


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


3 Points

Related Questions:

2 Answers

How do you copy and paste one excel spreadsheet to another making sure the headers are lined up correctly.

right-click the sheet you want to copy, select "copy to", then either select the position within the current document, select annother document (needs to be open in the same excel instance) or select "new document". check the box "copy" to copy, leave unchecked to move.

Jan 01, 2016 | Microsoft Excel Business & Productivity...

1 Answer

I have a column containing a value of a single letter. I need the spreadsheet to sort add rows by letter.

Move the column to A. Mark entire sheet and sort ascending. There are more raffined ways to do this, but this is an easy quick fix...

Sep 07, 2014 | Microsoft Excel for PC

1 Answer

How do i import an excel sheet to suretrak

In Excel create Activity IDs and Descriptions and copy those two columns. While in the Activity ID box in Suretrak (with no activities below) select paste. You should be able to at least input the Activity IDs and Descriptions. I haven't played with pasting Durations, Starts, etc. If the Suretrak columns match your Excel columns, you may be able to paste as much info as desired.

Jun 07, 2010 | Primavera SureTrak Project Manager 3.0 for...

1 Answer

Dear Sir, In case there are atleast 80 files or more having same format containing datas in columns in each file with different figures, I want to merge all file in a single sheet in one shot. Kindly...


If the column names and orders are same across files, then you can directly use the MS Excel's import data function, this will do your job.

Alternatively, if you want to do it manually, import each file in separate excel worksheet using data import wizard or simple copy paste of data (in latter case you have to use Text-to-Col feature of excel), and then manually append all figures (copy-paste in one go) to any external excel sheet.

Then finally, export/save as that external sheet to any filename of your choice.

Hope this helps.


Mar 24, 2009 | Microsoft Excel for PC

1 Answer

Excel formula

I'm assuming you'd like to assign a numerical value to cardinal references (1st, 2nd, 3rd, 4th, etc.).

Best way to do this is to create a quick lookup table on a separate sheet of the same .xls document. Down at the bottom of the page, click on Sheet2 and create a quick table where column A has 1st, 2nd, 3rd, etc. and column B has your values.

Now click back to Sheet1 where your data is and in A2 put this formula:
=VLookup(A1, Sheet2!$A$1:$B$x, 2, FALSE)
Note: replace the lower case x in the formula above with the number of the last row of data in your lookup table on Sheet2.

You can copy and paste this formula down the column to calculate your other values.

Hope that helps!

Aug 30, 2008 | Microsoft Excel for PC

1 Answer

About formula

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.

Aug 08, 2008 | Microsoft Business & Productivity Software

1 Answer

Matching data

1. Open up both workbooks.
2. Copy one worksheet from one workbook to another using.
Right click on the tab of one book and Select "Move or Copy"
3. A dialog box will appear..Next to the "To book" select the workbook you want to move it to.
4. Be sure to select the 'create a copy' box in the bottom left of the dialog box.
5. Sort each worksheet by students name. Data..Sort.
6. Copy the scores from one sheet to another.
7. You then you can manipulate the scores and compare anyway you like by using formulas in different columns.

Apr 29, 2008 | Microsoft Excel for PC

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

1 Answer

Excel Sort Across Sheets

Hi Raymond, I have a solution for you. Asuming your 'main' sheet is Sheet1. On Sheet2, in the same line as your data on sheet1 (i.e. A5 on sheet1 is JIM), then on A5 on sheet2, enter a link ( the = ) and enter. copy this formula down. Do the same with Sheet3 linking to Sheet1. When you sort Sheet1, Sheet 2 and 3 will follow. Hope this helps. Regards DeltaC

Sep 11, 2007 | Microsoft Office Standard for PC

1 Answer

Merging Data in 2 Separate Excel Workbooks

If the First Name and Last name are equal to both work sheets, I will just sort them by First Name and Last name and copy the results into a separate work sheet. If the names are different you will have to use Access: This can be easily done with Access and SQL, just import both work books into different tables to access and use the create query wizard, play with the results until you will get the result you want. Access will also solve the duplicate problem easily. Very important: You will have to use join properties: Read help about join properties and understand how this works... Afterward you can export it back to Excel or use Copy/Paste. If you can't use Access and have only Excel: You will have to use Vlookup on various fields until you will get the result you want. Its a hard work but in the end you will get the same result as access. Let me know what happend Daniel

Aug 20, 2007 | Microsoft Office Standard for PC

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

Related Topics:

91 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

18258 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