Re: $$Excel-Macros$$ Copy some files from a folder to another folder

2011-05-13 Thread ashish koul 
use this   one


Public f As Object, d As Object, sw As Object
Sub Main()

Dim Fso As Object
Set sw = CreateObject("Scripting.FileSystemObject")
Set Fso = CreateObject("Scripting.FileSystemObject")
Set RootFolder = Fso.GetFolder("C:\Documents and
Settings\ashish\Desktop\ashish koul")
FolderRead RootFolder
End Sub
Sub FolderRead(ByRef myFolder)


   For Each f In myFolder.Files
Var = f.Path
If Right(f.Name, 4) = ".xls" Or Right(f.Name, 5) = ".xlsx" Then

  sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"

'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"
   End If


   If Right(f.Name, 5) = ".jpeg" Then
sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
   End If

If Right(f.Name, 4) = ".pdf" Then

sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"

'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"
   End If

   Next f
   For Each d In myFolder.SubFolders
 FolderRead d
   Next d
End Sub


On Sat, May 14, 2011 at 12:19 PM, ashish koul  wrote:

> use move to move files and copy to copy the files
>
>
> Public f As Object, d As Object
> Sub Main()
>
> Dim Fso As Object
> Set Fso = CreateObject("Scripting.FileSystemObject")
> Set RootFolder = Fso.GetFolder("C:\Documents and
> Settings\ashish\Desktop\ashish koul")
> FolderRead RootFolder
> End Sub
> Sub FolderRead(ByRef myFolder)
> Dim sw As Object
> Set sw = CreateObject("Scripting.FileSystemObject")
>For Each f In myFolder.Files
> Var = f.Path
> If Right(f.Name, 4) = ".xls" Or Right(f.Name, 5) = ".xlsx" Then
>
>   sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"
>
> 'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"
>End If
>
>
>If Right(f.Name, 5) = ".jpeg" Then
> sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
> 'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
>End If
>
> If Right(f.Name, 4) = ".pdf" Then
>
> sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"
>
> 'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"
>End If
>
>Next f
>For Each d In myFolder.SubFolders
>  FolderRead d
>Next d
> End Sub
>
>
>
>
> On Sat, May 14, 2011 at 7:15 AM, Rafael Bruno wrote:
>
>> Hi friends,
>>
>> I have a simple request that it's confusing me. I have a folder (let's
>> call "c:\motherfolder\"), that has thousands of subfolders. In each
>> subfolder there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1
>> .jpeg.
>> I need to copy all pdfs to one folder (let's say "c:\pdfs_folder\"), all
>> excel files to another, and all jpegs to another.
>>
>> Can it be done? A long time ago (years) I remember doing something
>> similar, but only with .xls files (and even this I can't remember how).
>>
>> Thanks in advance,
>> Rafael
>> WebRep
>> Overall rating
>>
>>
>> --
>>
>> --
>> 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/discussexcel
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> *akoul*.wordpress.com 
> My Linkedin Profile 
>
>
> P Before printing, think about the environment.
>
>
>


-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/di

Re: $$Excel-Macros$$ Copy some files from a folder to another folder

2011-05-13 Thread ashish koul 
use move to move files and copy to copy the files


Public f As Object, d As Object
Sub Main()

Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Set RootFolder = Fso.GetFolder("C:\Documents and
Settings\ashish\Desktop\ashish koul")
FolderRead RootFolder
End Sub
Sub FolderRead(ByRef myFolder)
Dim sw As Object
Set sw = CreateObject("Scripting.FileSystemObject")
   For Each f In myFolder.Files
Var = f.Path
If Right(f.Name, 4) = ".xls" Or Right(f.Name, 5) = ".xlsx" Then

  sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"

'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\excel\"
   End If


   If Right(f.Name, 5) = ".jpeg" Then
sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\jpeg\"
   End If

If Right(f.Name, 4) = ".pdf" Then

sw.copyFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"

'sw.MoveFile f.Path, "C:\Documents and Settings\ashish\Desktop\pdf\"
   End If

   Next f
   For Each d In myFolder.SubFolders
 FolderRead d
   Next d
End Sub



On Sat, May 14, 2011 at 7:15 AM, Rafael Bruno wrote:

> Hi friends,
>
> I have a simple request that it's confusing me. I have a folder (let's call
> "c:\motherfolder\"), that has thousands of subfolders. In each subfolder
> there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1 .jpeg.
> I need to copy all pdfs to one folder (let's say "c:\pdfs_folder\"), all
> excel files to another, and all jpegs to another.
>
> Can it be done? A long time ago (years) I remember doing something similar,
> but only with .xls files (and even this I can't remember how).
>
> Thanks in advance,
> Rafael
> WebRep
> Overall rating
>
>
> --
>
> --
> 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

$$Excel-Macros$$ Re: Rank the data

2011-05-13 Thread No_Only 
cells F5 "=RANK(E5,$E$5:$E$14,0)"
...
cells F14 "=RANK(E14,$E$5:$E$14,0)"

everything is OK, good luck for you


On 5月13日, 上午10時50分, Rajesh K R  wrote:
> Hi Experts,
>
> Pls find the attachment and tell me how to rank the rooms as per the
> collection received from them.
>
> Regards
>
> Rajesh Kainikkara
>
>  RANKING.xls
> 18K檢視下載

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel

2011-05-13 Thread ashish koul 
what in case of address

which one it should pic

absolute address access address


On Fri, May 13, 2011 at 8:42 PM, Ivaylo  wrote:

> Hi Ashish,
>
> Thank you very much for your efforts!
>
> Your macro is just what I needed.
>
> However, it seems that it doesn't handle the exceptions:
>
> ===
>
> How to handle exceptions:
>
>
> If the consituent word doesn't have its own headword in the list,
> then
> the missing word must be copied in Sheet 2, column A and the phrase
> from which it comes from in column B - i.e. Sheet 2 must be list all
> the words which are part of a multiword phrases but don't have their
> own headwords.
>
>
> This is the case with "abnormal termination". The word "abnormal"
> exists as a headword, and the phrase "abnormal termination" is copied
> in column B next to abnormal. However, the word "termination" doesn't
> exist in column A (as a headword), then "termination" must be copied
> to column A in Sheet 2 and the phrase "abnormal termination" to
> column
> B in Sheet 2 (next to "termination").
> ===
>
> Is it possible for you to add this functionality?
>
> On May 13, 4:26 pm, ashish koul  wrote:
> > try this code
> >
> > Sub tests()
> > Dim i As Long
> >
> > Dim z As String
> >
> > For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
> >
> > If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") > 0
> Then
> >
> > Cells(i, 2) =
> >
> Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim­(Cells(i,
> > 1)), " ", "|")
> >
> > End If
> >
> > Next i
> >
> > For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
> >
> > z = ""
> > If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") = 0
> Then
> >
> > For swa = 1 To Sheets(1).Range("a65356").End(xlUp).Row
> >
> > If swa <> i Then
> >
> > If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)),
> > Application.WorksheetFunction.Trim(Cells(i, 1))) > 0 Then
> >
> > z = z & Application.WorksheetFunction.Trim(Cells(swa, 1)) & "|"
> >
> > End If
> >
> > End If
> >
> > Next swa
> >
> > If Right(z, 1) = "|" Then
> > Cells(i, 2) = Left(z, Len(z) - 1)
> > Else
> >
> > Cells(i, 2) = z
> > End If
> >
> > End If
> >
> > Next i
> >
> > End Sub
> >
> >
> >
> >
> >
> > On Wed, May 11, 2011 at 6:03 PM, Ivaylo  wrote:
> > > Hi to all VBA gurus,
> >
> > > I need help with an Excel macro which can do the following:
> >
> > > I have a long list of dictionary headwords in column A (sorted
> > > alphabetically), something like this:
> >
> > > abnormal termination
> > > abnormal
> > > abort
> > > about
> > > absolute
> > > absolute address
> > > absolute code
> > > accent
> > > access
> > > access address
> > > access mode
> > > access control
> > > access time
> > > address
> >
> > > ...
> >
> > > The list consists of single words and multiword phrases.
> >
> > > My task is to put in column B reference to the single words that
> > > constitute the multiword phrases. For example, for "abnormal
> > > termination" column B must be populated with "abnormal" and
> > > "termination", separated by "|":
> > > abnormal termination abnormal|termination
> >
> > > The second part of this task is that for the single-word headwords I
> > > must put references to their corresponding multiword phrases. For
> > > example, for "absolute" column B must be populated with "absolute
> > > address" and "absolute code", separated by "|":
> > > absolute absolute address|absolute code
> >
> > > Here is a desired output after the processing:
> >
> > > Column A - Column B
> > > abnormal - termination abnormal|termination
> > > abnormal - abnormal termination
> > > abort -
> > > about -
> > > absolute - absolute address|absolute code
> > > absolute address - absolute|address
> > > absolute code - absolute|code
> > > accent -
> > > access access - address|access mode|access control|access time
> > > access address - access|address
> > > access mode - access|mode
> > > access control - access|control
> > > access time - access|time
> > > address - absolute address|access address
> >
> > > How to handle exceptions:
> >
> > > If the consituent word doesn't have its own headword in the list, then
> > > the missing word must be copied in Sheet 2, column A and the phrase
> > > from which it comes from in column B - i.e. Sheet 2 must be list all
> > > the words which are part of a multiword phrases but don't have their
> > > own headwords.
> >
> > > This is the case with "abnormal termination". The word "abnormal"
> > > exists as a headword, and the phrase "abnormal termination" is copied
> > > in column B next to abnormal. However, the word "termination" doesn't
> > > exist in column A (as a headword), then "termination" must be copied
> > > to column A in Sheet 2 and the phrase "abnormal termination" to column
> > > B in Sheet 2 (next to "termination").
> >
> > > I'll be very grateful if anyone can help me writing such a macro.
> >
> > > --
> >
> > >
> -

$$Excel-Macros$$ Copy some files from a folder to another folder

2011-05-13 Thread Rafael Bruno 
Hi friends,

I have a simple request that it's confusing me. I have a folder (let's call
"c:\motherfolder\"), that has thousands of subfolders. In each subfolder
there are 3 files: 1 .pdf, 1 excel (may be .xls or .xlsx), and 1 .jpeg.
I need to copy all pdfs to one folder (let's say "c:\pdfs_folder\"), all
excel files to another, and all jpegs to another.

Can it be done? A long time ago (years) I remember doing something similar,
but only with .xls files (and even this I can't remember how).

Thanks in advance,
Rafael
WebRep
Overall rating

-- 
--
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/discussexcel

RE: $$Excel-Macros$$ Consolidate data

2011-05-13 Thread EXCELL 
Simply use the sumifs formula 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of ChilExcel
Sent: Wednesday, May 11, 2011 2:54 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Consolidate data

 

Please see attached file

 

 

 

By Chilexcel

2011/5/10 Rajesh K R 

Hi Experts 

Pls check the attached file and tell me how to consolidate date month wise.

 

Regards

 

Rajesh Kainikkara

-- 

--
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/discussexcel




-- 

Visita ; http://sites.google.com/site/chilexcel/Home

Visita ; http://www.youtube.com/user/timextag41

 

-- 

--
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/discussexcel

-- 
--
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/discussexcel


CONSOLIDATE_Chilexcel.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Rank the data

2011-05-13 Thread Mahesh parab 
Hi Rajesh

Try
in F5 cell use below formula and drag it
'=RANK(E5,$E$5:$E$14)


On Fri, May 13, 2011 at 8:20 PM, Rajesh K R wrote:

> Hi Experts,
>
> Pls find the attachment and tell me how to rank the rooms as per the
> collection received from them.
>
> Regards
>
> Rajesh Kainikkara
>
> --
>
> --
> 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/discussexcel
>

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ Rank the data

2011-05-13 Thread Ashish Pradhan 

Hi

You can use the formula
=RANK(C5,$C$5:$C$14)

I have used Rent as the Ranking Criteria. If you want to use Total 
Collection, simply change C5 in the above formula to E5.


Copy the formula in all the required cells.


Thanks

Ashish


On 5/13/2011 8:20 PM, Rajesh K R wrote:

Hi Experts,

Pls find the attachment and tell me how to rank the rooms as per the
collection received from them.

Regards

Rajesh Kainikkara



--
--
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/discussexcel

Re: $$Excel-Macros$$ Rank the data

2011-05-13 Thread mayur 
Hi Rajesh,

Please find attached.

Regards,
Mayur

On Fri, May 13, 2011 at 9:50 AM, Rajesh K R wrote:

> Hi Experts,
>
> Pls find the attachment and tell me how to rank the rooms as per the
> collection received from them.
>
> Regards
>
> Rajesh Kainikkara
>
> --
>
> --
> 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/discussexcel
>

-- 
--
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/discussexcel


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

RE: $$Excel-Macros$$ Excel Fourmla

2011-05-13 Thread Mishra, Kailash (GE Capital, Non-GE) 
Dear Ashish,

 

This formula not working Request you please clarify attached excel
sheet.

 

Regards 

 

Kailash 

 



From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of ashish koul
Sent: Friday, May 13, 2011 8:35 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Excel Fourmla

 

Sub swa()


Dim s
For k = 1 To Sheets(1).Range("a65356").End(xlUp).Row


s = ""

For i = Len(Sheets(1).Cells(k, 1).Text) To 1 Step -1

If IsNumeric(Mid(Sheets(1).Cells(k, 1).Value, i, 1)) Then
s = Mid(Sheets(1).Cells(k, 1).Value, i, 1) & s

Else

Exit For
End If


Next i
MsgBox s
Sheets(1).Cells(k, 3).Value = s
Next k

End Sub



On Fri, May 13, 2011 at 7:05 PM, Mishra, Kailash (GE Capital, Non-GE) <
kailash.mis...@ge.com> wrote:

Hi Experts,

 

I have Consolidate Addresses along with PIN code in one column I need
only the PIN the pin code in Next column Please confirm if any formula
for this,

 

 

Regards 

 

 

 

 

 

 

 

 

Appointment Address

PIN Code

13/1, NS road, room no735, Martial street,kolkata-71

 

Opp.Bank of India.Shanti Park,Mira Road East

 

flat no 13,ground floor,guru nanak market,infrt of national park,laajpat
nagar,part 4,delhi 110024

 

97-A, pocket-B, mayur vihar, phase-II, delhi-110092

 

A-7, SIDDHI APARTMENT OPP.AMAR~APARTMENT OPERA SOCI., nava vikas road,
PALDI ahm 380007

 

# 27/161, Pragatinagar~Naranpura ahm 380013

 

jp nagar 6 th phase near sindoora kalyana mantap sarakki signal
Bangalore-560078

 

THE FOYER HOUSE NO.52, 2ND CRSS HUCCHINS RD , COX TOWN, BANGALORE

 

#229, 9th A Main~vijayanagar, NEAR GOVT SCHOOL, BANGALORE-40

 

 

-- 

--
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/discussexcel




-- 

Regards

 

Ashish Koul

akoul.blogspot.com  

akoul.wordpress.com  

My Linkedin Profile  

 

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/discussexcel

-- 
--
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/discussexcel


Book6.xls
Description: Book6.xls

Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel

2011-05-13 Thread Ivaylo 
Hi Ashish,

Thank you very much for your efforts!

Your macro is just what I needed.

However, it seems that it doesn't handle the exceptions:

===

How to handle exceptions:


If the consituent word doesn't have its own headword in the list,
then
the missing word must be copied in Sheet 2, column A and the phrase
from which it comes from in column B - i.e. Sheet 2 must be list all
the words which are part of a multiword phrases but don't have their
own headwords.


This is the case with "abnormal termination". The word "abnormal"
exists as a headword, and the phrase "abnormal termination" is copied
in column B next to abnormal. However, the word "termination" doesn't
exist in column A (as a headword), then "termination" must be copied
to column A in Sheet 2 and the phrase "abnormal termination" to
column
B in Sheet 2 (next to "termination").
===

Is it possible for you to add this functionality?

On May 13, 4:26 pm, ashish koul  wrote:
> try this code
>
> Sub tests()
> Dim i As Long
>
> Dim z As String
>
> For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") > 0 Then
>
> Cells(i, 2) =
> Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim­(Cells(i,
> 1)), " ", "|")
>
> End If
>
> Next i
>
> For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> z = ""
> If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") = 0 Then
>
> For swa = 1 To Sheets(1).Range("a65356").End(xlUp).Row
>
> If swa <> i Then
>
> If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)),
> Application.WorksheetFunction.Trim(Cells(i, 1))) > 0 Then
>
> z = z & Application.WorksheetFunction.Trim(Cells(swa, 1)) & "|"
>
> End If
>
> End If
>
> Next swa
>
> If Right(z, 1) = "|" Then
> Cells(i, 2) = Left(z, Len(z) - 1)
> Else
>
> Cells(i, 2) = z
> End If
>
> End If
>
> Next i
>
> End Sub
>
>
>
>
>
> On Wed, May 11, 2011 at 6:03 PM, Ivaylo  wrote:
> > Hi to all VBA gurus,
>
> > I need help with an Excel macro which can do the following:
>
> > I have a long list of dictionary headwords in column A (sorted
> > alphabetically), something like this:
>
> > abnormal termination
> > abnormal
> > abort
> > about
> > absolute
> > absolute address
> > absolute code
> > accent
> > access
> > access address
> > access mode
> > access control
> > access time
> > address
>
> > ...
>
> > The list consists of single words and multiword phrases.
>
> > My task is to put in column B reference to the single words that
> > constitute the multiword phrases. For example, for "abnormal
> > termination" column B must be populated with "abnormal" and
> > "termination", separated by "|":
> > abnormal termination abnormal|termination
>
> > The second part of this task is that for the single-word headwords I
> > must put references to their corresponding multiword phrases. For
> > example, for "absolute" column B must be populated with "absolute
> > address" and "absolute code", separated by "|":
> > absolute absolute address|absolute code
>
> > Here is a desired output after the processing:
>
> > Column A - Column B
> > abnormal - termination abnormal|termination
> > abnormal - abnormal termination
> > abort -
> > about -
> > absolute - absolute address|absolute code
> > absolute address - absolute|address
> > absolute code - absolute|code
> > accent -
> > access access - address|access mode|access control|access time
> > access address - access|address
> > access mode - access|mode
> > access control - access|control
> > access time - access|time
> > address - absolute address|access address
>
> > How to handle exceptions:
>
> > If the consituent word doesn't have its own headword in the list, then
> > the missing word must be copied in Sheet 2, column A and the phrase
> > from which it comes from in column B - i.e. Sheet 2 must be list all
> > the words which are part of a multiword phrases but don't have their
> > own headwords.
>
> > This is the case with "abnormal termination". The word "abnormal"
> > exists as a headword, and the phrase "abnormal termination" is copied
> > in column B next to abnormal. However, the word "termination" doesn't
> > exist in column A (as a headword), then "termination" must be copied
> > to column A in Sheet 2 and the phrase "abnormal termination" to column
> > B in Sheet 2 (next to "termination").
>
> > I'll be very grateful if anyone can help me writing such a macro.
>
> > --
>
> > ---­---
> > 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.

$$Excel-Macros$$ Excel query

2011-05-13 Thread sekhar p 
Hi Experts,


Basically i have 4 different workbooks data with same format i will
consolidate all the 4 work books and apply filter
and go to
 Column AH,   and filter by X category  and move them to new sheet and
rename that new  sheet as In active
like wise ill perform the same for these  four columns as well   AI,   BE
,BG,   BZ


is there any possibility to automate this thing please advice i am end user
in excel macros.


Thanks,
Sekhar

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ Excel Fourmla

2011-05-13 Thread ashish koul 
Sub swa()


Dim s
For k = 1 To Sheets(1).Range("a65356").End(xlUp).Row


s = ""

For i = Len(Sheets(1).Cells(k, 1).Text) To 1 Step -1

If IsNumeric(Mid(Sheets(1).Cells(k, 1).Value, i, 1)) Then
s = Mid(Sheets(1).Cells(k, 1).Value, i, 1) & s

Else

Exit For
End If


Next i
MsgBox s
Sheets(1).Cells(k, 3).Value = s
Next k

End Sub


On Fri, May 13, 2011 at 7:05 PM, Mishra, Kailash (GE Capital, Non-GE) <
kailash.mis...@ge.com> wrote:

>  Hi Experts,
>
>
>
> I have Consolidate Addresses along with PIN code in one column I need only
> the PIN the pin code in Next column Please confirm if any formula for this,
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Appointment Address*
>
> *PIN Code*
>
> 13/1, NS road, room no735, Martial street,kolkata-71
>
>
>
> Opp.Bank of India.Shanti Park,Mira Road East
>
>
>
> flat no 13,ground floor,guru nanak market,infrt of national park,laajpat
> nagar,part 4,delhi 110024
>
>
>
> 97-A, pocket-B, mayur vihar, phase-II, delhi-110092
>
>
>
> A-7, SIDDHI APARTMENT OPP.AMAR~APARTMENT OPERA SOCI., nava vikas road,
> PALDI ahm 380007
>
>
>
> # 27/161, Pragatinagar~Naranpura ahm 380013
>
>
>
> jp nagar 6 th phase near sindoora kalyana mantap sarakki signal
> Bangalore-560078
>
>
>
> THE FOYER HOUSE NO.52, 2ND CRSS HUCCHINS RD , COX TOWN, BANGALORE
>
>
>
> #229, 9th A Main~vijayanagar, NEAR GOVT SCHOOL, BANGALORE-40
>
>
>
>
>
> --
>
> --
> 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

$$Excel-Macros$$ Re: Excel Fourmla

2011-05-13 Thread ashish koul 
Sub swa()


Dim s
For k = 1 To Sheets(1).Range("a65356").End(xlUp).Row


s = ""

For i = Len(Sheets(1).Cells(k, 1).Text) To 1 Step -1

If IsNumeric(Mid(Sheets(1).Cells(k, 1).Value, i, 1)) Then
s = Mid(Sheets(1).Cells(k, 1).Value, i, 1) & s

Else

Exit For
End If


Next i
MsgBox s
Sheets(1).Cells(k, 3).Value = s
Next k

End Sub

On Fri, May 13, 2011 at 7:05 PM, Mishra, Kailash (GE Capital, Non-GE) <
kailash.mis...@ge.com> wrote:

>  Hi Experts,
>
>
>
> I have Consolidate Addresses along with PIN code in one column I need only
> the PIN the pin code in Next column Please confirm if any formula for this,
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Appointment Address*
>
> *PIN Code*
>
> 13/1, NS road, room no735, Martial street,kolkata-71
>
>
>
> Opp.Bank of India.Shanti Park,Mira Road East
>
>
>
> flat no 13,ground floor,guru nanak market,infrt of national park,laajpat
> nagar,part 4,delhi 110024
>
>
>
> 97-A, pocket-B, mayur vihar, phase-II, delhi-110092
>
>
>
> A-7, SIDDHI APARTMENT OPP.AMAR~APARTMENT OPERA SOCI., nava vikas road,
> PALDI ahm 380007
>
>
>
> # 27/161, Pragatinagar~Naranpura ahm 380013
>
>
>
> jp nagar 6 th phase near sindoora kalyana mantap sarakki signal
> Bangalore-560078
>
>
>
> THE FOYER HOUSE NO.52, 2ND CRSS HUCCHINS RD , COX TOWN, BANGALORE
>
>
>
> #229, 9th A Main~vijayanagar, NEAR GOVT SCHOOL, BANGALORE-40
>
>
>
>
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

Re: $$Excel-Macros$$ if with 3 criteria

2011-05-13 Thread Rajesh K R 
Hi Everybody
Thank you for all send me reply.

Regards
Rajesh Kainikkara


On 5/13/11, rajan verma  wrote:
> we can compare range without if by array function..
> ={(C3:J3)=(C4:J4)}
>
> -- Forwarded message --
> From: irfan khan 
> Date: Thu, May 12, 2011 at 9:42 PM
> Subject: Re: $$Excel-Macros$$ if with 3 criteria
> To: excel-macros@googlegroups.com
>
>
> Hi Rajesh,
>
> I am not clear with you problem, yes u can use "And" function for multiple
> criteria.
> for example
>
> =IF(AND(B4=1212,C4=1117),E4,G4)
> the content in yellow are the two conditions
>
> thanks
>
>
> On Thu, May 12, 2011 at 8:51 PM, Rajesh K R 
> wrote:
>> Hi Experts,
>> Pls find the attached file and solve the issue regarding if function.
>>
>> Regards
>> Rajesh kainikkara
>>
>> --
>>
> --
>> 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/discussexcel
>>
>
>
>
> --
> Thanks,
> Irfan Ahmed Khan
>
>
>  --
> --
> 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/discussexcel
>
>
>
> --
> Regards
> Rajan verma
> +91 9158998701
>
> --
> --
> 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/discussexcel
>

-- 
--
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/discussexcel

$$Excel-Macros$$ Rank the data

2011-05-13 Thread Rajesh K R 
Hi Experts,

Pls find the attachment and tell me how to rank the rooms as per the
collection received from them.

Regards

Rajesh Kainikkara

-- 
--
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/discussexcel


RANKING.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ fixing Format of pivot table noit working

2011-05-13 Thread ashish koul 
try this pivot  pivot tutorial

http://www.contextures.com/xlPivot11.html

http://www.contextures.com/excel-vba-pivot-table-field-list.html

You can also select the header and use find and replace option




On Thu, May 12, 2011 at 4:11 PM, Jorge Marques  wrote:

> Hi guys is there anyway to format a pivot table and it´s stays that way
> whether i insert or take the options from and columns labels and row labels
> because when i change the design of the pivot it put´s everything back to
> standard like Sum of Jan instead of Jan like i what
>
> i tried to do with a macro on activesheet but i don´´t know how to assign
> the macro to the pivot, i have to change the name of the pivot in the
> macro!thank you very much 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

Re: $$Excel-Macros$$ Copy & paste data for dynamic data_need Excel Macro Solution

2011-05-13 Thread ashish koul 
can you attach the sample sheet

On Thu, May 12, 2011 at 3:47 PM, sagaraher  wrote:

> I have got following data in excel. I need to copy 1st number in given
> row for e.g. 122532 should be copied against 100 for 4 times & similar
> way 321654 should be get copied against 200 for 3 time like way.
> ( Problem is that I'm not sure how many times 100 or 200 nos. comes )
>
> kindly suggest me macro to run the same for very large data
>
>
>
> 122532
> 100
> 100
> 100
> 100
>
> 321654
> 200
> 200
> 200
>
> 654987
> 300
> 300
> 300
> 300
> 300
> 300
>
> 987654
> 100
> 100
>
> 789654
> 300
> 300
> 300
> 300
> 300
> 300
> 300
> 300
>
> --
>
> --
> 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

Re: $$Excel-Macros$$

2011-05-13 Thread ashish koul 
can you explain it bit more and attach the sample sheet

On Wed, May 11, 2011 at 4:45 PM, jmothilal  wrote:

> Dear Experts,
>
>
> I am  typing   Name  and place name  day 2 day different  excel files,
> what is easy method   i type these names and place names.
>
>
>
>
> --
> J.Mohilal
> Universal Computer Systems
> # 16, Brindavan Complex
> Otteri, Vellore-2
>
> --
>
> --
> 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

Re: $$Excel-Macros$$ A macro to parse a wordlist in Excel

2011-05-13 Thread ashish koul 
try this code


Sub tests()
Dim i As Long

Dim z As String


For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row


If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") > 0 Then

Cells(i, 2) =
Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim(Cells(i,
1)), " ", "|")

End If

Next i

For i = 1 To Sheets(1).Range("a65356").End(xlUp).Row

z = ""
If InStr(1, Application.WorksheetFunction.Trim(Cells(i, 1)), " ") = 0 Then


For swa = 1 To Sheets(1).Range("a65356").End(xlUp).Row

If swa <> i Then


If InStr(1, Application.WorksheetFunction.Trim(Cells(swa, 1)),
Application.WorksheetFunction.Trim(Cells(i, 1))) > 0 Then

z = z & Application.WorksheetFunction.Trim(Cells(swa, 1)) & "|"

End If

End If

Next swa

If Right(z, 1) = "|" Then
Cells(i, 2) = Left(z, Len(z) - 1)
Else


Cells(i, 2) = z
End If

End If


Next i

End Sub




On Wed, May 11, 2011 at 6:03 PM, Ivaylo  wrote:

> Hi to all VBA gurus,
>
> I need help with an Excel macro which can do the following:
>
> I have a long list of dictionary headwords in column A (sorted
> alphabetically), something like this:
>
> abnormal termination
> abnormal
> abort
> about
> absolute
> absolute address
> absolute code
> accent
> access
> access address
> access mode
> access control
> access time
> address
>
> ...
>
> The list consists of single words and multiword phrases.
>
> My task is to put in column B reference to the single words that
> constitute the multiword phrases. For example, for "abnormal
> termination" column B must be populated with "abnormal" and
> "termination", separated by "|":
> abnormal termination abnormal|termination
>
> The second part of this task is that for the single-word headwords I
> must put references to their corresponding multiword phrases. For
> example, for "absolute" column B must be populated with "absolute
> address" and "absolute code", separated by "|":
> absolute absolute address|absolute code
>
>
> Here is a desired output after the processing:
>
>
> Column A - Column B
> abnormal - termination abnormal|termination
> abnormal - abnormal termination
> abort -
> about -
> absolute - absolute address|absolute code
> absolute address - absolute|address
> absolute code - absolute|code
> accent -
> access access - address|access mode|access control|access time
> access address - access|address
> access mode - access|mode
> access control - access|control
> access time - access|time
> address - absolute address|access address
>
> How to handle exceptions:
>
> If the consituent word doesn't have its own headword in the list, then
> the missing word must be copied in Sheet 2, column A and the phrase
> from which it comes from in column B - i.e. Sheet 2 must be list all
> the words which are part of a multiword phrases but don't have their
> own headwords.
>
> This is the case with "abnormal termination". The word "abnormal"
> exists as a headword, and the phrase "abnormal termination" is copied
> in column B next to abnormal. However, the word "termination" doesn't
> exist in column A (as a headword), then "termination" must be copied
> to column A in Sheet 2 and the phrase "abnormal termination" to column
> B in Sheet 2 (next to "termination").
>
> I'll be very grateful if anyone can help me writing such a macro.
>
> --
>
> --
> 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/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
*akoul*.wordpress.com 
My Linkedin Profile 


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/discussexcel

$$Excel-Macros$$ Please help to make my project good.

2011-05-13 Thread Zafar Iqbal 
Dear, With due respect to your effort, I tried with a simplified formula and 
got the same result. Please the attached file with both formulas. Thanks

 

Regards,

ZAFAR IQBAL

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of §»VIPER«§
Sent: Thursday, May 12, 2011 8:36 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Please help to make my project good.

 

pfa



On Thu, May 12, 2011 at 12:31 AM, Mahesh parab  wrote:

Hi

 

check whether attach file helps.

On Wed, May 11, 2011 at 3:51 PM, karan 1237  wrote:

Respected Sirs/Mam,

I need your help making my project good.  Please find attachment. I will be 
very obliged if someone can help me as earlier as possible.

--
Basically I have to do that when I click on drop down list & select another 
career i.e. Building and Construction so In sub career list data should comes 
from the Building and construction's sheet and If I select another career i.e. 
Catering and Hospitality so data should comes from its list (Catering and 
Hospitality) and scroll bar should work with the same. I have 16 careers so 
please tell me how can I do 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/discussexcel


Simplified IF with Offset.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Excel Fourmla

2011-05-13 Thread Mishra, Kailash (GE Capital, Non-GE) 
Hi Experts,

 

I have Consolidate Addresses along with PIN code in one column I need
only the PIN the pin code in Next column Please confirm if any formula
for this,

 

 

Regards 

 

 

 

 

 

 

 

 

Appointment Address

PIN Code

13/1, NS road, room no735, Martial street,kolkata-71

 

Opp.Bank of India.Shanti Park,Mira Road East

 

flat no 13,ground floor,guru nanak market,infrt of national park,laajpat
nagar,part 4,delhi 110024

 

97-A, pocket-B, mayur vihar, phase-II, delhi-110092

 

A-7, SIDDHI APARTMENT OPP.AMAR~APARTMENT OPERA SOCI., nava vikas road,
PALDI ahm 380007

 

# 27/161, Pragatinagar~Naranpura ahm 380013

 

jp nagar 6 th phase near sindoora kalyana mantap sarakki signal
Bangalore-560078

 

THE FOYER HOUSE NO.52, 2ND CRSS HUCCHINS RD , COX TOWN, BANGALORE

 

#229, 9th A Main~vijayanagar, NEAR GOVT SCHOOL, BANGALORE-40

 

 

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ Abridged summary of excel-macros@googlegroups.com - 12 Messages in 8 Topics

2011-05-13 Thread ashish koul 
text to column and select tab..

On Thu, May 12, 2011 at 5:12 PM, renuka chari  wrote:

> hi to all thanks in advance here iam attaching one file i need like
> that but idont know how to do this can u help me any one Pl
>
> thanks & regards
>
>
>
>
>
> On 12 May 2011 05:58,   wrote:
> >   Today's Topic Summary
> >
> > Group: http://groups.google.com/group/excel-macros/topics
> >
> > Please help to make my project good. [2 Updates]
> > Calculate Due dates [2 Updates]
> > [No Subject] [2 Updates]
> > A macro to parse a wordlist in Excel [1 Update]
> > .Refresh Background:-False [1 Update]
> > Total of sum of Diff. criteria according to date [1 Update]
> > How to start Excel-Macro [1 Update]
> > Consolidate data [2 Updates]
> >
> >  Topic: Please help to make my project good.
> >
> > karan 1237  May 11 03:51PM +0530 ^
> >
> > Respected Sirs/Mam,
> >
> > I need your help making my project good. Please find attachment. I will
> be
> > very obliged if someone can help me as earlier as possible.
> >
> > --
> > Basically I have to do more...
> >
> > "STDEV(i)"  May 12 07:03AM +0700 ^
> >
> > please check the attachment if it helps..
> >
> > best regards,
> > STDEV(i)
> >
> >
> > note:
> > we need dynamic* Max Value* of ScrollBar
> > so we use Scroolbar from ActiveX Control, instead of from FORM. more...
> >
> >  Topic: Calculate Due dates
> >
> > Mr excel  May 11 08:37PM +0530 ^
> >
> > what i meant is instead of the period,the formula is showing as a result
> the
> > formula should return only the month with the year.
> > For example,*05-Apr-2011 TO 04-May-2011*.
> > should return apr'2011. more...
> >
> > Mr excel  May 11 08:42PM +0530 ^
> >
> > Got it ashish bhayya..thanks a lot.played with the formula and got the
> > desired result..
> > Thanks a lot.
> >
> > more...
> >
> >  Topic: [No Subject]
> >
> > "Siraj Momin (BTG)"  May 11 01:27PM +0400 ^
> >
> > Hi
> >
> >
> >
> > I have following code for filtering It works fine but the problem is in
> > red font color code, this field is date field & when I put this it gives
> > wrong information
> >
> > May be because of more...
> >
> > jmothilal  May 11 04:45PM +0530 ^
> >
> > Dear Experts,
> >
> >
> > I am typing Name and place name day 2 day different excel files, what
> > is easy method i type these names and place names.
> >
> >
> >
> >
> > --
> > J.Mohilal
> > Universal Computer more...
> >
> >  Topic: A macro to parse a wordlist in Excel
> >
> > Ivaylo  May 11 05:33AM -0700 ^
> >
> > Hi to all VBA gurus,
> >
> > I need help with an Excel macro which can do the following:
> >
> > I have a long list of dictionary headwords in column A (sorted
> > alphabetically), something like this:
> > more...
> >
> >  Topic: .Refresh Background:-False
> >
> > Sobavia  May 11 01:09AM -0500 ^
> >
> > Thanks Ashish. It helped
> >
> > Sent from my iPhone
> >
> > more...
> >
> >  Topic: Total of sum of Diff. criteria according to date
> >
> > Rohan Young  May 11 10:42AM +0530 ^
> >
> > Thanks very much Ashish Ji & Zafar
> >
> > Regds
> >
> > ROHAN
> >
> > more...
> >
> >  Topic: How to start Excel-Macro
> >
> > pramod kumar  May 11 10:38AM +0530 ^
> >
> > Thanks for reply Sir...
> >
> >
> >
> > --
> > Regards,
> > Pramod Kumar
> > Technocare Solution(TCS)
> > Rudrapur
> > +91 9927033573
> > E-Mail:-kumar.pramod03i...@gmail.com
> > more...
> >
> >  Topic: Consolidate data
> >
> > ashish koul  May 11 08:07AM +0530 ^
> >
> > check the attachemnt
> >
> >
> > see if it helps
> >
> >
> >
> >
> > --
> > *Regards*
> > * *
> > *Ashish Koul*
> > *akoul*.*blogspot*.com 
> > *akoul*.wordpress.com  more...
> >
> > ChilExcel  May 10 05:54PM -0400 ^
> >
> > Please see attached file
> >
> >
> >
> > By Chilexcel
> >
> > 2011/5/10 Rajesh K R 
> >
> >
> > --
> > Visita ; http://sites.google.com/site/chilexcel/Home
> > more...
> >
> > --
> >
> --
> > 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/discussexcel
> >
>
> --
>
> --
> 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 ht

$$Excel-Macros$$ Re: VB Help needed.

2011-05-13 Thread Stuart Redmann 


On 12 Mai, 13:22, Born to Win  wrote:
> Hi Experts,
>
> in attached excel file you can see contact no. along with  many services
> activated on a single no. & you can find it in separate entry i am trying to
> write a VB program so that i would have unique no. & services  (column wise)
> in new sheet in one column after a single click. please help me out

Copy the following code into the worksheet Desire format, add a button
that executes the sub PrintServiceColumnWise


__
' This forces us to declare all variables with a DIM statement (so
' we cannot get any errors due to mis-typed variable names).
Option Explicit

Sub PrintServiceColumnWise()

  Dim SourceCell As Excel.Range
  Set SourceCell = Worksheets("Base").Cells(2, 1)

  Dim TargetCell As Excel.Range
  Set TargetCell = Worksheets("Desire format").Cells(3, 1)

  ' Go through the first column in the source sheet until we find
  ' an empty cell.
  While SourceCell.Value <> ""

' How we have to proceed depends on whether we have seen the
' current MISDN in the last source row.
Dim LastMISDN As String
If LastMISDN <> SourceCell.Value Then

  ' If we see the MISDN for the first time, we copy the
  ' MISDN into the first column and copy the service into
  ' first service column.
  Set TargetCell = TargetCell.Offset(1, -TargetCell.Column + 1)
  TargetCell.Value = SourceCell.Value
  Set TargetCell = TargetCell.Offset(0, 1)
  TargetCell.Value = SourceCell.Offset(0, 3).Value


Else
  ' If we have seen the MISDN in the last iteration, we have to
  ' put the current service in the next column
  ' instead of the next row.
  Set TargetCell = TargetCell.Offset(0, 1)
  TargetCell.Value = SourceCell.Offset(0, 3)
End If

' Remember the MISDN that we have seen in this iteration and
' advance the source cell to the next row.
LastMISDN = SourceCell.Value
Set SourceCell = SourceCell.Offset(1, 0)

  Wend

End Sub


Regards,
Stuart

-- 
--
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/discussexcel

$$Excel-Macros$$ How to change Date format

2011-05-13 Thread Zafar Iqbal 
Excellent formula. Well Done.

 

Regards,

ZAFAR IQBAL

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Sixthsense
Sent: Friday, May 13, 2011 12:13 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ How to change Date format

Hi Prabhu,

Try the below formula and format the cell as date.

=IF(AND(OR(LEN(TRIM(B2))=5,LEN(TRIM(B2))=6),ISNUMBER(B2)),IF(LEN(TRIM(B2))=5
,--(--MID(TRIM(B2),2,2)&"-"&--LEFT(TRIM(B2),1)&"-"&--MID(TRIM(B2),4,2)),--(-
-MID(TRIM(B2),3,2)&"-"&--LEFT(TRIM(B2),2)&"-"&--MID(TRIM(B2),5,2))),"")

Herewith I have attached a sample file for your reference.

Hope that helps!

---
Sixthsense
:) Man of Extreme & Innovative Thoughts :)

On Thu, May 12, 2011 at 5:44 PM, Prabhu  wrote:

Hi friends,

 

Plz help to change date format when downloaded report from 1st of every
month to 9th of the month will be like DMMYY . 

 

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread Sixthsense 
Hi Stdev,


Your Solution will fail when the person intended to mention the year 1998 in
this manner 10498.


---
*Sixthsense
**:) Man of Extreme & Innovative Thoughts :)*


On Fri, May 13, 2011 at 4:13 PM, STDEV(i)  wrote:

> *
> =DATE(2000+RIGHT(B2,2),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))
> *
>
> see the attachment
>
>
>
> On Thu, May 12, 2011 at 7:14 PM, Prabhu  wrote:
>
>> Hi friends,
>>
>> Plz help to change date format when downloaded report from 1st of every
>> month to 9th of the month will be like DMMYY .
>>
>> I have no issue in changing the format using text to column  from 10th
>> date because it will be in DDMMYY.
>>
>> I have attached the sample sheet for your reference.Plz help to know the
>> date format as DDMMYY for entire month.(i am requesting macros to do the
>> same)
>>
>> Regards,
>>
>>  Pr
>>
>


-- 
---
*Sixthsense
**:) Man of Extreme & Innovative Thoughts :)*

-- 
--
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/discussexcel

Fwd: $$Excel-Macros$$ if with 3 criteria

2011-05-13 Thread rajan verma 
we can compare range without if by array function..
={(C3:J3)=(C4:J4)}

-- Forwarded message --
From: irfan khan 
Date: Thu, May 12, 2011 at 9:42 PM
Subject: Re: $$Excel-Macros$$ if with 3 criteria
To: excel-macros@googlegroups.com


Hi Rajesh,

I am not clear with you problem, yes u can use "And" function for multiple
criteria.
for example

=IF(AND(B4=1212,C4=1117),E4,G4)
the content in yellow are the two conditions

thanks


On Thu, May 12, 2011 at 8:51 PM, Rajesh K R 
wrote:
> Hi Experts,
> Pls find the attached file and solve the issue regarding if function.
>
> Regards
> Rajesh kainikkara
>
> --
>
--
> 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/discussexcel
>



-- 
Thanks,
Irfan Ahmed Khan


 --
--
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/discussexcel



-- 
Regards
Rajan verma
+91 9158998701

-- 
--
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/discussexcel

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread STDEV(i) 
thank you mr sixthsense

may i modif the formula to be:

=DATE(YEAR(
DATEVALUE("1Jan"&RIGHT(B2,2))),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))



On Fri, May 13, 2011 at 6:03 PM, Sixthsense  wrote:

> Hi Stdev,
>
>
> Your Solution will fail when the person intended to mention the year 1998
> in this manner 10498.
>
>
> ---
> *Sixthsense
> **:) Man of Extreme & Innovative Thoughts :)*
>
>
> On Fri, May 13, 2011 at 4:13 PM, STDEV(i) wrote:
>
>> *
>> =DATE(2000+RIGHT(B2,2),MID(TEXT(B2,"00"),3,2),LEFT(TEXT(B2,"00"),2))
>> *
>>
>> see the attachment
>>
>>
>>
>> On Thu, May 12, 2011 at 7:14 PM, Prabhu  wrote:
>>
>>> Hi friends,
>>>
>>> Plz help to change date format when downloaded report from 1st of every
>>> month to 9th of the month will be like DMMYY .
>>>
>>> I have no issue in changing the format using text to column  from 10th
>>> date because it will be in DDMMYY.
>>>
>>> I have attached the sample sheet for your reference.Plz help to know the
>>> date format as DDMMYY for entire month.(i am requesting macros to do the
>>> same)
>>>
>>> Regards,
>>>
>>>  Pr
>>>
>>

-- 
--
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/discussexcel


ctv__bsninvreport(V2).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ How to change Date format

2011-05-13 Thread Sixthsense 
Hi Prabhu,


Try the below formula and format the cell as date.


=IF(AND(OR(LEN(TRIM(B2))=5,LEN(TRIM(B2))=6),ISNUMBER(B2)),IF(LEN(TRIM(B2))=5,--(--MID(TRIM(B2),2,2)&"-"&--LEFT(TRIM(B2),1)&"-"&--MID(TRIM(B2),4,2)),--(--MID(TRIM(B2),3,2)&"-"&--LEFT(TRIM(B2),2)&"-"&--MID(TRIM(B2),5,2))),"")


Herewith I have attached a sample file for your reference.


Hope that helps!


---
*Sixthsense
**:) Man of Extreme & Innovative Thoughts :)*


On Thu, May 12, 2011 at 5:44 PM, Prabhu  wrote:

> Hi friends,
>
> Plz help to change date format when downloaded report from 1st of every
> month to 9th of the month will be like DMMYY .
>
> I have no issue in changing the format using text to column  from 10th date
> because it will be in DDMMYY.
>
> I have attached the sample sheet for your reference.Plz help to know the
> date format as DDMMYY for entire month.(i am requesting macros to do the
> same)
>
> Regards,
>
> Prabhu
>
> --
>
> --
> 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/discussexcel
>

-- 
--
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/discussexcel


Completed - bsninvreport xls.xls
Description: MS-Excel spreadsheet