I'm not the best with technical descriptions but please bear with me.

The task before me:

A large spreadsheet with a column of values that need to be reviewed and a different number input beside them. Unfortunately the number is not simply "value, less 15% though it's close". I will need to have a list of specific "find/replace" commands for my formula.

Example:

3.02

6.65

1.54

3.02

And I need to format it such that it says:

3.02 2.80

6.65 5.60

1.54 1.40

3.02 2.80

My idea was something along the lines of =if(A1=3.02,2.80,=if(A1=6.65,5.60,=if(A1....

Then I'd be able to just paste this formula and drag down the entire spreadsheet.

Unfortunately that didn't work and so I come to you all for help.

Please save me tons of time and figure out how I can make this spreadsheet generate it's own values!

Thanks,

Mike

Your If format is wrong. Should be for 3 possibilities

= If(A1=3.02,2.80, If(A1=6.65,5.60, If(A1=X,Y ,"")))

(I only use google docs spreadsheets and am assuming this all translates to excel)

I would use vlookup. Assuming I'm reading the problem correctly. You have a discreet set of values {x, y}. Somewhere in your sheet there are some x's and you want to fill in the y's.

to do that, make a sheet to hold the lookup table of answers. Fill in the x's and y's on the answers sheet. Create a named range called answers for the table.

Then on your sheet of data that has x values in a1:a, put in b1:

=VLOOKUP(a1, answers, 2, false)

It should fill in the y. You can reuse this formula for each cell in b.

You don't have to put the lookup table on another sheet or use a named range, depends on how formal / robust you want it.

You could also use vlookup and have the mapping table off somewhere to the side.

Here is an outline of a simple VBA program (macro) to do it

Sub test()
Nrow = 20 'set the number of rows to look at
For iRow = 1 To Nrow 'irow is row number
x = Cells(iRow, 1) 'read value in cell A,irow
Select Case x
Case 3.02: Cells(iRow, 2) = 5.6 'enter in col. B if a match
Case 5.2: Cells(iRow, 2) = 2.7
Case 6.5: Cells(iRow, 2) = 10.1
End Select
Next
End Sub

Lol, this is not nearly complicated enough to need a VBA macro.

But it might give insight to OP and others how to use VBA for more complicated problems.

You guys, if this works I'm going to be kicking myself at not having asked you sooner!!

Thanks!!

Hi guys, I'm back with another problem, I'm almost out of the woods but there's some infuriating problem that I can't understand at all.

So, I have the 'VLOOKUP' code solving the above problem now.

Here's how it looks now though, after I paste the VLOOKUP code in Column B:

A B
3.02 2.80
#N/A
6.65 5.60
#N/A
1.54 1.40
#N/A
3.02 #N/A
#N/A

Now as you can see though, there is a problem, where CERTAIN cells, are not being picked up by the vlookup code. They ARE in my vlookup table. And, to use the above example, if I were to "double click" on the 3.02 in Column A, THEN, the vlookup code would suddenly work and read the value 3.02 and fill in the correct number in column B.

However, since I have thousands of entries, I don't want to have to double click every one!!

Any ideas what might be causing this and how I can fix it?

What is your vlookup formula?

You should check whether there is any whitespace in problematic cells. If yes, then use "TRIM(celladdress)" instead "celladdress" in your formulas.

Thanks all for interest but turns out I just had to go to: Data>Text to Columns, and then clicked next 3x and it worked!

You can also check out http://forumserver.twoplustwo.com/34...-excel-874910/