Excel Formula If, else, Not Equal

[noecr26]

Hello Excel Forum,

I am new to the forum and basically new to Excel formulas outside of the basic sum and average functions I used in university. I do have a background in Computer Science and programming and I think my logic is alright though, but I cannot get the formula to output.

I have to columns of data. They are F2 and J2. I am basically trying to get a list of transactions from J2 forward. Where F2 is the current transaction. So if F2 does not equal J2 then the formula should output F2 into J2. If F2 equals J2, then move on and output J2 instead.


I have tried the following:

If(F2<>J2, F2,J2) and I get circular reference
I have tried

If(Not(F2=J2),F2,J2) and I get a circular reference

The only thing I think that might cause issue is that I changed cells formatting to Currency. Would that an effect on the formula?


Thanks, Sorry if this has been asked in different area or formatting sucks.

[alansidman]

In order for what you are trying to do, you will need to use a VBA solution. Excel cannot have a formula in a cell that it references, hence the circular reference. So it you provide a sample worksheet with some data, I am sure that we can supply you with a workable VBA solution.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Saarang84]

Hi,

Welcome to the forum.

Can u post a workbook with sample data? Also, would like to know what is the data type for the contents in column F and J.

[noecr26]

Hi guys/gals

Thanks for the quick response. I avoided adding my data because I did not want to blatantly ask for code or overwhelm the forum and put a bad taste in anyone's mouth.

Basically in response to the moderator. I have 10 columns. After each transaction I want to add the transaction total from Column F(Current Purchase) to the correct Transaction Column. So if it is the first transaction it would go into column for transaction 1. Second current purchase entry goes into Transaction 2 column and so on and so forth.

I have added the workbooks to this post. The type for F2 and J2 are currency if that is what you are asking.

Thanks for the warm welcomes and help.

[noecr26]

So I tried to build some VBA code. Maybe you guys could take a look at it?

Please Login or Register  to view this content.

I get a runtime error 1004: application-defined or object defined error

Any thoughts?

[alansidman]

Which line of code is highlighted when you click on debug when you get the error?

[noecr26]

So I fixed some code. After some work and fiddling and some help from stackoverflow I have two options:

Please Login or Register  to view this content.

And I get runtime error 13 at this line:

Please Login or Register  to view this content.

If this does not work I will post the other code that was recommended that works, but does not do exactly as I anticipated.

Thanks for the help

[duncandhu]

Hi,

This line:

Please Login or Register  to view this content.

seems wrong... I think when you have to specify ranges you can either have:
J:J or J1:J10 - You're trying to define a range using incorrect syntax.

Try putting:

Please Login or Register  to view this content.