Question about Business & Productivity Software

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

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

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

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

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.

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

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!

Terry

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!

Terry

Aug 30, 2008 | Microsoft Excel for PC

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.

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

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

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

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

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

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

Oct 21, 2016 | The Business & Productivity Software

91 people viewed this question

Usually answered in minutes!

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

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

Thanks guys I figured it out!

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.

×