Question about Computers & Internet

# 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

• 2 more comments
• gavster70 Jan 06, 2008

27003
140

27001
#N/A

27004
350

27002
#N/A

27005
65.65

27003
140

27010
623.75

27008
113.5

27013
123

27009
201

27014
495.33

27010
623.75

27011
623.75

27012
623.75As you can left side has missing numbers which transfer across and fill in the blanks (yellow). I need "0"'s but get #N/A and repetition. Hope this helps

• gavster70 Jan 07, 2008

Is there any way i can send this file - I'm sure it will be easier that way?

• gavster70 Jan 07, 2008

Thanks guys I figured it out!

• gavster70 Jan 07, 2008

Sorry for messing around but I am new to all of this so I will start again: I have 2 worksheets named DCT INFO and DCT. In DCT INFO I have 2 columns A has positions (some are missing so it's not consecutive) B has a corresponding dollar amount. I need to copy these 2 columns to worksheet DCT together with all the missing positions, and their corresponding values. The missing positions which are filled in on column A must show a "0" in column B in DCT.

×

Ad

## 1 Answer

• 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
• 17 Answers

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

• drew ivan Jan 06, 2008

LOOKUP returns #N/A when the value you're looking up is less than the smallest number in the lookup table. You can detect this after the fact using the ISERROR function.

Thus, you can follow the advice in the solution above, except use the formula =IF(ISERROR(A3), 0, A3). This will have your original results in column A and your new results in a new column. I like doing it this way, because it's easy to test and make sure everything's working right.

Once you're satisfied that the formulas are working properly, you can combine both formulas into one. Change your formula from this:

=LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B)

to this:

=ISERROR(LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B),0,LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B))

This formula is saying, do the lookup. If it is an error, put 0 in the cell. Otherwise, put the lookup value in the cell.

×

Ad

## 1 Suggested Answer

• 2 Answers

Hi,
A 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
Good luck!

Posted on Jan 02, 2017

Ad

## Add Your Answer

×

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video.

×

Loading...

## 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 Computers & Internet

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

### Please transfer old account,joeybanta1113@hotmail.com,to new account,joeypanetta2011@gmail.com i need all my contactacks! and my pictures,"please help me"!!!! thank joey panetta

Exporting Hotmail ContactsThe first step is to get the information you need out of Hotmail, you can achieve this by doing the following:-
1. Select Contacts from the Hotmail navigation bar.
2. Click On Print View.
3. Highlight the entire contents of the table (including the heading names e.g. Name, E-Mail and Phone as well as all your contacts).
4. Press Ctrl+C to copy the table.
5. Create a new spreadsheet document in Excel.
6. Select Edit and then Paste from the main menu.
7. Sort the data by the E-Mail column.
8. For any rows with a blank E-Mail column copy any e-mail address from the Name column to theE-Mail column.
9. Read this guide from Google on the format of CSV contact files and make any adjustments as may be necaserry.
10. Select File and then Save As... from the menu.
11. Make sure you select a format involving "CSV" for saving the file. (GMail can import csv files)
You should now have a csv file containing all your Hotmail contacts ready for importing into GMail.
Importing Your Hotmail Contacts Into GMailImporting your new contacts file into GMail is really straight forward:-
1. Select Contacts from the GMail left hand menu.
2. Select Import from the 3 options at the top right of your list of contacts.
3. Click Browse to locate your csv file containing your Hotmail contacts.
4. Click Import.

Jun 27, 2011 | Computers & Internet

1 Answer

### In trying to download video from our cam corder (Sony DCR-SR85) to our PC we get the following message 'Document management information can not be located" and we are unable to download the video files....

Different ways to transfer video:
Transfer the video to the computer using one of the following methods:

Use the Import pictures and videos option.
1. In the AutoPlay window, click Import Pictures and videos using Windows .
2. In the Import Pictures and Videos window that appears, click Import Settings .
3. In the Import Settings window, assign the location on the computer where you want to copy the picture and movie files.
• Next to Import images to click the Browse button and then select the desired folder (for example, My Pictures ).
• Next to Import videos to click the Browse button and then select the desired folder (for example, My Videos ).
4. Click the OK button.
5. In the Import Pictures and Videos dialog box, click the OK button.
6. Click the Import button.

• Use the supplied Handycam® Utility application.
1. Start the Handycam Utility application bundled with the Sony® Picture Utility software.
2. In the Handycam Utility window, click to the select the type of files you want to transfer: HD movies , SD movies or Pictures .
3. Click the Import button.

• Copy and paste the video files from the Removable Disk window that represents the camcorder.
1. On the computer desktop, create a new folder.
2. If the Removable Disc window is not showing, open the My Computer window and then click to select the Removable Disk icon.
3. In the Removable Disk window, click to select all the folders and files listed.
4. On the keyboard, press the CTRL+C keys to copy the folders and files.
5. Open the new folder that you created.
6. In the new folder, press the CTRL+V keys on the keyboard to paste the folders and files into the new folder.
7. Rename the new folder.

Oct 21, 2010 | Cameras

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...

Hi,

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.

Thanks.

Mar 24, 2009 | 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 Computers & Internet

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

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

## Open Questions:

#### Related Topics:

93 people viewed this question

## Ask a Question

Usually answered in minutes!

Level 3 Expert

18424 Answers

Level 3 Expert

7733 Answers

Level 3 Expert

14162 Answers

Loading...