Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Basically, conditional formatting in Microsoft Excel simply means that "a certain format" will be applied or NOT applied based on a certain condition. This "format" is referring to cell properties like font color, background, style and others.

For instance, if the sum of cell A1 and A2 is greater than 1000, A3 where the SUM is shown, will be rendered as bold, italics and color red. So, if the sum is less than the set value (1000), nothing will happen, just display the sum in normal text since the "condition" is not met.

Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

What exactly is your scenario? Is it something like:

If Col A matches with Col B, THEN? Do you want to show something in Col C like "match"? Please spcify your requirements so that I can help you further.

well....column A has codes, Column B describes what these codes are, Column C are other codes some identical to those in column A. I want column D to have the description of the codes that are already known based on Column A and B. Does this make sense? I don't want to have to manually do it as there are hundreds........

That is quite complicated and I doubt if there is a better way to do that than manual

Col A = codes Col B = code description Col C = other codes Col D = code description

Now with Col D, how do you intent to ask Excel to populate it? Is it something like it will look for the code in WHICH column and copy description from Col B?

i want excel to look at column A to see if the code in Column A matches the code in Column C then the associated description in Column B to be in column A. I am sure there is a way, but it is complicated...

Are you saying that "then the associated description in Column B to be in column A"? Is it Column A or Column D? You cannot change the values in column A where the comparison is done.

You want excel to look at column A to see if the code in Column A matches the code in Column C then the associated description in Column B to be copied in column D.

Is this correct?

Also, are you using Excel 2007 o7 2010? One more thing, can you upload a dummy file for me to work on? Like just a single page with col A, B, C and D so that I can work from there? Just a dummy of your actual file if it is possible.

Do you have a concrete list of codes in the first column? For instance, all the KNOWN codes are already there in the first column so that we can use VLOOKUP function instead.

you are taking way too long. if you don't know how to do it just say so, but keeping me suspended like this is not that nice. i need a quicker answer as it is for something urgent.

Customer:replied 4 years ago.

Relist: Answer came too late. I need a quicker answer

Have you tried VLOOKUP? In this specific requirements of yours requires a VLOOKUP function in order to automatically enter the description if there is a "match" with the code.

However, if there is no match, NA is entered to the cell and you can then manually enter the description if that description is NOT in the table of descriptions.

Now, in that file, please note that I have placed a sample lookup table. Enter the code and see how the sample description changes as you enter the correct code. If the code is NOT in the table, NA is displayed.

Now, regarding the formula or how to do the vlookup, please do this:

Click on cell E2

Click on the FX button in the formula bar to bring up functions list

Then enter the data as see in this screen:

The formula used here is =VLOOKUP(C2,A2:B28,2,FALSE)

Please make sure that in the formula is made ABSOLUTE before you drag it to copy to the other cells.

To make the cell reference ABSOLUTE, please insert $ around the cell reference and the formula shall become:

=VLOOKUP(C2,$A$2:$B$28,2,FALSE)

Now drag the cell handle to copy the formula and you are done.

Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

I immediately see a problem with your excel file. With vlookup, the lookup table SHALL NOT contain duplicate entry. Can you avoid duplicates? For instance, 1 are recorded two times. It will not work because Excel has no way of telling which label to show when the code 1 is seen or entered. Will it be "Cholera" or "Due to Vibrio cholerae"? I hope that explains.

Also, your initial attempt did not make it because you use the range A:B when in fact column B is hidden and what you really want to show up are the label that is in column C. Thus, I adjusted the range to A:C.

Here is the corrected file. Please take note that entries with duplicates, what is automatically displayed is the first occurrence of the entry. Please note also that those CODES that are NOT SEEN in the lookup table are automatically marked as #N/A. It simply means "Value not available".

Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

Thanks a lot, I look forward to give you plenty of excellent feedback. Let me know if you think I should post this as a new question, but could you tell me:

1. how to add 1 zero before every two digit number in the column and 2 zeroes for every 1 digit number. The minimum no of digits should be three. Not also there are some values with a preceding letter. Nevertheless, I also want to fix those numbers in this way.

2. Once I do this, I want like to add a decimal point to each number (or letter-number) in a way that it is the decimal is in the second place, e.g.

100 to 10.0 and 010 to 01.0

Attached is the column in question. I will rate all aforementioned answers seperately so don't worry.

Yes, exactly. i was able to add the zeroes, but it is the decimals that is difficult. Eventually, I will remove the decimals, but before that I have to put so that the numbers can end up in the format that is appropriate.

For Coolumn B, I just made a direct copy from Col A. That is, in B2, I made =A2 formula and copying the formulas all the way down. Regarding the padding of initial zeros, you just need to go to select B2 which contains 10, then click on the small down arrow in the Number group in the Home menu. In the left panel named Category, click on Custom then click on the 3 zeros. That will make your cell value fit into 3 digits MINIMUM and pad zeros for 2-digit entries. After doing that and if B2 now shows 010, drag the cell/fill handle to copy the format to the rest of the cells in Column B. Here is my screen shot:

Now regarding the decimals, here is the trick. In Column C, you just need to copy or reference the value B and divide it by 10 so that it will move the decimal one digit to the left. So in C2, enter =B2/10.

Now, copy the formulas all the way down column C. For columns with 2 digits, the initial zeros are removed. So you need to select C2 thenclick the small down arrow in the Numbner group. Click Custom in the Category then select the option 00.0. That will pad zero for 2digit cell values. Here is my screen shot:

Please be sure to rate my answer with 3 or more stars/faces if this helped. Keep me posted with your results so that I can help you further. If you have further question, please reply to me so that I can help you further.

this is great, but why when ever I copy and paste the column it starts to give me two decimals? may be the reason is i don't understand the program. could you explain it to me...

There are two columns with formulas which are dependent on each other, so you have to copy both and know where to paste them. If you are not sure I suggest keeping the formulas where they are and paste the new data in column A instead

Hope this makes it clear and brings this issue to conclusion

I rated you excellent because you did what I asked you, though for me, it has not solved the problem since the programmers at my workplace have given me wrong information. Looks like we'll have to figure this out on our own, for now.

Could you tell me: in this series of right justified numbers, where would the decimal place go?

12345

1234

123

12

1

Please note, as before, if the numbers are two we add one leading zero and if one, we add two leading zeroes.

Ok I checked your last message but you need to give me the requirement so I can give you the solution. Specifically please show me how you need to have the above 5 codes formatted and I will be happy to provide the formulas which you will be able to apply to all the list. If in doubt I suggest that you ask the proponent or your workplace how they want these 5 codes formatted to make sure that the solution will per their advice