Re: $$Excel-Macros$$ Re: Query to retreive data

[Vaibhav Joshi]

you dont need access, you can continue using excel & thru DAO you can
retrieve data from closed workbook too..

On Wed, Apr 29, 2015 at 10:35 AM, Nasir Saikh  wrote:

> r8 Pramod access is very easy and taking very less time than excel on huge
> data you can use it till 2 GB data.
>
> On Tuesday, April 28, 2015 at 11:34:45 AM UTC+5:30, Mandeep Baluja wrote:
>
>> My mmaster file is large and i just want to do lookup on my sheet from
>> master file without opening my master file as it is of huge mb around 250
>> mb or more than that. any ideas
>>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query to retreive data

[Paul Schreiner]

I'm curious about your file size and response issue. What version of Excel are 
you using?As ask because I use Excel 2010 and have a file that is generated 
from 5 different data sources.The file contains 29 sheets,one of which contains 
145,500 rows and 74 columns.There are 27 macro modules containing 13,700 lines 
of VBA code. This, to me, seems to be a rather large file. If I save it as 
Excel97-2003 format (.xls) it is 83Mb,If I save it in Excel2010 macro format 
(.xlsm) format, it's 55Mb.If I save it in Excel2010 binary format (.xlsb), it's 
only 17Mb. (the .xlsb format runs MUCH faster) So, I'm curious as to why your 
file is 350mb.Does it contain graphics/images?Are there lots of formulas? 
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
 
  From: Mandeep Baluja 
 To: excel-macros@googlegroups.com 
 Sent: Tuesday, April 28, 2015 6:11 AM
 Subject: $$Excel-Macros$$ Re: Query to retreive data
   
Yes it works, Thanks for the same we can considered this issue is resolved. But 
one thing Excel is not useful for large files On Daily basis  I got files of 
350 mb having data of 2-3 lakh rows and I am unable to combine the same jjust 
because excel not responds. 



On Tuesday, April 28, 2015 at 3:10:42 PM UTC+5:30, Nasir Saikh wrote:
Hi Mandeep As per my suggestion you can use table link vloopup as I am using 
and it is working fine. Example is given below. Sub LoopUp()
Dim iRow As Integer
Dim MaxRow As Long
MaxRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\Users\ 
n0258542\Documents\[Aakash - Copy.xlsx]Input_Consolidated'! R1C2:R44C5,4,0)"
For iRow = 3 To MaxRow
    Selection.Offset(1, 0).Select
    Selection.FillDown
Next
End Sub
  
-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


   

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query to retreive data

[Pramod Singh]

Why not you are using MS Access. It's pretty cool and can we use easy..
this is best for if some one have to work in large data

Pramod
On 28 Apr 2015 15:41, "Mandeep Baluja"  wrote:

> Yes it works, Thanks for the same we can considered this issue is
> resolved. But one thing Excel is not useful for large files On Daily basis
>  I got files of 350 mb having data of 2-3 lakh rows and I am unable to
> combine the same jjust because excel not responds.
>
>
> On Tuesday, April 28, 2015 at 3:10:42 PM UTC+5:30, Nasir Saikh wrote:
>>
>> Hi Mandeep
>>
>> As per my suggestion you can use table link vloopup as I am using and it
>> is working fine. Example is given below.
>>
>> Sub LoopUp()
>> Dim iRow As Integer
>> Dim MaxRow As Long
>> MaxRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
>> Range("B2").FormulaR1C1 =
>> "=VLOOKUP(RC[-1],'C:\Users\n0258542\Documents\[Aakash -
>> Copy.xlsx]Input_Consolidated'!R1C2:R44C5,4,0)"
>> For iRow = 3 To MaxRow
>> Selection.Offset(1, 0).Select
>> Selection.FillDown
>> Next
>> End Sub
>>
>>
>>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query Regarding Duplicate and Update Status

[saravanan R]

On Wed, Mar 18, 2015 at 5:31 PM, saravanan R  wrote:

> Hi Mandeep,
>
> Thanks a Million.
>
> I have one more query related to the same file, is it possible to split
> the file based on "Status" OK wrt to each Coleague.
> and file name should be QueryDup-FK & QueryDup-BD (colleague name will be
> dyanmic)
>
> thanks in advance.
>
>
> On Wed, Mar 18, 2015 at 5:25 PM, Mandeep baluja 
> wrote:
>
>> Try this ..
>>
>>
>> Regards,
>> MandeepBaluja
>> Learningzmypassion.
>> https://www.facebook.com/VBAEXCELSQL?ref=hl
>> https://www.linkedin.com/profile/view?id=312532939
>>
>> On Wednesday, 18 March 2015 16:59:17 UTC+5:30, saravanan R wrote:
>>>
>>> Hello Team,
>>>
>>> I have an Query regarding Duplicate check for certain Criteria in
>>> filter. I need this  to be in VBA Code . Please see the details below and
>>> find the sample attached file with expected result mentioned.
>>>
>>> I have to filter for FK (Column "B"), Then find duplicate DOC.no for
>>> this colleague only. If the DOC no. is Duplicating then Update Status as
>>> "OK", if it is Unique then update as "NOT OK"
>>>
>>> thanks in advance,
>>>
>>> Saravanan R
>>> +91 8807708842
>>> +91 9449716334
>>>
>>  --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query Regarding Duplicate and Update Status

[saravanan R]

Hi Mandeep,

Thanks a Million.

I have one more query related to the same file, is it possible to split the
file based on "Status" OK wrt to each Coleague.
and file name should be QueryDup-FK & QueryDup-BD (colleague name will be
dyanmic)

thanks in advance.


On Wed, Mar 18, 2015 at 5:25 PM, Mandeep baluja 
wrote:

> Try this ..
>
>
> Regards,
> MandeepBaluja
> Learningzmypassion.
> https://www.facebook.com/VBAEXCELSQL?ref=hl
> https://www.linkedin.com/profile/view?id=312532939
>
> On Wednesday, 18 March 2015 16:59:17 UTC+5:30, saravanan R wrote:
>>
>> Hello Team,
>>
>> I have an Query regarding Duplicate check for certain Criteria in filter.
>> I need this  to be in VBA Code . Please see the details below and find the
>> sample attached file with expected result mentioned.
>>
>> I have to filter for FK (Column "B"), Then find duplicate DOC.no for this
>> colleague only. If the DOC no. is Duplicating then Update Status as "OK",
>> if it is Unique then update as "NOT OK"
>>
>> thanks in advance,
>>
>> Saravanan R
>> +91 8807708842
>> +91 9449716334
>>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query in IF formula on sheet deactivation event

[Ashish Bhalara]

Thanks Mr.Michael, it's work.

Regards.
Ashish Bhalara
9624111822
P*Please do not print this email unless it is absolutely necessary. Spread
environmental üawareness.♣♣♣*


On Sun, Mar 1, 2015 at 11:08 PM, michael vinoth 
wrote:

> Hi Ashish,
>
> Please find the attached macro file.
>
> Regards,
> MV
>
> On Saturday, February 28, 2015 at 5:04:35 PM UTC+5:30, Ashish_Bhalara
> wrote:
>>
>> Dear expert,
>>
>> I need help in attached file in which if cheque amount is greater than
>> zero and date & cheque number field Blank then msgbox should display, and
>> when cheque amount is blank, no any msgbox should display.
>>
>> In my case and as per my formula the msgbox display even cheque amt.
>> field is zero.
>>
>> Kindly see attached file to solve my query, I tried different way in
>> codding but did't get result.
>>
>> Regards.
>> Ashish Bhalara
>> 9624111822
>> P*Please do not print this email unless it is absolutely necessary.
>> Spread environmental üawareness.♣♣♣*
>>
>>   --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query on count

[Kal xcel]

Thank you all. It's really helped me a lot.

Thanks in once again

On Thu, Nov 13, 2014 at 6:24 PM, Mandeep Baluja 
wrote:

> Hey Kalyan,
>
> check this array formula and the
> sheet. 
> =COUNT(C4:R4)-COUNTIF(C4:R4,"<=0")-SUM(IF(ISNUMBER(SEARCH(".",C4:R4)),1,0))
>
> Regards,
> Mandeep Baluja
>
> On Thursday, November 13, 2014 5:12:11 PM UTC+5:30, Kal xcel wrote:
>>
>> Dear Experts,
>>
>> Please help me to solve my query.
>>
>> Thanks in advance
>>
>> --
>> *Kalyan Chattopadhyay*
>>
>>
>> *Executive Corporate MIS*
>>
>> *Joy Cosmetics*
>>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
*Kalyan Chattopadhyay*


*Executive Corporate MIS*

*Joy Cosmetics*

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Query on Text to Columns function

[De Premor]

Try this code

Sub Macro1()
Dim Data As Range
Application.DisplayAlerts = False
Set Data = Range("C2", Range("C2").End(xlDown).Address)
Data.TextToColumns Data.Offset(, 1), OtherChar:=".", 
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))

Application.DisplayAlerts = True
End Sub



On 01-09-2013 1:45, PoojaSharma wrote:
Thanks Lalit for the solution, though the solution to my query is 
quite simpler..while using text to column on the very next step 
choose text for each column within text to column window.

Regards  Pooja Sharma
Sent from my iPad

On Aug 29, 2013, at 2:09 PM, Lalit Mohan Pandey 
mailto:mohan.pande...@gmail.com>> wrote:



Try this

Option Explicit

Sub SplitValues()

Dim lngLoop   As Long
Dim vararrRawData() As Variant
Dim vararrSplitData As Variant
'Const variable change accordingly as per your requirement
Const strDataRangeToSplit   As String = "C1:C65536"
Const strDataShtNameAs String = "Sheet1"
Const strDataOutputShtName  As String = "Sheet1"
Const strDataOutputCell As String = "J1"
With ThisWorkbook
With .Worksheets(strDataShtName)
vararrRawData = .Range(strDataRangeToSplit).Value
End With
With .Worksheets(strDataOutputShtName)
For lngLoop = LBound(vararrRawData) To UBound(vararrRawData)
.Range(strDataOutputCell).Offset(lngLoop - 1, 0).Value = 
vararrRawData(lngLoop, 1)
vararrSplitData = Split(Replace(vararrRawData(lngLoop, 1), ".", 
".'"), ".")

If UBound(vararrSplitData) = 0 Then
vararrSplitData(0) = vbNullString
End If
.Range(strDataOutputCell).Offset(lngLoop - 1, 1).Resize(, 
UBound(vararrSplitData) + 1).Value = vararrSplitData

Next lngLoop
End With
End With
lngLoop = Empty
Erase vararrRawData
vararrSplitData = Empty

End Sub


On Thursday, 29 August 2013 13:32:17 UTC+5:30, Pooja wrote:

Hi All,

Please advice on the below query.

Regards
Pooja Sharma
Sent from BlackBerry® on Airtel

*From: * Pooja Sharma >
*Date: *Thu, 29 Aug 2013 13:19:08 +0530
*To: *>
*Subject: *Query on Text to Columns function

Hi All,

I am seeking for your support on one of my file (attached),
wherein I have to separate the values in different columns, the
separator (Delimitor) value is the "." sign.

I was trying to do this with the function text to columns, but it
removes the zero value and I need these 0 values too.

Please advice.


--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? 
It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel


FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like 
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security 
measure.

4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE : Don't ever post confidential data in a workbook. Forum owners 
and members are not responsible for any loss.

---
You received this message because you are subscribed to the Google 
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to excel-macros+unsubscr...@googlegroups.com.

To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.


--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this gro

Re: $$Excel-Macros$$ Re: Query

[Aamir Shahzad]

yes you are right but I am not understanding why formula generating error,
I want to know the reason? can you investigate.

Aamir Shahzad

On Fri, Sep 28, 2012 at 5:41 PM, Don Guillett  wrote:

>
>
> On Friday, September 28, 2012 7:24:58 AM UTC-5, Aamir Shahzad wrote:
>>
>> Dear Group,
>>
>> When I mannualy enter the "C7" into the following formula, it has no
>> problem,
>>
>> =SUMPRODUCT(($H$2:$H$9=$H13)*(*C7*))
>>
>> but when I am trying to generate C7, it's given me #value error, however
>> address function is creating the C7 ok seperaterly.
>>
>>
>> =SUMPRODUCT(($H$2:$H$9=$H12)*(ADDRESS(MATCH($H12,$H$2:$H$9,0)+1,COLUMN(C2
>>
>> Sample file is also attached, Please suggest.
>> Regards,
>>
>> Aamir Shahzad
>> First, use a MEANINGFUL subject line like " sumproduct question"
>
>
> Methinks you are making it much harder than necessary. Copy this down and
> across
>  '=INDEX(C:C,MATCH($H12,$H:$H))
>
>
>
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>



-- 

Regards,
Aamir Shahzad

-- 
Join official facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES (1120+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

6) Jobs posting is not allowed.

7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.

Re: $$Excel-Macros$$ Re: Query- Global Variable

[noorain . ansari]

Dear Hemant,

Pls declear variable as Public and it will declear out of pocedure

Like
Public abc as string
Sub A()
Abc="ram"
Msg abc
End sub

Sub B()
Abc="rahim"
Msg abc
End sub
Sent from BlackBerry® on Airtel

-Original Message-
From: Excel Vba 
Sender: excel-macros@googlegroups.com
Date: Fri, 13 Jul 2012 18:24:57 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Query- Global Variable

Hi Group,

Is it good practise to declare a global variable.
But, when i use global variable in loop it performs better.

Regards,
excelvba.lear...@gmail.com

-
On Fri, Jul 13, 2012 at 4:51 PM, Excel Vba wrote:

> Hi Group,
>
>
> How can i declare a global variable for below mentioned.
>
>
>
> Private sub abc()
> dim sh as worksheet
> dim rng as range
>
> set sh = Thisworkbook.Sheets("Sheet1")
> set rng = sh.range("a1:c"&sh.range("c"&rows.count).end(xlup).row)
>
> End sub
>
>
> In my module i have 20 procedure and in future comes many more.
> So, my query how to declare a global variable and how to call in my
> procedure.
>
> Regards,
> excelvba.lear...@gmail.com
>
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

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

To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com

Re: $$Excel-Macros$$ Re: Query

[Venkat CV]

Dear Shasi,

You are Welcome...

*Best Regards,*
*Venkat *
*Chennai*
*My Linked in profile *

On Sun, Sep 4, 2011 at 8:37 AM, Shashi Ranjan  wrote:

> Dear Venkat & Noorain Sir
>
> I am very thankful to you for sharing your precipus time helping me solve
> this task.
>
> Thanks & Regards
> Shashi Ranjan
>
>
>
> On 1 September 2011 15:09, NOORAIN ANSARI wrote:
>
>> Dear Shashi,
>>
>> Please see attached sheet if it help to u...
>>
>> On Thu, Sep 1, 2011 at 2:22 PM, shashi  wrote:
>>
>>> Dear Venkat
>>>
>>> I have second condtion that i want payout amount datewise. I want an
>>> urgent help on condition function. In this data i have the requirement
>>> of *Payout amount* for each branch in datewise without using
>>>
>>> On Aug 31, 10:15 pm, shashi  wrote:
>>> > Dear Excel Guru's
>>> >
>>> > I want an urgent help on condition function. In this data i have the
>>> > requirement of *Payout amount* for each branch in datewise without
>>> using
>>> > pivot. Please help me in this query.
>>> >
>>> > Thanks
>>> > Regards
>>> > Shashi Ranjan
>>> >
>>> >  Consolidation RI payout Data.xls
>>> > 1512KViewDownload
>>>
>>> --
>>>
>>> --
>>> 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 & regards,
>> Noorain Ansari
>> *http://noorain-ansari.blogspot.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/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
>



-- 
*
*
*
*
*
*

-- 
--
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$$ Re: Query

[Shashi Ranjan]

Dear Venkat & Noorain Sir

I am very thankful to you for sharing your precipus time helping me solve
this task.

Thanks & Regards
Shashi Ranjan



On 1 September 2011 15:09, NOORAIN ANSARI  wrote:

> Dear Shashi,
>
> Please see attached sheet if it help to u...
>
> On Thu, Sep 1, 2011 at 2:22 PM, shashi  wrote:
>
>> Dear Venkat
>>
>> I have second condtion that i want payout amount datewise. I want an
>> urgent help on condition function. In this data i have the requirement
>> of *Payout amount* for each branch in datewise without using
>>
>> On Aug 31, 10:15 pm, shashi  wrote:
>> > Dear Excel Guru's
>> >
>> > I want an urgent help on condition function. In this data i have the
>> > requirement of *Payout amount* for each branch in datewise without using
>> > pivot. Please help me in this query.
>> >
>> > Thanks
>> > Regards
>> > Shashi Ranjan
>> >
>> >  Consolidation RI payout Data.xls
>> > 1512KViewDownload
>>
>> --
>>
>> --
>> 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 & regards,
> Noorain Ansari
> *http://noorain-ansari.blogspot.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/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$$ Re: Query

[Kal xcel]

Thanks Ashish

Thanks Ankur

On Thu, Jul 7, 2011 at 11:51 AM, ankur  wrote:

> *
> *Have A Nice Time & Enjoy Life
>
> Regards:
> CMA Ankur Pandey
> (Someone Different)
>
> I'm not the best but i'm not like the rest~~
>
>
>   On Wed, Jul 6, 2011 at 6:05 PM, ankur  wrote:
>
>> *hi ashish sir
>> may this help u ...
>> *Have A Nice Time & Enjoy Life
>>
>> Regards:
>> CMA Ankur Pandey
>> (Someone Different)
>>
>> I'm not the best but i'm not like the rest~~
>>
>>
>>
>> On Wed, Jul 6, 2011 at 5:30 PM, ashish koul wrote:
>>
>>>
>>>
>>> On Wed, Jul 6, 2011 at 4:56 PM, Kal xcel  wrote:
>>>
 Dear Experts,

 For this chart

 3001-5000 = 30
 5001-6000 = 40
 6001-7000 = 45
 7001-8000 = 50
 8001-9000 = 50
 9001-15000 = 110
 15001-25000 = 130
 25001-4 = 150
 40001>= =200

 I use this formula:



 =IF(AND(D16>=3001,D16<=5000),30,IF(AND(D16>=5001,D16<=6000),40,IF(AND(D16>=6001,D16<=7000),45,IF(AND(D16>=7001,D16<=8000),50,IF(AND(D16>=8001,D16<=9000),90,IF(AND(D16>=9001,D16<=15000),110,IF(AND(D16>=15001,D16<=25000),130,IF(AND(D16>=25001,D16<=4),150,IF(AND(D16>=40001),200,0)

 Is their any other short/intelligent way to get desired result?

 Please help

 Thanks in advance

 Kalyan

>>>
>>>
>>>
>>> --
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *akoul*.*blogspot*.com 
>>> http://akoul.posterous.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
>

-- 
--
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$$ Re: Query

[ankur]

*
*Have A Nice Time & Enjoy Life

Regards:
CMA Ankur Pandey
(Someone Different)

I'm not the best but i'm not like the rest~~


On Wed, Jul 6, 2011 at 6:05 PM, ankur  wrote:

> *hi ashish sir
> may this help u ...
> *Have A Nice Time & Enjoy Life
>
> Regards:
> CMA Ankur Pandey
> (Someone Different)
>
> I'm not the best but i'm not like the rest~~
>
>
>
> On Wed, Jul 6, 2011 at 5:30 PM, ashish koul  wrote:
>
>>
>>
>> On Wed, Jul 6, 2011 at 4:56 PM, Kal xcel  wrote:
>>
>>> Dear Experts,
>>>
>>> For this chart
>>>
>>> 3001-5000 = 30
>>> 5001-6000 = 40
>>> 6001-7000 = 45
>>> 7001-8000 = 50
>>> 8001-9000 = 50
>>> 9001-15000 = 110
>>> 15001-25000 = 130
>>> 25001-4 = 150
>>> 40001>= =200
>>>
>>> I use this formula:
>>>
>>>
>>>
>>> =IF(AND(D16>=3001,D16<=5000),30,IF(AND(D16>=5001,D16<=6000),40,IF(AND(D16>=6001,D16<=7000),45,IF(AND(D16>=7001,D16<=8000),50,IF(AND(D16>=8001,D16<=9000),90,IF(AND(D16>=9001,D16<=15000),110,IF(AND(D16>=15001,D16<=25000),130,IF(AND(D16>=25001,D16<=4),150,IF(AND(D16>=40001),200,0)
>>>
>>> Is their any other short/intelligent way to get desired result?
>>>
>>> Please help
>>>
>>> Thanks in advance
>>>
>>> Kalyan
>>>
>>
>>
>>
>> --
>> *Regards*
>> * *
>> *Ashish Koul*
>> *akoul*.*blogspot*.com 
>> http://akoul.posterous.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

Re: $$Excel-Macros$$ Re: Query

[ankur]

*hi ashish sir
may this help u ...
*Have A Nice Time & Enjoy Life

Regards:
CMA Ankur Pandey
(Someone Different)

I'm not the best but i'm not like the rest~~


On Wed, Jul 6, 2011 at 5:30 PM, ashish koul  wrote:

>
>
> On Wed, Jul 6, 2011 at 4:56 PM, Kal xcel  wrote:
>
>> Dear Experts,
>>
>> For this chart
>>
>> 3001-5000 = 30
>> 5001-6000 = 40
>> 6001-7000 = 45
>> 7001-8000 = 50
>> 8001-9000 = 50
>> 9001-15000 = 110
>> 15001-25000 = 130
>> 25001-4 = 150
>> 40001>= =200
>>
>> I use this formula:
>>
>>
>>
>> =IF(AND(D16>=3001,D16<=5000),30,IF(AND(D16>=5001,D16<=6000),40,IF(AND(D16>=6001,D16<=7000),45,IF(AND(D16>=7001,D16<=8000),50,IF(AND(D16>=8001,D16<=9000),90,IF(AND(D16>=9001,D16<=15000),110,IF(AND(D16>=15001,D16<=25000),130,IF(AND(D16>=25001,D16<=4),150,IF(AND(D16>=40001),200,0)
>>
>> Is their any other short/intelligent way to get desired result?
>>
>> Please help
>>
>> Thanks in advance
>>
>> Kalyan
>>
>
>
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com 
> http://akoul.posterous.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


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

Re: $$Excel-Macros$$ Re: query for a solution

[ankur]

hi
Prakash Singh Gusain and others experts

thanks for your supportu all provide very helpful solution.
thnks again
Have A Nice Time & Enjoy Life

Regards:
CMA Ankur Pandey
(Someone Different)

I'm not the best but i'm not like the rest~~



On Thu, Feb 10, 2011 at 3:53 PM, Prakash Gusain  wrote:
> Hi Ankur,
>
> Please find the attached file for solution, I know you have already got the
> solution from other experts.  The only different thing you will find is
> there NO NEED TO SORT DATA AS WELL! no matter were your unique id is in the
> column it will give you the solution.
>
> The solution provided by Expert Umesh lacks this feature!
>
> Hope it made some contribution to learn something new as well :)
> --
> Best Regards!
> Prakash Singh Gusain
>
> Please visit my blog (click here)
>
> --
> --
> 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$$ Re: Query Excel File

[Upendra Singh Sengar]

Hi Ayush,

This doesn't help. I've got the correct code:
"Select Distinct [Market] from [Sheet1$A1:D5000]"
or
"SELECT DISTINCT [Column Heading] FROM TableName ORDER BY [ColumnHeading];",



(¨`·.·´¨) Keep
`·.¸(¨`·.·´¨) Smiling !!
(¨`·.·´¨)¸.·´ Upendra Singh
`·.¸.·´9910227325


On 23 December 2010 21:37, Ayush  wrote:

> Dear Upendra,
>
> Well, if your code is working fine to query excel fileYou can use
> "Select distinct * from [RECEIVE$] " to pull distinct data from the column.
>
> Not sure if it will help you. Write back in case you need something else.
>
> 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=wall&ref=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=wall&ref=ts

Re: $$Excel-Macros$$ Re: Query - Date Formula

[Nikhil Shah]

Dear Friend

Thanks For Help.I have Found Data Validation Function.

My Excel Sheet is now working find..

against Thanks

Regards
Nikhil Shah

On Sat, Jul 10, 2010 at 4:51 PM, saggi  wrote:

>
> Dear Nikhil,
>
> Use Data Validation  for reflect the error
>
> --
>
> --
> 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
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>

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

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe