Conversion from Numeric to String in Excel

[prabhu281276]

Hi

I have couple of questions to be asked which i require your valuable help and support.

I have been using excel for the past 1 year or so and i have found various methods to improve on it. But i still find there are various challenges and tricks involved in it to solve a particular problem

I have a data here given below
Date Op Amt TC2 Amt Diff
1/9/2006 150 100 50.00
5/9/2006 142.14 198.25 -56.11
7/10/2006 158.25 160.25 -2.00
8/10/2006 258.14 250.25 7.89
8/10/2006 174.12 160.14 13.98
27/10/2006147.25 158.25 -11.00
15/1/2007 250 200 50.00
1/11/2006 258.23 258.22 0.01
15/1/2007 147.14 158.23 -11.09
My question is in the diff column i have figures ending with .00. When i place filter in this field and press custom and say ending with .00 i must get the result ending with .00. I have previously asked the same type of question but due to data corruption and other issues i was not able to keep a backup of this problem. So please help me.

The second question is the most important one.
Date Op Amt TC2 Amt Diff Comments Worked by
1/9/2006 150 100 50.00 cleared ABC
5/9/2006 142.14 198.25 -56.11 queried XYZ
7/10/2006 158.25 160.25 -2.00 cleared ABC
8/10/2006 258.14 250.25 7.89 cleared ABC
8/10/2006 174.12 160.14 13.98 cleared ABC
27/10/2006147.25 158.25 -11.00 cleared XYZ
15/1/2007 250 200 50.00 cleared XYZ
1/11/2006 258.23 258.22 0.01 cleared XYZ
15/1/2007 147.14 158.23 -11.09 queried XYZ
I am working in the second sheet of the excel workbook. In the first sheet i am entering the persons who are presently working in this workbook. This file is shared. I want to know how many datas that ABC has cleared and how many he has queried and i also want to know the total count that ABC has worked on and how many is pending. I also have a separate files called a Daily Tracker and Individual control log in a separate path in which whatever data is worked upon today is entered by the respective heads. Instead of manually entering the data is it possible to automate it via macros or through formulae.

Please help me

Regards

S.V.Prabhakar

 

[ravishankar]

your first question is not clear

countif worksheet function can help you
=countif(C2:C20, "cleared by ABC") will give you that info. modify the formula to suit your needs.

 

[prabhu281276]

Conversion from Numeric to String

Hi

Date Op Amt TC2 Amt Diff
1/9/2006 150 100 50.00
5/9/2006 142.14 198.25 -56.11
7/10/2006 158.25 160.25 -2.00
8/10/2006 258.14 250.25 7.89
8/10/2006 174.12 160.14 13.98
27/10/2006147.25 158.25 -11.00
15/1/2007 250 200 50.00
1/11/2006 258.23 258.22 0.01
15/1/2007 147.14 158.23 -11.09

In the above data i have columns like date, op amt, tc2 amt and diff. The difference between the op amt and tc2 amt is the diff. Now if i place a filter in the diff column and go to custom and say give me the amounts ending with .00, the answers it returns is blank. To get the result one of my friends told me that you have to convert numeric to string and vice versa to get the result. I want to know what is the function i should use to get the result.

The second question which i also posted in the same letter has got me half of the result. I did not get the result for the remaining. I also want the result for the same also which will help me to do the work faster and quicker.

Regards

S.V.Prabhakar

 

[Dufus]

prabhu281276,

RE: First question

Can you add another column with the formula =IF(MOD(A2,1)=0,1,0) and filter on this column for "equals 1"?

Dufus