date:20101005

Dear Ankit ,

There is also an add-in called Ultimate add-in which contains a lot of other
features like finding and removing duplicates, transposing cell contents
between two cells etc,  along with text to numbers conversion  feature.
But,unfortunately some features of them are not working.But it is quite an
useful add-in. Anyway thank you very much for your concern.


Regards,


P.VIJAY KUMAR

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Numer To Text

Dear Ankit,

To use the add-in select the number you want to convert into text.then go to
the add-ins in the Menu bar and select the text to numbers button.
Automaticaaly the number you selected turns into text. But, you dont't have
the original number.If you want to have the number and also the converted
text copy the number into another cell and then change it into text. In this
way you can have both the number and also you converted text.Hope this
clarifies your doubt.


Regards,
Vijaykumar

On Tue, Oct 5, 2010 at 11:38 AM, Ankit Agrawal
ankit.agrawal...@gmail.comwrote:

 Thank you sir for deteil knowledge but again same problem *how we can use
 add-in? plz tell me *

 **
 Thnanks  Regards

 Ankit


 On Tue, Oct 5, 2010 at 8:30 AM, P.VIJAYKUMAR vijay.4...@gmail.com wrote:

 Dear Ankit,

 There is no Direct formula to convert numbers into text.But there are some
 add-ins available in the websites. just google for text to numbers converter
 in excel add-in.
 Once you download the add-in to install the add-in  to excel go to the
 excel ribbon Button, the from there open excel options ,then select add-in
 from the avialble options. Excel will then display the add-ins which are
 already installed . It will also have a go button in the bottom for
 installing others in that it will show some available not installed
 add-ins,you can also install them along with our add-in..After clicking go
 button  then click the browse button then select the file which you
 downloaded to any disk or any place in your computer.Then click OK.Your
 add-in is installed.Once installed the file which is selected for
 installation should not be moved from the place it is downloaded.To many
 movements make the add-in not working.So when downloading only make sure
 that the file is saved in a place in which is is not disturbed further.Once
 installed you add-in shall work in every workbook. Make sure that an addin
 button is added to the Menu Bar.Hope this clarified your problem


 Regards,
 VijayKumar

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link

 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


  --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




-- 









P.VIJAY KUMAR

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Help in sending the salary slip

Hello Ankit,

I did not understand you question properly. Why do use mail merge .You can
create  single sheet work book as you salary ship and give password to it
and also mail the password to the concerned person through a text file
attached along with the salary sheet.

Regards,
P.VIJAY KUMAR

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Help in sending the salary slip

2010-10-05 Thread prabhakar jain

you can attach your excel salary sheet with salary slip through vlookup and
copy that slip on mail.
sending you a sample sheet (EMP CODE IS KEY )

WRITE ONLY EMP. CODE THE WHOLE DETAIL U CAN EXTRACT.

On Tue, Oct 5, 2010 at 12:40 PM, P.VIJAYKUMAR vijay.4...@gmail.com wrote:

 Hello Ankit,

 I did not understand you question properly. Why do use mail merge .You can
 create  single sheet work book as you salary ship and give password to it
 and also mail the password to the concerned person through a text file
 attached along with the salary sheet.

 Regards,
 P.VIJAY KUMAR

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




-- 
Prabhakar Jain
Mumbai

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


Salary_SEPT10.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10

2010-10-05 Thread Maries

Dear Mr.Ayush,

You are doing wonderful service here... Thank you so much.
Congratulations for MVP... I'm very happy to here in this group.

Regards,

Maries
On Tue, Oct 5, 2010 at 9:15 AM, SUMIT VYAS svyas0...@gmail.com wrote:

Dear Mr. Ayush Jain

One Word Name You : - *Matrix EXCEL *

Congratulations Ayush Jain. Really Very proud of you man.

Regards

Sumit Vyas

On 10/5/10, Aindril De aind...@gmail.com wrote:

Hey Ayush!! You haven't responded to Neil's email

Where is the Treat Man!!!? [?][?]

Cheers,
Andy

On Mon, Oct 4, 2010 at 9:17 PM, krishna mummina
lovemekris...@gmail.comwrote:

Congratulations Ayush Jain. Really Very proud of you man.

On 10/4/10, rajasekhar praharaju rajasekhar.prahar...@gmail.com wrote:
hearty congratulations ayush, you really deserve it

Infact you are good encyclopedia for us.

Have a flamboyant future and delightful moments for ur future
endeavors.

Regards,
Raj

On Sun, Oct 3, 2010 at 3:47 PM, Ayush jainayus...@gmail.com wrote:

Dear Group,

I am proud to announce that I am awarded Microsoft Most Valuable
Professional MVP on October 01, 2010 for providing technical expertise
in Excel technical communities for past one year.

http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-announced-october-2010.aspx

My deep thanks to each group member for all your support and
contribution to this group.

Best regards,
Ayush Jain
Microsoft MVP

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Right click

Thanks.. Its working

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

RE: $$Excel-Macros$$ Re: Need next number with a twist

2010-10-05 Thread Dave Bonallack


Hi,

For every unique value of col A - C and col F, then the number is increased by 
1 from col G unless there is already a number for that combo in col H, then it 
gets increased by 1 from that number in col H

 

I think I understand the first part of the sentence, but the meaning of the 
second part escapes me.

unless there is already a number for that combo in col H, then it gets 
increased by 1 from that number in col H
 It appears that there is always a number in Col H, but is it a number for 
that combo ? I can't tell, since it is just a number, and not derived by a 
formula.

Regards - Dave.

 

 

 


Date: Mon, 4 Oct 2010 13:47:08 -0700
From: n8dine4ma...@yahoo.com
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
To: excel-macros@googlegroups.com






Does anyone have any thoughts on this?  I've attached the spreadsheet again if 
anyone wants to take a look.  Is there a way maybe to concatenate some cells 
and then write a formula?





From: None n8dine4ma...@yahoo.com
To: excel-macros@googlegroups.com
Sent: Thu, September 30, 2010 2:52:14 PM
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist



Joshua,
 
Thank you so much for your response.  Unfortunately this isn't working.  The 
formula in col I is referencing col H which is where I originally wanted the 
formula.  There will be no data in col H unless I can't get a formula to work 
in there, then it will be manually entered.  Can you help me with how the 
formula should look in cell H2 so that it will return the same numbers I 
manually entered there to show what the formula result should be?  Thank you.
 
Nadine





From: Joshua Leuthauser leu...@gmail.com
To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
Sent: Wed, September 29, 2010 5:32:44 PM
Subject: $$Excel-Macros$$ Re: Need next number with a twist

Build a key in column g.  The formula for the key should be:
=a2b2c2f2

Populate that down for all of the rows.

Basically what I built says -- look at the column of keys, if you
don't find a match then take the max of all transactions used thus far
and increment by one.  If you do find a match, use the same
transaction number that was used by that key (the combination of a-c2
 f2).

You'll notice that it doesn't reserve the next transaction that
should be used, it just assigns the next transaction to whichever key
shows up that is unique.

After you have your column built with the key, here is the formula I
put in I2:
=IF(ISNA(VLOOKUP(G2,G1:H1,2,FALSE)),MAX(H:H)
+1,VLOOKUP(G2,G1:H1,2,FALSE))

Fill that down the remainder of column I and you have a working
formula that will either give the same transaction number if a key
match is found otherwise give you the next available transaction
number.



On Sep 28, 10:16 pm, None n8dine4ma...@yahoo.com wrote:
 Hi there.  I need some help finding the next number but there's a twist. 
 Attached is a file that shows what I need.

 There's a sheet titled Transactions where the data is continually added to 
 and
 the order cannot be changed so sorting the data is out of the question.  The
 next sheet is called Need Formula and this is where I need a formula to be
 entered into cells H2 - H24 for this sample spreadsheet.  The actual file will
 have more rows.

 The formula needs to look at the number in cell G2 and increment it by one
 UNLESS, and here's the trick, there is already a number in this column (H) for
 the combination of cells A2-C2 and F2.  The sample will give you a better idea
 as I've entered the end result in column H already.  I just need a formula 
 that
 will give me the same result.  Thank you so much for your help.

  next numbers.xls
 43KViewDownload

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
 

Like our page on facebook , Just follow below link

Re: $$Excel-Macros$$ Right click

So, what was the problem?
was it a Before_right-click event?

I had suggested to disable the events as a diagnostic to check to see if that 
was the problem.
NOT as a proposed solution!

Because when you exit excel and open it again, the problem will return.


Or was it something else?

paul


- Original Message 
 From: sivam sivamma...@gmail.com
 To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
 Sent: Tue, October 5, 2010 4:22:57 AM
 Subject: Re: $$Excel-Macros$$ Right click
 
 Thanks.. Its working
 
 -- 
--
-
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
 To post to this group, send email to excel-macros@googlegroups.com
 
 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10

2010-10-05 Thread Tulasi Rao

Hi Ayush,

You got the deserved

Regards,

Tulasi Rao

On Tue, Oct 5, 2010 at 10:40 AM, seema mba seema.mba2...@gmail.com wrote:

Hi Ayush!

You really deserve that title...

Regards,

Seema

On Tue, Oct 5, 2010 at 9:26 AM, Aindril De aind...@gmail.com wrote:

Hey Ayush!! You haven't responded to Neil's email

Where is the Treat Man!!!? [?][?]

Cheers,
Andy

On Mon, Oct 4, 2010 at 9:17 PM, krishna mummina
lovemekris...@gmail.comwrote:

Congratulations Ayush Jain. Really Very proud of you man.

On 10/4/10, rajasekhar praharaju rajasekhar.prahar...@gmail.com wrote:
hearty congratulations ayush, you really deserve it

Infact you are good encyclopedia for us.

Have a flamboyant future and delightful moments for ur future
endeavors.

Regards,
Raj

On Sun, Oct 3, 2010 at 3:47 PM, Ayush jainayus...@gmail.com wrote:

Dear Group,

I am proud to announce that I am awarded Microsoft Most Valuable
Professional MVP on October 01, 2010 for providing technical expertise
in Excel technical communities for past one year.

http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-announced-october-2010.aspx

My deep thanks to each group member for all your support and
contribution to this group.

Best regards,
Ayush Jain
Microsoft MVP

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Thanks Best Regards
Tulasi Rao

Re: $$Excel-Macros$$ Re: Need next number with a twist

I agree. 

Your description SEEMS to imply that if you're identifying records as a 
combination of A-C and F.
Then, the first time a combination is encountered, increment the value in G and 
place the result in H.

 That portion of the statement: 
unless there is already a number for that combo in col H, then it gets 
increased by 1 from that number in col H
seems to say that the subsequent times a record identifier is encounterd, 
increase the value
last calculated FOR THAT RECORD by 1.

But from your examples, every time a record set is duplicated, the SAME Next 
Trans# is used...
Also, 
A-C for rows 2 and 3 are the same.
but column F is different, so the COMBINATION of A-C,F is unique.
Yet in your example, column H of row 2 is G (11) + 1 = 12.
row 3 should be G(11) + 1 = 12 also, but instead is 13!

that would imply that your logic says to create unique Trans# for each unique 
record
But the trick is that you want it to START one above the previous Trans#...

I'm not sure we can do that without a macro.

How about:

Sub New_Trans()
    Dim Dict_Record, Dict_Trans
    Dim RowCnt, R
    Dim RecID, TransNo, TempNo
    
    Set Dict_Record = CreateObject(Scripting.Dictionary)
    Set Dict_Trans = CreateObject(Scripting.Dictionary)
    Dict_Record.RemoveAll
    Dict_Trans.RemoveAll
    
    RowCnt = 
Application.WorksheetFunction.CountA(ActiveSheet.Range(A1:A65000))
    
    For R = 2 To RowCnt
    RecID = ActiveSheet.Cells(R, A).Value _
   ActiveSheet.Cells(R, B).Value _
   ActiveSheet.Cells(R, C).Value _
   ActiveSheet.Cells(R, F).Value
    If (Dict_Record.exists(RecID)) Then
    ActiveSheet.Cells(R, H).Value = Dict_Record.Item(RecID)
    Else
    TempNo = ActiveSheet.Cells(R, G).Value + 1
    While (Dict_Trans.exists(TempNo))
    TempNo = TempNo + 1
    Wend
    Dict_Trans.Add TempNo, TempNo
    Dict_Record.Add RecID, TempNo
    ActiveSheet.Cells(R, H).Value = TempNo
    End If
    Next R
End Sub


Paul


From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Tue, October 5, 2010 5:09:01 AM
Subject: RE: $$Excel-Macros$$ Re: Need next number with a twist

Hi,
For every unique value of col A - C and col F, then the number is increased 
by 
1 from col G unless there is already a number for that combo in col H, then it 
gets increased by 1 from that number in col H
 
I think I understand the first part of the sentence, but the meaning of the 
second part escapes me.
unless there is already a number for that combo in col H, then it gets 
increased by 1 from that number in col H
 It appears that there is always a number in Col H, but is it a number for 
that 
combo ? I can't tell, since it is just a number, and not derived by a formula.
Regards - Dave.
 
 
 

Date: Mon, 4 Oct 2010 13:47:08 -0700
From: n8dine4ma...@yahoo.com
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist
To: excel-macros@googlegroups.com


Does anyone have any thoughts on this?  I've attached the spreadsheet again if 
anyone wants to take a look.  Is there a way maybe to concatenate some cells 
and 
then write a formula?





From: None n8dine4ma...@yahoo.com
To: excel-macros@googlegroups.com
Sent: Thu, September 30, 2010 2:52:14 PM
Subject: Re: $$Excel-Macros$$ Re: Need next number with a twist


Joshua,

Thank you so much for your response.  Unfortunately this isn't working.  The 
formula in col I is referencing col H which is where I originally wanted the 
formula.  There will be no data in col H unless I can't get a formula to work 
in 
there, then it will be manually entered.  Can you help me with how the formula 
should look in cell H2 so that it will return the same numbers I manually 
entered there to show what the formula result should be?  Thank you.

Nadine





From: Joshua Leuthauser leu...@gmail.com
To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
Sent: Wed, September 29, 2010 5:32:44 PM
Subject: $$Excel-Macros$$ Re: Need next number with a twist

Build a key in column g.  The formula for the key should be:
=a2b2c2f2

Populate that down for all of the rows.

Basically what I built says -- look at the column of keys, if you
don't find a match then take the max of all transactions used thus far
and increment by one.  If you do find a match, use the same
transaction number that was used by that key (the combination of a-c2
 f2).

You'll notice that it doesn't reserve the next transaction that
should be used, it just assigns the next transaction to whichever key
shows up that is unique.

After you have your column built with the key, here is the formula I
put in I2:
=IF(ISNA(VLOOKUP(G2,G1:H1,2,FALSE)),MAX(H:H)
+1,VLOOKUP(G2,G1:H1,2,FALSE))

Fill that down the remainder of column I and you have a working
formula that will

$$Excel-Macros$$ Checking Characters

Hi,
I want to check the following things from the charaters
1)Character is capital letter or not
2)Character is bold or not
3)Character is italic or not
4)Character is underlined or not

Am also searching solution for these things. But i didnt fine any
solution. I dont want to convert. I just want to check whether it is
or not.

Could anyone help into this.

Thanks in advance..

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Analysing

Hi
My string is surha12hj
Here i want to take each and every character and i need to take only
charaters which is from A-Z or a-z.. Can any one help on to this.


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Checking Characters

The easiest way to do this is to record a macro in which you
Change a character to Capital, Bold, Italics and Underlined.

Then look at the macro as it is recorded.

The next thing is:

are you looking at the complete cell contents? or
a portion of the cell?

for instance:
to check for capital CONTENTS of cell F14, you can say:  
--
if (Cells(14,F).value = ucase(cells(14,F).value) then
--
fonts are trickier.

to check to see if the entire cell is bold:

If(Range(F14).Font.Bold) then
which is the same as:
IF(Range(F14).Font.Bold = True) then

and Italics is:
IF(Selection.Font.Italic) then

underline is:
Selection.Font.Underline = xlUnderlineStyleSingle

(there are other styles available)

if you're dealing with PART of the cell,
or single characters, it gets trickier.

record a macro and check it out.

Paul

- Original Message 
 From: sivam sivamma...@gmail.com
 To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
 Sent: Tue, October 5, 2010 8:16:55 AM
 Subject: $$Excel-Macros$$ Checking Characters
 
 Hi,
     I want to check the following things from the charaters
 1)Character is capital letter or not
 2)Character is bold or not
 3)Character is italic or not
 4)Character is underlined or not
 
 Am also searching solution for these things. But i didnt fine any
 solution. I dont want to convert. I just want to check whether it is
 or not.
 
 Could anyone help into this.
 
 Thanks in advance..
 
 -- 
--
-
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
 To post to this group, send email to excel-macros@googlegroups.com
 
 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Analysing

what do you want to do with them?
are they in a cell or from a userform?

 


- Original Message 
 From: sivam sivamma...@gmail.com
 To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
 Sent: Tue, October 5, 2010 8:39:34 AM
 Subject: $$Excel-Macros$$ Analysing
 
 Hi
 My string is surha12hj
 Here i want to take each and every character and i need to take only
 charaters which is from A-Z or a-z.. Can any one help on to this.
 
 
 -- 
--
-
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
 To post to this group, send email to excel-macros@googlegroups.com
 
 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Top Poster of the month(Sep10) :- Dilip Pandey and Paul Schreiner

2010-10-05 Thread Ayush

Dear Group,

I am very happy to announce that Dilip and Paul are the top poster for
the month of September'10. They are the joint winners with 22 post
each. They both are all time poster of the group as well.

I really appreciate their consistency and quality of solutions
provided to this group. They both have high willingness to help others
with excellent knowledge of Excel.

Thanks a tonne on behalf of whole group.

Keep posting !

Best regards,
Ayush Jain
Microsoft MVP

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Thanks Everyone

2010-10-05 Thread Ayush

Dear Group,

Thank you so much. I am quite happy to hear all the wishes in last 2
days.

It’s the greatest achievement of my life that I have been awarded as
MVP for contribution to the greater Excel Community.

The whole credit goes to my readers of blogs and Excel group members.
It was not possible without their valuable feedback and support in
running these initiatives.

 last but not least to Microsoft for recognizing me.

I will try my best to bring more resources and make you awesome in
Excel.

Thanks  Best regards,
Ayush Jain
Microsoft MVP


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Comparing two trial balances

2010-10-05 Thread Umar Abeer

Hi guys,

I have to compare two trial balances every month. Presently, I compare them
using a vlook up formula and the account codes against which there is a NA
error I add them to the old trial balance.

I need a VBA code to automate the identification of the new codes and
addition to the rows in the numerical order. I have attached a file with the
requirement illustrated.

Good Day.

Thanks,
Umar

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


trial balance.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Checking Characters

2010-10-05 Thread Paul

As I said, record a macro  in which you change the case/font of single
characters within a string and see how VBA sets the font in a string.

Here's what I came up with:
Option Explicit

Sub ChkString()
Dim NumChars, C, ChkStr

Range(A2:Z65000).ClearContents
Range(A2).Value = Char
Range(B2).Value = Case
Range(C2).Value = Bold
Range(D2).Value = Italics
Range(E2).Value = Underline
ChkStr = Range(A1).Value
NumChars = Len(ChkStr)

For C = 1 To NumChars
Cells(C + 2, A).Value = Mid(ChkStr, C, 1)
If (Mid(ChkStr, C, 1) = UCase(Mid(ChkStr, C, 1))) Then
Cells(C + 2, B).Value = X
End If
If (Left(Cells(1, A).Characters(Start:=C,
Length:=1).Font.FontStyle, 4) = Bold) Then
Cells(C + 2, C).Value = X
End If
If (InStr(Cells(1, A).Characters(Start:=C,
Length:=1).Font.FontStyle, Italic)  0) Then
Cells(C + 2, D).Value = X
End If
If (Cells(1, A).Characters(Start:=C,
Length:=1).Font.Underline  xlUnderlineStyleNone) Then
Cells(C + 2, E).Value = X
End If
Next C
End Sub

Paul


- Original Message 
 From: sivam sivamma...@gmail.com
 To: Paul Schreiner schreiner_p...@att.net
 Sent: Tue, October 5, 2010 8:54:05 AM
 Subject: Re: $$Excel-Macros$$ Checking Characters

 Hi Paul,

 I know these things. But what i want is for example one cell having
 value MathuSudhanan.
 Here i want to take each and every character and i need to test
 whether it is capital or not, bold or not, underlined or not.
 At those times these things will not work.. Please look into this..

On Oct 5, 8:46 am, Paul Schreiner schreiner_p...@att.net wrote:
 The easiest way to do this is to record a macro in which you
 Change a character to Capital, Bold, Italics and Underlined.

 Then look at the macro as it is recorded.

 The next thing is:

 are you looking at the complete cell contents? or
 a portion of the cell?

 for instance:
 to check for capital CONTENTS of cell F14, you can say: 
 --
 if (Cells(14,F).value = ucase(cells(14,F).value) then
 --
 fonts are trickier.

 to check to see if the entire cell is bold:

 If(Range(F14).Font.Bold) then
 which is the same as:
 IF(Range(F14).Font.Bold = True) then

 and Italics is:
 IF(Selection.Font.Italic) then

 underline is:
 Selection.Font.Underline = xlUnderlineStyleSingle

 (there are other styles available)

 if you're dealing with PART of the cell,
 or single characters, it gets trickier.

 record a macro and check it out.

 Paul



 - Original Message 
  From: sivam sivamma...@gmail.com
  To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
  Sent: Tue, October 5, 2010 8:16:55 AM
  Subject: $$Excel-Macros$$ Checking Characters

  Hi,
      I want to check the following things from the charaters
  1)Character is capital letter or not
  2)Character is bold or not
  3)Character is italic or not
  4)Character is underlined or not

  Am also searching solution for these things. But i didnt fine any
  solution. I dont want to convert. I just want to check whether it is
  or not.

  Could anyone help into this.

  Thanks in advance..

  --
 --
 -
  Some important links for excel users:
  1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
  2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
  3. Excel tutorials athttp://www.excel-macros.blogspot.com
  4. Learn VBA Macros athttp://www.quickvba.blogspot.com
  5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com

  To post to this group, send email to excel-macros@googlegroups.com

  
  Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall...- 
 Hide quoted text -

 - Show quoted text -

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Analysing

2010-10-05 Thread Paul

Try:
Sub AddTags()
Dim ChkStr, ChkChar, OutStr, C
ChkStr = Range(A1).Value
OutStr = 
For C = 1 To Len(ChkStr)
ChkChar = Mid(ChkStr, C, 1)
If (IsNumeric(ChkChar)) Then
OutStr = OutStr  N  ChkChar
ElseIf ((ChkChar = UCase(ChkChar)) _
And (ChkChar   )) Then
OutStr = OutStr  H  ChkChar
Else
OutStr = OutStr  ChkChar
End If
Next C
Range(B1).Value = OutStr
End Sub

Paul

- Original Message 
 From: sivam sivamma...@gmail.com
 To: Paul Schreiner schreiner_p...@att.net
 Sent: Tue, October 5, 2010 8:57:18 AM
 Subject: Re: $$Excel-Macros$$ Analysing

 suppose if a cell(one) having value like Mathusudh ANan12  means i
 need to take each and every cell and i need to check. If a particular
 character having Capital letter than i need to put H infront of the
 character. If it is number i need to put N infront of the character.
 Please look into this..


On Oct 5, 8:50 am, Paul Schreiner schreiner_p...@att.net wrote:
 what do you want to do with them?
 are they in a cell or from a userform?

  



 - Original Message 
  From: sivam sivamma...@gmail.com
  To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
  Sent: Tue, October 5, 2010 8:39:34 AM
  Subject: $$Excel-Macros$$ Analysing

  Hi
  My string is surha12hj
  Here i want to take each and every character and i need to take only
  charaters which is from A-Z or a-z.. Can any one help on to this.

  --
 --
 -
  Some important links for excel users:
  1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
  2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
  3. Excel tutorials athttp://www.excel-macros.blogspot.com
  4. Learn VBA Macros athttp://www.quickvba.blogspot.com
  5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com

  To post to this group, send email to excel-macros@googlegroups.com

  
  Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall...- 
 Hide quoted text -

 - Show quoted text -

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10

2010-10-05 Thread Srinivasan Ethirajalu

Good. Keep going..

On Tue, Oct 5, 2010 at 4:09 PM, Tulasi Rao tulasir...@gmail.com wrote:

Hi Ayush,

You got the deserved

Regards,

Tulasi Rao

On Tue, Oct 5, 2010 at 10:40 AM, seema mba seema.mba2...@gmail.comwrote:

Hi Ayush!

You really deserve that title...

Regards,

Seema

On Tue, Oct 5, 2010 at 9:26 AM, Aindril De aind...@gmail.com wrote:

Hey Ayush!! You haven't responded to Neil's email

Where is the Treat Man!!!? [?][?]

Cheers,
Andy

On Mon, Oct 4, 2010 at 9:17 PM, krishna mummina lovemekris...@gmail.com
wrote:

Congratulations Ayush Jain. Really Very proud of you man.

On 10/4/10, rajasekhar praharaju rajasekhar.prahar...@gmail.com
wrote:
hearty congratulations ayush, you really deserve it

Infact you are good encyclopedia for us.

Have a flamboyant future and delightful moments for ur future
endeavors.

Regards,
Raj

On Sun, Oct 3, 2010 at 3:47 PM, Ayush jainayus...@gmail.com wrote:

Dear Group,

I am proud to announce that I am awarded Microsoft Most Valuable
Professional MVP on October 01, 2010 for providing technical
expertise
in Excel technical communities for past one year.

http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-announced-october-2010.aspx

My deep thanks to each group member for all your support and
contribution to this group.

Best regards,
Ayush Jain
Microsoft MVP

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

Re: $$Excel-Macros$$ Analysing

funny! 
Luckily, I haven't had any of my editors convert the 
characters to smileys!

It would make it VERY distracting.. although cheerful!

Paul



- Original Message 
 From: ta...@tinusz.hu ta...@tinusz.hu
 To: excel-macros@googlegroups.com
 Sent: Tue, October 5, 2010 10:17:47 AM
 Subject: Re: $$Excel-Macros$$ Analysing
 
 nice mackro! :-)
 see att.
 
 On Tue, 5 Oct 2010 06:52:11 -0700 (PDT), Paul schreiner_p...@att.net
 wrote:
  Try:
  Sub AddTags()
     Dim ChkStr, ChkChar, OutStr, C
     ChkStr = Range(A1).Value
     OutStr = 
     For C = 1 To Len(ChkStr)
         ChkChar = Mid(ChkStr, C, 1)
         If (IsNumeric(ChkChar)) Then
             OutStr = OutStr  N  ChkChar
         ElseIf ((ChkChar = UCase(ChkChar)) _
             And (ChkChar   )) Then
             OutStr = OutStr  H  ChkChar
         Else
             OutStr = OutStr  ChkChar
         End If
     Next C
     Range(B1).Value = OutStr
  End Sub
  
  Paul
  
  - Original Message 
  From: sivam sivamma...@gmail.com
  To: Paul Schreiner schreiner_p...@att.net
  Sent: Tue, October 5, 2010 8:57:18 AM
  Subject: Re: $$Excel-Macros$$ Analysing
 
  suppose if a cell(one) having value like Mathusudh ANan12  means i
  need to take each and every cell and i need to check. If a particular
  character having Capital letter than i need to put H infront of the
  character. If it is number i need to put N infront of the character.
  Please look into this..
 
  
  On Oct 5, 8:50 am, Paul Schreiner schreiner_p...@att.net wrote:
  what do you want to do with them?
  are they in a cell or from a userform?
 
   
 
 
 
  - Original Message 
   From: sivam sivamma...@gmail.com
   To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com
   Sent: Tue, October 5, 2010 8:39:34 AM
   Subject: $$Excel-Macros$$ Analysing
 
   Hi
   My string is surha12hj
   Here i want to take each and every character and i need to take only
   charaters which is from A-Z or a-z.. Can any one help on to this.
 
   --
  
--

  -
   Some important links for excel users:
   1. Follow us on TWITTER for tips tricks and links :
  http://twitter.com/exceldailytip
   2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
   3. Excel tutorials athttp://www.excel-macros.blogspot.com
   4. Learn VBA Macros athttp://www.quickvba.blogspot.com
   5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
 
   To post to this group, send email to excel-macros@googlegroups.com
 
   
   Like our page on facebook , Just follow below link
  http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall...- 
Hide quoted text -
 
  - Show quoted text -
 
 -- 
--
-
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
 To post to this group, send email to excel-macros@googlegroups.com
 
 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
 

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Numer To Text

2010-10-05 Thread Ankit Agrawal

Thank you for your help
But Sir I am using 2003 excel  i m working on networking PC thus I think
the add-in did not install properly.


On Tue, Oct 5, 2010 at 12:35 PM, P.VIJAYKUMAR vijay.4...@gmail.com wrote:

 Dear Ankit,

 To use the add-in select the number you want to convert into text.then go
 to the add-ins in the Menu bar and select the text to numbers button.
 Automaticaaly the number you selected turns into text. But, you dont't have
 the original number.If you want to have the number and also the converted
 text copy the number into another cell and then change it into text. In this
 way you can have both the number and also you converted text.Hope this
 clarifies your doubt.


 Regards,
 Vijaykumar


 On Tue, Oct 5, 2010 at 11:38 AM, Ankit Agrawal ankit.agrawal...@gmail.com
  wrote:

 Thank you sir for deteil knowledge but again same problem *how we can use
 add-in? plz tell me *

 **
 Thnanks  Regards

 Ankit


   On Tue, Oct 5, 2010 at 8:30 AM, P.VIJAYKUMAR vijay.4...@gmail.comwrote:

  Dear Ankit,

 There is no Direct formula to convert numbers into text.But there are
 some add-ins available in the websites. just google for text to numbers
 converter in excel add-in.
 Once you download the add-in to install the add-in  to excel go to the
 excel ribbon Button, the from there open excel options ,then select add-in
 from the avialble options. Excel will then display the add-ins which are
 already installed . It will also have a go button in the bottom for
 installing others in that it will show some available not installed
 add-ins,you can also install them along with our add-in..After clicking go
 button  then click the browse button then select the file which you
 downloaded to any disk or any place in your computer.Then click OK.Your
 add-in is installed.Once installed the file which is selected for
 installation should not be moved from the place it is downloaded.To many
 movements make the add-in not working.So when downloading only make sure
 that the file is saved in a place in which is is not disturbed further.Once
 installed you add-in shall work in every workbook. Make sure that an addin
 button is added to the Menu Bar.Hope this clarified your problem


 Regards,
 VijayKumar

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link

 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link

 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




 --









 P.VIJAY KUMAR

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Re: Comparing two trial balances

2010-10-05 Thread who

Hi,

Try this out. The formula on the right autopopulated for me, but it
may not for you.

Sub Macro1()
' David
Range(D3).Select
Do Until ActiveCell.Value = 
  NewAccount = ActiveCell.Value
  OldAccount = ActiveCell.Offset(0, -3).Value
  If NewAccount = OldAccount Then
ActiveCell.Offset(1, 0).Select
  ElseIf NewAccount  OldAccount Then
Stop
ReturnAddress = ActiveCell.Address
Rows(ActiveCell.Row).EntireRow.Select
Selection.Insert Shift:=xlDown
Range(ReturnAddress).Select
ActiveCell.Range(A1:C1).Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
ActiveCell.Offset(0, -3).Value = ActiveCell.Value
  ElseIf NewAccount  OldAccount Then
Stop
  End If
Loop
End Sub


Thanks,
David

On Oct 5, 6:03 am, Umar Abeer umarab...@googlemail.com wrote:
 Hi guys,

 I have to compare two trial balances every month. Presently, I compare them
 using a vlook up formula and the account codes against which there is a NA
 error I add them to the old trial balance.

 I need a VBA code to automate the identification of the new codes and
 addition to the rows in the numerical order. I have attached a file with the
 requirement illustrated.

 Good Day.

 Thanks,
 Umar

  trial balance.xlsx
 18KViewDownload

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Help in sending the salary slip

2010-10-05 Thread Ankit Agrawal

But Sir there are more than hundred employee than how we can make only
single sheet



On Tue, Oct 5, 2010 at 1:04 PM, prabhakar jain prabhakar.kj...@gmail.comwrote:

 you can attach your excel salary sheet with salary slip through vlookup and
 copy that slip on mail.
 sending you a sample sheet (EMP CODE IS KEY )

 WRITE ONLY EMP. CODE THE WHOLE DETAIL U CAN EXTRACT.

   On Tue, Oct 5, 2010 at 12:40 PM, P.VIJAYKUMAR vijay.4...@gmail.comwrote:

 Hello Ankit,

 I did not understand you question properly. Why do use mail merge .You can
 create  single sheet work book as you salary ship and give password to it
 and also mail the password to the concerned person through a text file
 attached along with the salary sheet.

 Regards,
 P.VIJAY KUMAR

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link

 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




 --
 Prabhakar Jain
 Mumbai

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Fwd:

2010-10-05 Thread neil johnson

 Hi Everyone ,



I have rows of data with which I'd like to forecast the next 2 periods of

data for.  What is the formula to do this.  I'd like to do this in excel

all on one row as shown below:



monthJan  Feb  March  April  May  June  July  Aug  Sep(current Month)

Next remaing month

--   ---  ---  ---  ---  ---  ---  ---  ---  ---  

--

Sales  10   20  30  10 5060 7580 ?

 ?

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 20 10

2010-10-05 Thread janet dickson

Hi Ayush, my heartiest congratulation

On Tue, Oct 5, 2010 at 4:55 PM, Srinivasan Ethirajalu
srinivasan.ethiraj...@gmail.com wrote:

Good. Keep going..

On Tue, Oct 5, 2010 at 4:09 PM, Tulasi Rao tulasir...@gmail.com wrote:

Hi Ayush,

You got the deserved

Regards,

Tulasi Rao

On Tue, Oct 5, 2010 at 10:40 AM, seema mba seema.mba2...@gmail.comwrote:

Hi Ayush!

You really deserve that title...

Regards,

Seema

On Tue, Oct 5, 2010 at 9:26 AM, Aindril De aind...@gmail.com wrote:

Hey Ayush!! You haven't responded to Neil's email

Where is the Treat Man!!!? [?][?]

Cheers,
Andy

On Mon, Oct 4, 2010 at 9:17 PM, krishna mummina
lovemekris...@gmail.com wrote:

Congratulations Ayush Jain. Really Very proud of you man.

On 10/4/10, rajasekhar praharaju rajasekhar.prahar...@gmail.com
wrote:
hearty congratulations ayush, you really deserve it

Infact you are good encyclopedia for us.

Have a flamboyant future and delightful moments for ur future
endeavors.

Regards,
Raj

On Sun, Oct 3, 2010 at 3:47 PM, Ayush jainayus...@gmail.com wrote:

Dear Group,

I am proud to announce that I am awarded Microsoft Most Valuable
Professional MVP on October 01, 2010 for providing technical
expertise
in Excel technical communities for past one year.

http://blogs.technet.com/b/southasiamvp/archive/2010/10/01/new-mvps-announced-october-2010.aspx

This is a prestigious award from Microsoft given every Quarter to
the
people who share their deep knowledge, real-world experience, and
impartial, objective feedback to help people enhance the way they
use
technology.

My deep thanks to each group member for all your support and
contribution to this group.

Best regards,
Ayush Jain
Microsoft MVP

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

Like our page on facebook , Just follow below link

http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at

Re: $$Excel-Macros$$ Fwd:

I'm not sure you're going to get much help here.

if you were to say that you have a numeric sequence
and would like help predicting the next number in the series.

Then, there MIGHT be a solution.  Because there may be a relationship between 
the numbers in the series:

10 20 30 40 50... the next numbers are 60 and 70.
or
10 20 40 70 110.. the next number would be 160, then 210

So, a relationship exists between the numbers in the series.

But there is no relationship between the numbers when they represent trends in 
sales.

The trends are based on the goods sold, time of year, economy, and a myriad of 
other factors!

I mean.. if you're talking about clothing:
sales of winter clothes is affected by the season and severity.

By simply asking for a numeric trend analysis, even if a numeric relationship 
is 
observed,
it would just be a coincidence. Not an indicator of a trend...

sorry...

Paul


From: neil johnson neil.jh...@googlemail.com
To: excel-macros@googlegroups.com
Sent: Tue, October 5, 2010 11:36:11 AM
Subject: $$Excel-Macros$$ Fwd:




Hi Everyone ,
 
I have rows of data with which I'd like to forecast the next 2 periods of
data for.  What is the formula to do this.  I'd like to do this in excel
all on one row as shown below:
 
month    Jan  Feb  March  April  May  June  July  Aug  Sep(current Month)
Next remaing month
--   ---  ---  ---  ---  ---  ---  ---  ---  ---  
--
Sales  10   20  30  10 50    60 75    80 ?
     ?
 

-- 
--

Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
 

Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Formatting footers using macro command

2010-10-05 Thread CCMacroWriter

I've inherited a workbook with a macro code that when executed
modifies each worksheet's footers.  Below is the code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range(inpQuoteNum).Address Or Target.Address
= Range(inpGrpName).Address Then

Dim lf As String, cf As String, rf As String

lf = Group Name:   Range(inpGrpName).Value
cf = P
rf = Quote #   Range(inpQuoteNum).Value

For Each ws In Worksheets
With ws.PageSetup
.LeftFooter = lf
.CenterFooter = cf
.RightFooter = rf
End With
Next ws

End If

End Sub

With all the looping and the number of sheets in the workbook,
execution is a bit slow.  I thought that if you could simply group the
workbook sheets that needed to be changed and then change them, the
execution would be quicker.  However, after macro recording the steps,
and then editing the steps to specifically change only that part of
the worksheets, I find that only one sheet is changed.  The following
is my code:

' mcrChangeFooters Macro
 Macro recorded 10/4/2010
'

'
Sheets(Array(Cover Sheet, Census, RMM - Debits, Rate
Sheets, _
Standard Pairings, Base Rate Calculation, Age-Gender
Development, _
Area Factor, RAF, RMM - Short Form, RMM - Employer
Form, _
Group Information)).Select
With ActiveSheet.PageSetup
.LeftFooter = Group Name: test
.RightFooter = Quote # a
End With
Sheets(Start).Select
End Sub


I understand that I will have to declare variables and populate the
variables for the left footer and the right footer, but could someone
review and let me know how to write the code so it works, or show me
some code that would run excel more quickly than the code I inherited
or I created.

Thanks in advance for your help!

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Consolitate

2010-10-05 Thread Idhrees Mohamed

Dear Team,

I attach sample excell sheet. In this regard i need consolitate all sheet
datas in single sheet. Here one heading Row is common for all sheet datas.
Because i have big excell file i won't copy and paste in single sheet. Any
ideas about this query (Macro or Any Formula). Please help this requirement
is urgent.

Thanks in Advance.

-- 
Thanks  Regards,

Mohamed Idhrees. S.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


Consolitate.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Sorting Datas

2010-10-05 Thread Idhrees Mohamed

Dear Team,

I need help with attached file. In this one column Relay type, we select
any one item (in the combo box), its display all the corresponding details
below the combo box with the title. Please sortout my problem.

Thanks in Advance.
-
Regards,

Mohamed Idhrees. S.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


Book1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Re: Consolitate

2010-10-05 Thread CCMacroWriter

Have you tried dataconsolodate... ?

On Oct 5, 11:50 am, Idhrees Mohamed idhrees...@gmail.com wrote:
 Dear Team,

 I attach sample excell sheet. In this regard i need consolitate all sheet
 datas in single sheet. Here one heading Row is common for all sheet datas.
 Because i have big excell file i won't copy and paste in single sheet. Any
 ideas about this query (Macro or Any Formula). Please help this requirement
 is urgent.

 Thanks in Advance.

 --
 Thanks  Regards,

 Mohamed Idhrees. S.

  Consolitate.xlsx
 23KViewDownload

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Importing data into excel

2010-10-05 Thread Deepak Rai

Hi Peter,

Sure soon I will send you that consolidation macro.

Regards,
Deepak Rai

On Mon, Oct 4, 2010 at 11:08 PM, Peter Konijn peter@gmail.com wrote:

 Hi,

 It works, thanks a lot for your support.

 I am still curious for the macro of Deepak Rai.



 Regards Peter

 2010/10/3 P.VIJAYKUMAR vijay.4...@gmail.com

 Dear Peter,

 You can Import Data From Data Menu.In DATA Menu Go to Get External Data,
 then select Existing Connections,then you will get a dialog box,in that
 select browse for more then select the file you need to import, then press
 ok. Then you will get a query about what sheets of the workbook you need to
 import and where you need to import .Once you specify your requirements you
 can have your data imported according to your needs.I am Attaching the file
 in which i have impoterd the data of Data One sheet to Import_and_Calc.xlsx.






 Regards,
 VijayKumar


 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link

 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


   --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




-- 
Regards,
Deepak Rai

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ I need a balancing formula. Help pls.

2010-10-05 Thread eDo

I need a formula than can balance input data automatically. Ive been
working on this for quite sometime but unfortunately I cannot seem to
crack the secret. Let say for exampe I have 5 Teams, Team A has 4
members, Team B has 5 members, Team C has 6 members, Team D has 7
members and Team E has 9 members. Now what I need is to add this 5
Teams together and divide it into two groups of teams without dividing
the members in each team. so basically I will have the result of  Team
E grouping with Team C , Team E has 9 members plus Team C has 6
members so that would be a total of 15 members, The other group of
teams would be Team A, Team B and Team D adding their members, 4 plus
5 plus 7 would be a total of 16 members. So basically this is were i
need to get. A formula that can automaticaly sort out the best
possible combination of teams for each group. For anyone that can help
me, Thanks!

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ How to eliminate Zeros

2010-10-05 Thread NRao Mynampati

Hi experts,
how to eleminate zeros while selective range for average
function.Please find the attachement here fro your reference.

Regards,
Rao

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


average.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Hi Experts, Urgent

2010-10-05 Thread rajasekhar praharaju

hi Pankaj,

Can you please tell what formula you are looking for.

Could you please express your query in brief.


Thanks,
Raj

On Tue, Oct 5, 2010 at 8:23 PM, Pankaj Kumar rajputpanka...@gmail.comwrote:

  Hi, Experts Its urgent basis i need u r help, i have a Name list A-Z, I
 have sorting Name list A-Z in different different coloums ,,, with the help
 of formulas ,,,attached excelsheet

 Ex.
 Aaman  Banaran   Canmanra  Damanran
 AryanBhuvanCamelDavid
  *Warm Regards
 Pankaj kumar
 M: 9899816107
 e-mail: rajputpanka...@gmail.com
rajputpank...@yahoo.in*
 *   rajputpankaj1...@rediffmail.com*

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Re: Consolitate

2010-10-05 Thread who

Hi,

You can try this:
Sub Consolidate()
'10/5/2010 by David
  Dim ws As Worksheet
  For Each ws In Worksheets
Sheets(ws.Name).Select
If ActiveSheet.Name = Table 1 Then
  Sheets.Add
  Sheets(ActiveSheet.Name).Name = Consolidate
  Sheets(ws.Name).Select
End If
Range(A1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets(Consolidate).Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Range(A1).Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
  Next ws
End Sub

Hope it helps.

Thanks,
David

On Oct 5, 9:50 am, Idhrees Mohamed idhrees...@gmail.com wrote:
 Dear Team,

 I attach sample excell sheet. In this regard i need consolitate all sheet
 datas in single sheet. Here one heading Row is common for all sheet datas.
 Because i have big excell file i won't copy and paste in single sheet. Any
 ideas about this query (Macro or Any Formula). Please help this requirement
 is urgent.

 Thanks in Advance.

 --
 Thanks  Regards,

 Mohamed Idhrees. S.

  Consolitate.xlsx
 23KViewDownload

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ VBA CODE UNLOCKER

2010-10-05 Thread krishna mummina

Hello Guys,

Check it out.


http://www.vbaunlocker.info/*


*

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ How to eliminate Zeros

2010-10-05 Thread ashish koul

hi

check the attachment see if it helps

On Wed, Oct 6, 2010 at 5:14 AM, NRao Mynampati ml.narasimha...@gmail.comwrote:




 Hi experts,
 how to eleminate zeros while selective range for average
 function.Please find the attachement here fro your reference.

 Regards,
 Rao

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

 --

 --
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links :
 http://twitter.com/exceldailytip
 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials at http://www.excel-macros.blogspot.com
 4. Learn VBA Macros at http://www.quickvba.blogspot.com
 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below link
 http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts




-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com http://akoul.blogspot.com/
*akoul*.wordpress.com http://akoul.wordpress.com/
My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830


P Before printing, think about the environment.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts


average(1).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Checking Characters

Really thanks Paul.. Its working fine...

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Numer To Text

Dear Ankit,

Here is a code that might help you. It is didvided into prts depending upon
your requirement copy and pate the required part in Visual basic.



'' Main Function *'
Function SpellNumbers(ByVal MyNumber)
Dim Rupees, Paise, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) =  Thousand 
Place(3) =  Lac 
Place(4) =  Crore 
Place(5) =  Arab  ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, .)
'Convert Paise and set MyNumber to Rupee amount
If DecimalPlace  0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1)  00, 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber  
If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
If Count  1 Then Temp = GetHundreds(Right(MyNumber, 2))
If Temp   Then Rupees = Temp  Place(Count)  Rupees
If Count = 1 And Len(MyNumber)  3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
If Count  1 And Len(MyNumber)  2 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = 
End If
End If
Count = Count + 1
Loop
Select Case Rupees
Case 
Rupees = No Rupees
Case One
Rupees = One Rupee
Case Else
Rupees = Rupees   Rupees
End Select
Select Case Paise
Case 
Paise = 
Case One
Paise =  and One Paisa
Case Else
Paise =  and   Paise   Paise
End Select
SpellNumbers = Rupees  Paise
End Function
'***
' Converts a number from 100-999 into text *
'***
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right(000  MyNumber, 3) 'Convert the hundreds place
If Mid(MyNumber, 1, 1)  0 Then
Result = GetDigit(Mid(MyNumber, 1, 1))   Hundred 
End If
'Convert the tens and ones place
If Mid(MyNumber, 2, 1)  0 Then
Result = Result  GetTens(Mid(MyNumber, 2))
Else
Result = Result  GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
'*
' Converts a number from 10 to 99 into text. *
'*
Function GetTens(TensText)
Dim Result As String
Result =  'null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select Case Val(TensText)
Case 10: Result = Ten
Case 11: Result = Eleven
Case 12: Result = Twelve
Case 13: Result = Thirteen
Case 14: Result = Fourteen
Case 15: Result = Fifteen
Case 16: Result = Sixteen
Case 17: Result = Seventeen
Case 18: Result = Eighteen
Case 19: Result = Nineteen
Case Else
End Select
Else ' If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = Twenty 
Case 3: Result = Thirty 
Case 4: Result = Forty 
Case 5: Result = Fifty 
Case 6: Result = Sixty 
Case 7: Result = Seventy 
Case 8: Result = Eighty 
Case 9: Result = Ninety 
Case Else
End Select
Result = Result  GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function
'***
' Converts a number from 1 to 9 into text. *
'***
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = One
Case 2: GetDigit = Two
Case 3: GetDigit = Three
Case 4: GetDigit = Four
Case 5: GetDigit = Five
Case 6: GetDigit = Six
Case 7: GetDigit = Seven
Case 8: GetDigit = Eight
Case 9: GetDigit = Nine
Case Else: GetDigit = 
End Select
End Function

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

Re: $$Excel-Macros$$ Numer To Text