Forum Discussion
Solved
IF
I am trying to write a formula that would look something like: =IF(B2=B1, Copy C2, None) This formula would be in cell C1. If B2 matches B1 then copy the value from C2 to C1, otherwise do nothi...
- it could be a number of things but basically the text in B33 and B34 aren't EXACTLY the same. It could be an extra space, it could be a non-displayed control character, or something else.
This is one of many reasons why data validation for values like this are so important (i.e. only allow values from a list of valid entries).
How are the values in column B entered? Is this just a 1 off or is this part of a very long list with many similar issues? Why are you even repeating cell information like this anyhow?
so the formula would be =IF(B2=B1,C2,"") BUT if by "do nothing" you are hoping you can have a value already IN C1 and have that shown, you can NOT a cell can have EITHER a fixed value OR a formula that will dictate the value to be shown. in that case you enter the "do nothing" value in the second part of the IF statement like so: =IF(B2=B1,C2, [enter other value here] )
mtarler Thanks for your reply. Strangely this is working for some but not all cells. Below is a sample where the A & S Electric (B Column) match triggered the C column match. But it didn't work for A Capital Electric, Inc. Any idea why?
| A & S Electric Inc. | 16990 Other Electrical, 16951 Landscape/Outdoor Lighting, 16950 Lighting Package, 16800 Fire Alarm, 16700 Security/Tel/Data Systems, 16050 Electrical |
| A & S Electric Inc. | 16990 Other Electrical, 16951 Landscape/Outdoor Lighting, 16950 Lighting Package, 16800 Fire Alarm, 16700 Security/Tel/Data Systems, 16050 Electrical |
| A Capital Electric, Inc. | =IF(B33=B34,Q34) |
| A Capital Electric, Inc. | 16990 Other Electrical, 16951 Landscape/Outdoor Lighting, 16950 Lighting Package, 16800 Fire Alarm, 16700 Security/Tel/Data Systems, 16050 Electrical |
- it could be a number of things but basically the text in B33 and B34 aren't EXACTLY the same. It could be an extra space, it could be a non-displayed control character, or something else.
This is one of many reasons why data validation for values like this are so important (i.e. only allow values from a list of valid entries).
How are the values in column B entered? Is this just a 1 off or is this part of a very long list with many similar issues? Why are you even repeating cell information like this anyhow?- That B33 and B34 aren't exactly the same is the reason I thought of too. But the values were created by copying from one sheet to the next so they should be the same. You mention "non-displayed control characters" with which I am unfamiliar, but maybe that's it. As a test I tried copying B33 to B34 to ensure an exact match and then running the formula but it still won't work.
As you can tell, I'm not much of an Excel pro. What I am really trying to do is copy the values in column b from one spread sheet to another where column a is similar on both spread sheets but has MANY more values on the second sheet. There is not a column b value for every column a value. So there isn't a way that I can figure to sort column a on the second sheet to match column a on the first which would set up a simple copy paste of the column b values. My solution was to copy columns a and b from the first to the second sheet then sort the rows by alphabetical order of column a which would at least get the similar column a values next to one another. Then I can run this formula where I look for two matching values in column a (say on rows 5 and 6) and when one of them has has a value in column b (say row 6) it gets copied to the other row in column b (say row 5).
I have now found that I can take one successful formula and copy it down with cntrl-d with 100% success. Strange that entering the formula directly into some cells won't work, but the cntrl-d copy works without a hitch.
Anyway, thanks again for your help.- what it really sounds like is you need to do a VLOOKUP or XLOOKUP and if you are using Excel 365 then you have dynamic arrays so you don't even need to copy down you can just do it using 1 formula. Something like:
=VLOOKUP($A$1:$A$100, othersheet!$A$1:$B$10000, 2, FALSE)