Re: $$Excel-Macros$$ Vlookup basics and help
Don, It did not return any values On Wed, Mar 7, 2012 at 4:56 PM, dguillett1 wrote: > Modify to suit > > =IF(ISNA(VLOOKUP(A26,Maindata!$A$2:$I$39,3,0)),"",VLOOKUP(A26,Maindata!$A$2:$I$39,3,0)) > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > *From:* Skanda > *Sent:* Wednesday, March 07, 2012 11:31 AM > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Vlookup basics and help > > > > On Wed, Mar 7, 2012 at 11:04 AM, NOORAIN ANSARI > wrote: > >> Dear Skanda, >> >> Plase share sample workbook with group >> >> -- >> Thanks & regards, >> Noorain Ansari >> ** <http://excelmacroworld.blogspot.com/>*http://noorainansari.com/* >> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> >> On Wed, Mar 7, 2012 at 10:23 PM, Skanda wrote: >> >>> I have two worksheets subset and Maindata. >>> >>> Subset has the following field names:TDM_CL_MASTER_KEY Product >>> Claim_Number Insured Primary_Excess Claimant Claim_Handler >>> >>> Maindata has the following field names:TDM_CL_MASTER_KEY Profit_Center >>> Product Claim_Number Insured Policy_Number Primary_Excess Claimant >>> Claim_Handler >>> >>> >>> How to: >>> We have claim_number in Subset worksheet, use that to pull policy_number >>> and profit_center from Maindata worksheet and also compare >>> compare the "insured " from both the tabs? >>> >>> -- >>> 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 >>> >> >> >> >> >> >> -- >> 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 >> > > -- > 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 > > -- > FORUM RULES (986+ members
Re: $$Excel-Macros$$ Vlookup basics and help
On Wed, Mar 7, 2012 at 11:04 AM, NOORAIN ANSARI wrote: > Dear Skanda, > > Plase share sample workbook with group > > -- > Thanks & regards, > Noorain Ansari > *http://noorainansari.com/* <http://excelmacroworld.blogspot.com/> > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > On Wed, Mar 7, 2012 at 10:23 PM, Skanda wrote: > >> I have two worksheets subset and Maindata. >> >> Subset has the following field names:TDM_CL_MASTER_KEY Product >> Claim_Number Insured Primary_Excess Claimant Claim_Handler >> >> Maindata has the following field names:TDM_CL_MASTER_KEY Profit_Center >> Product Claim_Number Insured Policy_Number Primary_Excess Claimant >> Claim_Handler >> >> >> How to: >> We have claim_number in Subset worksheet, use that to pull policy_number >> and profit_center from Maindata worksheet and also compare >> compare the "insured " from both the tabs? >> >> >> -- >> 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 >> > > > > > > -- > 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 > -- 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 vlookup.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Vlookup basics and help
Yes On Wed, Mar 7, 2012 at 11:04 AM, dguillett1 wrote: > vlookup?? > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > *From:* Skanda > *Sent:* Wednesday, March 07, 2012 10:53 AM > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ Vlookup basics and help > > I have two worksheets subset and Maindata. > > Subset has the following field names:TDM_CL_MASTER_KEY Product > Claim_Number Insured Primary_Excess Claimant Claim_Handler > > Maindata has the following field names:TDM_CL_MASTER_KEY Profit_Center > Product Claim_Number Insured Policy_Number Primary_Excess Claimant > Claim_Handler > > > How to: > We have claim_number in Subset worksheet, use that to pull policy_number > and profit_center from Maindata worksheet and also compare > compare the "insured " from both the tabs? > > -- > 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 > > -- > 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 > -- 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
$$Excel-Macros$$ Vlookup basics and help
I have two worksheets subset and Maindata. Subset has the following field names:TDM_CL_MASTER_KEY Product Claim_Number Insured Primary_Excess Claimant Claim_Handler Maindata has the following field names:TDM_CL_MASTER_KEY Profit_Center Product Claim_Number Insured Policy_Number Primary_Excess Claimant Claim_Handler How to: We have claim_number in Subset worksheet, use that to pull policy_number and profit_center from Maindata worksheet and also compare compare the "insured " from both the tabs? -- 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
Re: $$Excel-Macros$$ Meaning of minus minus or -- in an Excel function
Asa, Thank you very much for taking sometime and explaining the concept for us. Skanda. On Fri, Feb 24, 2012 at 3:00 AM, Asa Rossoff wrote: > Hi Skanda, > > I don't know much about SAS, but here is an example of a common use of --* > *** > > =SUMPRODUCT(--(Customer[City]="Paris"),--(Customer[Country]<>"France"))*** > * > > The above uses an Excel 2010 Table notation. You can replace > "Customer[City]" and "Customer[Country]" with the data range in columns > containing the City and Country in a list of Customers. > > ** ** > > The formula actually counts the number of records that meet both criteria, > because each criterion is evaluated to 0 or 1 and then multiplied by the > other for each record, resulting in a 0 unless both criteria are met, when > it will be 1. Then the results for all the records are added together.*** > * > > ** ** > > If you want to know, say, the total sales for that set of customers: > > > =SUMPRODUCT(--(Customer[City]="Paris"),--(Customer[Country]<>"France"),Customer[Sales]) > > > will work because the 0 or 1 for each record will then be multiplied by > the Sales amount before being added to other record's results. > > ** ** > > ** ** > > In Excel 2007+ the basic examples I just gave could be achieved more > efficiently (quicker calculation) using COUNTIFS and SUMIFS, which wouldn't > require the double negative. > > ** ** > > I'll also throw in to my explanations that sometimes it's useful to not > convert all booleans to numbers, and take advantage of the fact that > booleans are ignored by aggregate functions. > > ** ** > > Take the MEDIAN and AVERAGE functions, for example. Including 0 values > for records not of interest will skew the results. Instead, you can handle > the boolean values using IF(boolean,value to aggregate) (you can omit the > value-if-false part because IF will just return a boolean False when it's > omited, which will then be disregarded by the aggregate function as if > there was no value at all): > > > =MEDIAN(IF((Customer[City]="Paris")*(Customer[Country]<>"France")*NOT(ISBLANK(Customer[Sales])),Customer[Sales])) > > > ** ** > > The attached files demonstrate. The xlsx version has both 2010 table > reference and traditional range reference versions of the formulas; the xls > version just has range versions. > > ** ** > > Asa > > ** ** > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Skanda > *Sent:* Thursday, February 23, 2012 9:10 AM > > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Meaning of minus minus or -- in an Excel > function > > ** ** > > I'm from SAS background. Can anybody please provide an example for the > following scenario. > > > > On Thu, Feb 23, 2012 at 11:02 AM, Sam Mathai Chacko > wrote: > > I know I mentioned otherwise in > http://osdir.com/ml/excel-macros/2011-12/msg00084.html > > However,* you cannot use ++* > > You can use +0, *1, -0, /1, ^1 etc, but -- is the most elegant and common > approach. > > Regards, > > Sam Mathai Chacko > > ** ** > > On Thu, Feb 23, 2012 at 12:21 PM, Asa Rossoff wrote:** > ** > > Hi Johann, > > To add to Noorain's explanation: > > > > Boolean (True/False) values cannot be added, multiplied, averaged, etc. by > aggregate functions such as SUM(), PRODUCT(), AVERAGE(), MEDIAN(), and > SUMPRODUCT. In fact, boolean values are always completely ignored by > aggregate functions. > > > > To summarize boolean values with an aggregate function, you must convert > the the boolean to a number. When you use a value in a formula as if it > were a different type of data, Excel will in most circumstances convert the > value to that type of data. This implicit method of data type conversion > is called coercion. You can coerce boolean values to a number, text > representations of a number to a number, textual dates/times to actual > date/times (dateserials), anything to text,… > > > > Boolean values can be coerced to a number (1 for True, 0 for False) in > several ways. If you use an arithmetic operator with a boolean value, the > boolean is coerced to become a number. In recent versions of Excel, the > N() function performs an explicit conversion to a number and works in many > circumstances (although arithmetic coercion works more broadly). For > completeness there is also
Re: $$Excel-Macros$$ Meaning of minus minus or -- in an Excel function
I'm from SAS background. Can anybody please provide an example for the following scenario. On Thu, Feb 23, 2012 at 11:02 AM, Sam Mathai Chacko wrote: > I know I mentioned otherwise in > http://osdir.com/ml/excel-macros/2011-12/msg00084.html > > However,* you cannot use ++* > > You can use +0, *1, -0, /1, ^1 etc, but -- is the most elegant and common > approach. > > Regards, > > Sam Mathai Chacko > > > On Thu, Feb 23, 2012 at 12:21 PM, Asa Rossoff wrote: > >> Hi Johann, >> >> To add to Noorain's explanation: >> >> ** ** >> >> Boolean (True/False) values cannot be added, multiplied, averaged, etc. >> by aggregate functions such as SUM(), PRODUCT(), AVERAGE(), MEDIAN(), and >> SUMPRODUCT. In fact, boolean values are always completely ignored by >> aggregate functions. >> >> ** ** >> >> To summarize boolean values with an aggregate function, you must convert >> the the boolean to a number. When you use a value in a formula as if it >> were a different type of data, Excel will in most circumstances convert the >> value to that type of data. This implicit method of data type conversion >> is called coercion. You can coerce boolean values to a number, text >> representations of a number to a number, textual dates/times to actual >> date/times (dateserials), anything to text,… >> >> ** ** >> >> Boolean values can be coerced to a number (1 for True, 0 for False) in >> several ways. If you use an arithmetic operator with a boolean value, the >> boolean is coerced to become a number. In recent versions of Excel, the >> N() function performs an explicit conversion to a number and works in many >> circumstances (although arithmetic coercion works more broadly). For >> completeness there is also the similar VALUE function which can convert >> most data types to numbers, but not booleans. >> >> ** ** >> >> When you don't need to use an arithmetic operator in your formula >> already, but you need to coerce a boolean value, you have to device a >> neutral expression that includes an arithmetic operator, like --boolean, >> boolean*1, boolean+0, boolean/1. -- is often favored. Your formula is >> still clean, the -- form would probably not be used in other circumstances, >> and it's a fast calculation for Excel. >> >> ** ** >> >> Usually the requirement to utilize boolean values as numbers comes up in >> array formulas and SUMPRODUCT formulas (similar because SUMPRODUCT resolves >> array formulas too). Numeric representations of boolean values is useful >> in these formulas because when multiplied by another value, that value can >> either be included in a summary when the boolean is True/1, or not, when >> False/0. That boolean value or expression has become a criteria for what >> to summarize. >> >> ** ** >> >> Asa >> >> ** ** >> >> *From:* excel-macros@googlegroups.com [mailto: >> excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI >> *Sent:* Wednesday, February 22, 2012 7:06 PM >> *To:* excel-macros@googlegroups.com >> *Subject:* Re: $$Excel-Macros$$ Meaning of minus minus or -- in an Excel >> function >> >> ** ** >> >> Dear johann, >> >> >> >> The -- is used as an unary operator to convert a boolean value ie, >> TRUE/FALSE in to 1/0. Sumproduct for example does not evaluate non-numeric >> values that results in a formula like the boolean results in the array >> equation A1:A10="johann". >> >> So to negate this, we use unary operator -- with TRUE or FALSE to give 1 >> or 0 >> >> We can also use ++ ,+0,*1 >> >> In fact you can also use a multiplier or a divisor of 1 >> >> In case you'd like more information, look up in google. Here's something >> from an Excel MVP >> >> http://www.mcgimpsey.com/excel/formulae/doubleneg.html >> >> On Thu, Feb 23, 2012 at 5:24 AM, johann wrote: >> >> Hi Cyberspace, >> >> I'd like to understand the meaning of "--" in front of an Excel >> function like : >> =--sumproduct(...) >> =--substitute(...) >> ... and other I can't recall >> >> I know that in some programming language it's a way to "decrement" a >> variable like i = i - 1 can be i-- or --i following the precedence of >> the operator --. >> >> Thanks, >> Cyberuser >> >> -- >> 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. >> >> >> --
Re: $$Excel-Macros$$ Truncate after 16 digits
Thank you.Will do that. On Thu, Nov 17, 2011 at 12:36 AM, Asa Rossoff wrote: > Hi Skanda, > > =trunc(-3.89811069564618E-07,16) > > ** ** > > But with General number format, Excel will still display with scientific > notation. > > Change the number format to overcome that. > > ** ** > > If your concern was for display to begin with, you can pretty much just > change the number format… but in that case, Excel will round the final > decimal place instead or truncating to it when displayed. > > ** ** > > Asa > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Skanda > *Sent:* Wednesday, November 16, 2011 9:41 AM > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ Truncate after 16 digits > > ** ** > > There are values like this:-3.89811069564618E-07 > > > > How to truncate after 16 digits after the decimal? > > -- > FORUM RULES (934+ 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 > > -- > FORUM RULES (934+ 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 > -- FORUM RULES (934+ 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
$$Excel-Macros$$ Truncate after 16 digits
There are values like this:-3.89811069564618E-07 How to truncate after 16 digits after the decimal? -- FORUM RULES (934+ 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
$$Excel-Macros$$ Decimal greater than 0.5
Hi All, How to convert the numbers in a column based on following: example: 13.45 should be displayed as 13 13.5 or 13.51(decimal equal or greater than .5) should be 14. -- -- 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$$ Pad zeros
Thank you all.it works like a charm!! On Fri, Jun 17, 2011 at 7:28 AM, Rakesh Joshi wrote: > instead of using below formula you can use. > this formula it will work in any cell and for any value. > *=REPT(0,LEN(B4)+(2)-LEN(B4))&B4 > > * > On Fri, Jun 17, 2011 at 10:46 AM, Venkatesan c wrote: > >> Hi Skanda, >> >> Please use Below formula for our query. >> =REPT(0,(9-LEN(B4)))&B4 >> >> *Best Regards,* >> *Venkat* >> >> On Fri, Jun 17, 2011 at 3:46 AM, Skanda wrote: >> >>> Hi, >>> How to add zeros in front of a value with varying lengths so that the >>> length of the column is 9? >>> for instance a column policy id has values with varying lengths as >>> follows: >>> >>> Policyid: >>> 745689 >>> 2346789 >>> >>> >>> so i need to transform the above policyid to: >>> 000745689 >>> 002346789 >>> >>> >>> -- >>> >>> -- >>> 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 >>> >> >> >> >> -- >> *Best Regards,* >> *Venkat* >> >> >> -- >> >> -- >> 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 >> > > > > -- > Rakesh Joshi > -- -- 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$$ Pad zeros
Hi, How to add zeros in front of a value with varying lengths so that the length of the column is 9? for instance a column policy id has values with varying lengths as follows: Policyid: 745689 2346789 so i need to transform the above policyid to: 000745689 002346789 -- -- 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$$ Dynamic file name and sum
Vasant,
Can I run the following as is or do I have to make changes to the path etc?
I tried running as is,and I'm getting an error Previous Month File does not
exist. I saved both files on desktop.
Sub UpdateFormula2()
Dim Flnm As String, Mn As String, Yr As Integer, PrevMn As String
Dim FlPath As String, WkBk As Workbook, GetPrevMnthFile As String
Flnm = ThisWorkbook.Name
Mn = Mid(Flnm, 12, 3)
Yr = Mid(Flnm, 15, 4)
PrevMn = WorksheetFunction.Choose(Month(DateAdd("m", -1, DateValue("01-" &
Mn & "-" & Yr))), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec")
If PrevMn = "Dec" Then
Yr = Yr - 1
End If
GetPrevMnthFile = "DDD_REPORT_" & PrevMn & Trim(Str(Yr)) & ".xls"
FlPath = ThisWorkbook.Path & "\" & GetPrevMnthFile
If IsFileExists(FlPath) Then
ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
"=SUM(prod!J2:J9)-SUM('" & ThisWorkbook.Path & "\[" & GetPrevMnthFile &
"]prod'!$I$2:$I$9)"
else
msgbox "Previous Month File does not exist !"
End If
End Sub
' Function to check if file exists in the folder
Function IsFileExists(Flnm
As String) As Boolean
On Error Resume Next
If Not Dir(Flnm, vbDirectory) = vbNullString Then IsFileExists = True
On Error GoTo 0
End Function
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Dynamic file name and sum
Vasant,
I will try this out and let you know.
Thanks for the quick around.
On Fri, Jun 3, 2011 at 1:12 AM, Vasant wrote:
> There are many approaches of doing it.
>
> I have listed two here..
>
> Pass the month and year as parameters to the - 'UpdateFormula' subroutine.
> You can pass the parameters either through a worksheet change event or a
> userform.
>
> 'Trigger on worksheet change event in Memo sheet (all the workbooks has to
> be opened)
> '--
> Private Sub Worksheet_Change(ByVal Target As Range)
> If ThisWorkbook.Worksheets("Memo").Range("J2") <> "" And
> ThisWorkbook.Worksheets("Memo").Range("J3") <> "" Then
> Call UpdateFormula(ThisWorkbook.Worksheets("Memo").Range("J2"),
> ThisWorkbook.Worksheets("Memo").Range("J3"))
> End If
> End Sub
>
>
> Sub UpdateFormula(Mn, Yr)
> Flnm = "DDD_Report_" & Mn & "_" & Trim(Str(Yr)) & ".xls"
> ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
> "=SUM(prod!J2:J9)-SUM('E:\[" & Flnm & "]prod'!$I$2:$I$9)"
> End Sub
>
>
>
> The other is : -
> this approach will work if the workbook names are standardized.
> ie. DDD_REPORT_APR2011.xls, DDD_REPORT_MAR2011.xls and so on.
>
> the below code gets the previous month file name using the current month
> workbook name and then updates the formula in the cell if the previous month
> file is available in the folder.
>
>
> Sub UpdateFormula2()
> Dim Flnm As String, Mn As String, Yr As Integer, PrevMn As String
> Dim FlPath As String, WkBk As Workbook, GetPrevMnthFile As String
> Flnm = ThisWorkbook.Name
> Mn = Mid(Flnm, 12, 3)
> Yr = Mid(Flnm, 15, 4)
> PrevMn = WorksheetFunction.Choose(Month(DateAdd("m", -1, DateValue("01-" &
> Mn & "-" & Yr))), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
> "Sep", "Oct", "Nov", "Dec")
> If PrevMn = "Dec" Then
> Yr = Yr - 1
> End If
> GetPrevMnthFile = "DDD_REPORT_" & PrevMn & Trim(Str(Yr)) & ".xls"
> FlPath = ThisWorkbook.Path & "\" & GetPrevMnthFile
> If IsFileExists(FlPath) Then
> ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
> "=SUM(prod!J2:J9)-SUM('" & ThisWorkbook.Path & "\[" & GetPrevMnthFile &
> "]prod'!$I$2:$I$9)"
> else
> msgbox "Previous Month File does not exist !"
> End If
> End Sub
>
> ' Function to check if file exists in the folder
> Function IsFileExists(Flnm
> As String) As Boolean
> On Error Resume Next
> If Not Dir(Flnm, vbDirectory) = vbNullString Then IsFileExists = True
> On Error GoTo 0
> End Function
>
> Hope this helps
>
> Regards
> Vasant
>
>
>
>
> On Thu, Jun 2, 2011 at 6:55 PM, Skanda wrote:
>
>> Vasant,I'm seeking help to write a macro.
>>
>>
>> On Thu, Jun 2, 2011 at 12:23 AM, Vasant wrote:
>>
>>> A crude way of doing it is using the indirect function (though not
>>> advisable), on condition that the file name is updated in C13 cell and file
>>> is open.
>>> Generally Indirect is not used outside a workbook
>>>
>>> =SUM(prod!J2:J9)-SUM(INDIRECT("["&C13&"]prod!$I$2:$I$9"))
>>>
>>> The other way round is writing a macro.
>>>
>>>
>>>
>>> On Wed, Jun 1, 2011 at 7:00 PM, Skanda wrote:
>>>
>>>> vasant everymonth we have to change the file name.i.e MAR_2011
>>>>
>>>> On Wed, Jun 1, 2011 at 4:58 AM, Vasant wrote:
>>>>
>>>>> Hi Skanda,
>>>>>
>>>>>
>>>>> =SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9)
>>>>>
>>>>> Hope this helps
>>>>>
>>>>> Regards
>>>>> Vasant
>>>>>
>>>>> On Wed, Jun 1, 2011 at 12:15 AM, Skanda wrote:
>>>>>
>>>>>> There are two excel workbooks:DDD_REPORT_APR2011.xls and
>>>>>> DDD_REPORT_MAR2011.xls.
>>>>>>
>>>>>> In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4
>>>>>> in memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column)
>>>&
Re: $$Excel-Macros$$ Dynamic file name and sum
Vasant,I'm seeking help to write a macro.
On Thu, Jun 2, 2011 at 12:23 AM, Vasant wrote:
> A crude way of doing it is using the indirect function (though not
> advisable), on condition that the file name is updated in C13 cell and file
> is open.
> Generally Indirect is not used outside a workbook
>
> =SUM(prod!J2:J9)-SUM(INDIRECT("["&C13&"]prod!$I$2:$I$9"))
>
> The other way round is writing a macro.
>
>
>
> On Wed, Jun 1, 2011 at 7:00 PM, Skanda wrote:
>
>> vasant everymonth we have to change the file name.i.e MAR_2011
>>
>> On Wed, Jun 1, 2011 at 4:58 AM, Vasant wrote:
>>
>>> Hi Skanda,
>>>
>>>
>>> =SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9)
>>>
>>> Hope this helps
>>>
>>> Regards
>>> Vasant
>>>
>>> On Wed, Jun 1, 2011 at 12:15 AM, Skanda wrote:
>>>
>>>> There are two excel workbooks:DDD_REPORT_APR2011.xls and
>>>> DDD_REPORT_MAR2011.xls.
>>>>
>>>> In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4 in
>>>> memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column)
>>>> minus sum(i Column) from prod worksheet minus sum(i2:i9) of prod sheet
>>>> from DDD_REPORT_MAR2011.xls.
>>>>
>>>>
>>>>
>>>> This is a done every month so the file names have to be dynamic.
>>>>
>>>> --
>>>>
>>>> --
>>>> 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
>>>
>>> Vasant
>>>
>>> --
>>>
>>> --
>>> 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
>>
>
>
>
> --
> Regards
>
> Vasant
>
> --
>
> --
> 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$$ Dynamic file name and sum
vasant everymonth we have to change the file name.i.e MAR_2011 On Wed, Jun 1, 2011 at 4:58 AM, Vasant wrote: > Hi Skanda, > > > =SUM(prod!J2:J9)-SUM([DDD_Report_Mar_2011.xls]prod!$I$2:$I$9) > > Hope this helps > > Regards > Vasant > > On Wed, Jun 1, 2011 at 12:15 AM, Skanda wrote: > >> There are two excel workbooks:DDD_REPORT_APR2011.xls and >> DDD_REPORT_MAR2011.xls. >> >> In the DDD_REPORT_APR2011.xls, how to calculate the value for cell F4 in >> memo worksheet of :DDD_REPORT_APR2011.xls calculated as sum(J column) >> minus sum(i Column) from prod worksheet minus sum(i2:i9) of prod sheet >> from DDD_REPORT_MAR2011.xls. >> >> >> >> This is a done every month so the file names have to be dynamic. >> >> -- >> >> -- >> 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 > > Vasant > > -- > > -- > 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$$ Automation of formula
The column "difference" highlighted in green is calculated as the difference between the two cells to its left.Then %difference is the percentage in their difference and abs difference is their absolute difference. How to code such that if i pass the formula these three fields are calculated for rest of the columns. attached is the worksheet. -- -- 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_ACCESS.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Dynamic formula
Ashsih, Format of file is attached but no data On Sat, May 21, 2011 at 8:29 AM, Skanda wrote: > > > On Sat, May 21, 2011 at 12:39 AM, ashish koul wrote: > >> can you attach the sample file >> >> >> On Fri, May 20, 2011 at 10:07 PM, Skanda wrote: >> >>> There are two data files say temp1_mon.xls and >>> temp2_monyyy.xls.these two data files change everymonth i.e temp1.xls is >>> always the current data month and temp2.xls is previous data month. >>> >>> here monyyy is displayed as APR2011 for current month and MAR2011 for >>> previous month. >>> >>> There is a tab called MEMO in temp1_mon.cls where the cell >>> reference(4,6) is calculated as: >>> >>> (sum of column 10 from minus sum of column 9 from current month file ) >>> minus sum of column 10 from previous month file. >>> >>> >>> So the file which is current month will become prior month for the next >>> month run. Help me to make the formula dynamic. >>> VB code can 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 >>> >> >> >> >> -- >> *Regards* >> * * >> *Ashish Koul* >> *akoul*.*blogspot*.com <http://akoul.blogspot.com/> >> *akoul*.wordpress.com <http://akoul.wordpress.com/> >> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> >> >> >> P Before printing, think about the environment. >> >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/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 Temp_APR2011.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Temp_MAR2011.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Dynamic formula
On Sat, May 21, 2011 at 12:39 AM, ashish koul wrote: > can you attach the sample file > > > On Fri, May 20, 2011 at 10:07 PM, Skanda wrote: > >> There are two data files say temp1_mon.xls and >> temp2_monyyy.xls.these two data files change everymonth i.e temp1.xls is >> always the current data month and temp2.xls is previous data month. >> >> here monyyy is displayed as APR2011 for current month and MAR2011 for >> previous month. >> >> There is a tab called MEMO in temp1_mon.cls where the cell >> reference(4,6) is calculated as: >> >> (sum of column 10 from minus sum of column 9 from current month file ) >> minus sum of column 10 from previous month file. >> >> >> So the file which is current month will become prior month for the next >> month run. Help me to make the formula dynamic. >> VB code can 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 >> > > > > -- > *Regards* > * * > *Ashish Koul* > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > *akoul*.wordpress.com <http://akoul.wordpress.com/> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > > P Before printing, think about the environment. > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/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$$ Dynamic formula
There are two data files say temp1_mon.xls and temp2_monyyy.xls.these two data files change everymonth i.e temp1.xls is always the current data month and temp2.xls is previous data month. here monyyy is displayed as APR2011 for current month and MAR2011 for previous month. There is a tab called MEMO in temp1_mon.cls where the cell reference(4,6) is calculated as: (sum of column 10 from minus sum of column 9 from current month file ) minus sum of column 10 from previous month file. So the file which is current month will become prior month for the next month run. Help me to make the formula dynamic. VB code can 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
$$Excel-Macros$$ Syntax
Question 1. Dim strPathName As String Dim intCount As Long Dim fs, f What will be the type for variables fs and f?They are not defined in the variable declaration. Question 2. Application.StatusBar = "Now Saving " & GetFileSaveName ActiveWorkbook.SaveAs _ FileName:=GetFileSaveName _ , FileFormat:=xlNormal _ , Password:="", WriteResPassword:="" _ what does the symbol := mean? I mean are they used in specific situations? -- -- 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$$ code explanation please
The data actually pouplates till AS column.When the report is generated it stops at cloumn AL. Does this array thing has to do anything about it? Application.StatusBar = "Please Wait.Loading " & Trim(Prod) & " Data File" Workbooks.OpenText FileName:=FileLoc, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:= _ Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _ Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), _ Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _ Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _ Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _ Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _ Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), _ Array(52, 2)) On Wed, Mar 16, 2011 at 12:56 AM, STDEV(i) wrote: > excel is opening a text File > the array are data for column index and length of string string to be > converted to a cells > > Array(Array(1, 2), Array(2, 4)) > > text file contents: "ABCDEFG" > > converted to 2 columns > column 1 = "AB" > column 2 = "CDEF" > > If you use [Text To Column] ( of DATA menu) you will understand this code. > CMIIW > > > > On Wed, Mar 16, 2011 at 12:04 AM, Skanda wrote: > >> What does the following code do?especially the array part! >> >> Application.StatusBar = "Please Wait.Loading " & Trim(Prod) & " Data >> File" >> Workbooks.OpenText FileName:=FileLoc, Origin _ >> :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ >> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, >> Semicolon:=False, _ >> Comma:=False, Space:=False, Other:=True, OtherChar:="~", >> FieldInfo:= _ >> Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, >> 2), Array(6, 2), _ >> Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), >> Array(11, 2), Array(12, 2), _ >> Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), >> Array(17, 1), Array(18, 1), _ >> Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), >> Array(23, 1), Array(24, 1), _ >> Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), >> Array(29, 1), Array(30, 1), _ >> Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), >> Array(35, 1), Array(36, 1), _ >> Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), >> Array(41, 1), _ >> Array(52, 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 > -- -- 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$$ code explanation please
What does the following code do?especially the array part! Application.StatusBar = "Please Wait.Loading " & Trim(Prod) & " Data File" Workbooks.OpenText FileName:=FileLoc, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:= _ Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _ Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), _ Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _ Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _ Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _ Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), _ Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), _ Array(52, 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
Re: $$Excel-Macros$$ Dynamic range
Thanks Ashish.
On Wed, Feb 16, 2011 at 1:12 AM, ashish koul wrote:
> see if this helps
>
>
> Sub growth()
> Dim i As Long
> i = Range("s21").End(xlDown).Row
> Columns("S:S").Select
> Selection.Insert Shift:=xlToRight
> Range("S21").Select
> ActiveCell.FormulaR1C1 = "Growth"
> Range("S23").Select
> ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
> Range("S23").Select
> Selection.NumberFormat = "0.00%"
> Selection.AutoFill Destination:=Range("S23:S490")
> Range("S23:S" & i).Select
> End Sub
>
>
>
> On Wed, Feb 16, 2011 at 2:57 AM, Skanda wrote:
>
>> Sub growth()
>> '
>> ' growth Macro
>> '
>> Columns("S:S").Select
>> Selection.Insert Shift:=xlToRight
>> Range("S21").Select
>> ActiveCell.FormulaR1C1 = "Growth"
>> Range("S23").Select
>> ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
>> Range("S23").Select
>> Selection.NumberFormat = "0.00%"
>> Selection.AutoFill Destination:=Range("S23:S490")
>> Range("S23:S490").Select
>> End Sub
>>
>>
>>
>> In the above code, the range is set from S23 to S490.But there could be
>> changes in the number of records in the future.
>> how to make it more dynamic, keeping the S23 as constant but S490 to
>> change depending on the records from RC[-2] and RC[-1].
>> i.e if columns RC[-2] and RC[-1] have 5000 records in future how make the
>> above code dynamic?
>>
>> --
>>
>> --
>> 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 <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/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$$ Dynamic range
Sub growth()
'
' growth Macro
'
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("S21").Select
ActiveCell.FormulaR1C1 = "Growth"
Range("S23").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-2]"
Range("S23").Select
Selection.NumberFormat = "0.00%"
Selection.AutoFill Destination:=Range("S23:S490")
Range("S23:S490").Select
End Sub
In the above code, the range is set from S23 to S490.But there could be
changes in the number of records in the future.
how to make it more dynamic, keeping the S23 as constant but S490 to change
depending on the records from RC[-2] and RC[-1].
i.e if columns RC[-2] and RC[-1] have 5000 records in future how make the
above code dynamic?
--
--
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$$ Convert PDF to excel
hi All, How to convert PDF file to an excel file? Thanks, Skanda. -- -- 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
$$Excel-Macros$$ password
Hi All, How to unlock a password that is set for an excel file? -- -- 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
Re: $$Excel-Macros$$ Re: Sort and then extract digits from 7 to 25
Sid,
How do i use this in the existing code?As how to call it etc tec
Skanda.
On Mon, Aug 2, 2010 at 5:27 AM, Sid. wrote:
> Hi Skanda,
>
> It probably looks something like this (you may have to tweak the Row/
> Column values depending on the overall structure of your worksheet)
>
> Sub Select_Char7to25()
> '
> '
>Application.Goto Reference:="R6C2"
>Range("B6").Select
>ActiveCell.FormulaR1C1 = "=MID(RC[-1],7,19)"
>Application.Goto Reference:="R1000C2"
>ActiveCell.FormulaR1C1 = "=MID(RC[-1],7,19)"
>Selection.Copy
>Range(Selection, Selection.End(xlUp)).Select
>ActiveSheet.Paste
>Application.CutCopyMode = False
> End Sub
>
> Kind regards,
>
> Sid.
>
> On Aug 1, 4:49 pm, Skanda wrote:
> > Hi All,
> > The report that I generate is sorted on column "S" and then it is
> hidden
> > using VBA code.After the sorting is done I want to display only
> characters
> > from 7 to 25 from row b6 to B1000.
> > Eg. PM 00 New York,NY. We need to diaplay only New York,NY.How
> to
> > do this programatically?
> >
> > Thanks,
> > Skanda.
>
> --
>
> --
> 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
$$Excel-Macros$$ Sort and then extract digits from 7 to 25
Hi All, The report that I generate is sorted on column "S" and then it is hidden using VBA code.After the sorting is done I want to display only characters from 7 to 25 from row b6 to B1000. Eg. PM 00 New York,NY. We need to diaplay only New York,NY.How to do this programatically? Thanks, Skanda. -- -- 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
$$Excel-Macros$$ Extract numbers from alphanumeric string
I used this formula to extract numbers from alphanumeric string =1*mid(b2,match(true,isnumber(1*mid(b2,row($1:$159),1)),0,count(1*mid(b2,row($1:$159),1))) but it doesnt seem to work. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 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 6,800 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
Re: $$Excel-Macros$$ PIVOT TABLE AND PERCENTAGE
Anish, Thanks for the quick turn around.Can you tell me how to get the solution.I have data that has 20,000 rows. Skanda. On Thu, Dec 17, 2009 at 2:28 AM, Anish Shrivastava wrote: > Hi Skanda, > > Attached please find the solution..Lemme know if the format is according to > your requirements.. > > Cheers!! > ANISH > > On Wed, Dec 16, 2009 at 8:46 PM, Skanda wrote: > >> Srini, >> Thanks for the response.Let me explain my requirement. >> >> We need the percentage at the physician level. >> say for instance, Srini has total qty 30. >> so the BCBS for srini is 10/30 which is 0.33% and wellcare for srini is >> 20/30 which is 0.66%. >> >> we should not combine samuel total with srini total to get the percentage >> at phy level. >> >> and could you if you can..explain to me in steps how to get the pivot >> table if you have a solution. >> >> Skanda. >> >> >> >> On Wed, Dec 16, 2009 at 3:13 AM, Srinivasulu Reddy Yarasi < >> seenuyar...@gmail.com> wrote: >> >>> Hi Skanda, >>> >>> Updated pivot with quantity column too >>> >>> regards >>> Srinivasulu Yarasi >>> >>> >>> On Wed, Dec 16, 2009 at 1:41 PM, Srinivasulu Reddy Yarasi < >>> seenuyar...@gmail.com> wrote: >>> >>>> Hi Skanda, >>>> >>>> Attached excel file with sample. >>>> >>>> U can delete two records of Srini & refresh pivot table. U will get data >>>> for for the example u specified. >>>> >>>> regards >>>> Srinivasulu Yarasi >>>> >>>> >>>> On Wed, Dec 16, 2009 at 2:17 AM, Skanda wrote: >>>> >>>>> can we get percentage in pivot table? >>>>> >>>>> say for instance i have physican payer Mg_dispensed columns >>>>> >>>>> Samuel Aetna 100 >>>>> Samuel BCBS 200 >>>>> Samuel wellcare 200 >>>>> >>>>> when we do a pivot can we get something like this: >>>>> samuel Aetna 10020% >>>>> samuel BCBS 20040% >>>>> samuel wellcare 200 40% >>>>> >>>>> -- >>>>> >>>>> -- >>>>> Some important links for excel users: >>>>> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >>>>> http://www.excelitems.com >>>>> 2. Excel tutorials at http://www.excel-macros.blogspot.com >>>>> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com >>>>> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>> >>>>> >>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>> If you find any spam message in the group, please send an email to: >>>>> Ayush Jain @ jainayus...@gmail.com or >>>>> Ashish Jain @ 26may.1...@gmail.com >>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>> HELP US GROW !! >>>>> >>>>> We reach over 6,500 subscribers worldwide and receive many nice notes >>>>> about the learning and support from the group. Our goal is to have 10,000 >>>>> subscribers by the end of 2009. 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. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >>> http://www.excelitems.com >>> 2. Excel tutorials at http://www.excel-macros.blogspot.com >>> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com >>> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> If you find any spam message in the group, please send an email to: >>> Ayush Jain @ jainayus...@gmail.com or >>> Ashish Jain @ 26may.1...@gmail.com >>> <><><><><><><><><><&g
Re: $$Excel-Macros$$ PIVOT TABLE AND PERCENTAGE
Srini, Thanks for the response.Let me explain my requirement. We need the percentage at the physician level. say for instance, Srini has total qty 30. so the BCBS for srini is 10/30 which is 0.33% and wellcare for srini is 20/30 which is 0.66%. we should not combine samuel total with srini total to get the percentage at phy level. and could you if you can..explain to me in steps how to get the pivot table if you have a solution. Skanda. On Wed, Dec 16, 2009 at 3:13 AM, Srinivasulu Reddy Yarasi < seenuyar...@gmail.com> wrote: > Hi Skanda, > > Updated pivot with quantity column too > > regards > Srinivasulu Yarasi > > > On Wed, Dec 16, 2009 at 1:41 PM, Srinivasulu Reddy Yarasi < > seenuyar...@gmail.com> wrote: > >> Hi Skanda, >> >> Attached excel file with sample. >> >> U can delete two records of Srini & refresh pivot table. U will get data >> for for the example u specified. >> >> regards >> Srinivasulu Yarasi >> >> >> On Wed, Dec 16, 2009 at 2:17 AM, Skanda wrote: >> >>> can we get percentage in pivot table? >>> >>> say for instance i have physican payer Mg_dispensed columns >>> >>> Samuel Aetna 100 >>> Samuel BCBS 200 >>> Samuel wellcare 200 >>> >>> when we do a pivot can we get something like this: >>> samuel Aetna 10020% >>> samuel BCBS 20040% >>> samuel wellcare 200 40% >>> >>> -- >>> >>> -- >>> Some important links for excel users: >>> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >>> http://www.excelitems.com >>> 2. Excel tutorials at http://www.excel-macros.blogspot.com >>> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com >>> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> If you find any spam message in the group, please send an email to: >>> Ayush Jain @ jainayus...@gmail.com or >>> Ashish Jain @ 26may.1...@gmail.com >>> <><><><><><><><><><><><><><><><><><><><><><> >>> HELP US GROW !! >>> >>> We reach over 6,500 subscribers worldwide and receive many nice notes >>> about the learning and support from the group. Our goal is to have 10,000 >>> subscribers by the end of 2009. 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. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > http://www.excelitems.com > 2. Excel tutorials at http://www.excel-macros.blogspot.com > 3. Learn VBA Macros at http://www.vbamacros.blogspot.com > 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > If you find any spam message in the group, please send an email to: > Ayush Jain @ jainayus...@gmail.com or > Ashish Jain @ 26may.1...@gmail.com > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,500 subscribers worldwide and receive many nice notes about > the learning and support from the group. Our goal is to have 10,000 > subscribers by the end of 2009. 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. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ PIVOT TABLE AND PERCENTAGE
can we get percentage in pivot table? say for instance i have physican payer Mg_dispensed columns Samuel Aetna 100 Samuel BCBS 200 Samuel wellcare 200 when we do a pivot can we get something like this: samuel Aetna 10020% samuel BCBS 20040% samuel wellcare 200 40% -- -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ tranpose question
Hi all, Please find the attachment for my requirement.! Thanks, Skanda. --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,000 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- excel question.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Sum
Columns A and B give the sum of ceizg and paracept in each row. Is there any formula that sums all the values of "Ceizg" ad "Paracept". e.g:96,00 is the sum of all "paracept" in the first row. 4,600 is the sum of all "Ceizg" in the second row. I gave only partial data. I have about 50K rows and its very difficult to do summation manually. Thanks for your help in advance, --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~--- paracept.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ convert all fields to character
Hi all, I have a workbook with three worksheets in them (AOB,WW,CST). Each of the worksheet have 20 variables. I want to use them all in sas.But it is easier to process if all the variables are converted to character type. I manually change the data type beofre uploading them into sas. Can you help me with any code that converts all the variables to character type in all three worksheets. Thanks. --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Compare and extract data
HI ALL, I have two excel workbooks table1 and table2. Table1 has two sections : ALL name score1 score2 score3 demo name score1 score2 score3 Table2 has : tablenum type month name score1score3 tablenum is 1(which is constant always ) type is ALL or DEMO Question is: I receive table1 for each month and I have to update table2 based on data received in table1. The macro should be able to compare two worksheets and extract score1 and score3 information for names in table1 by type variable and update table2. I need your help in writing a macro for the above condition. Thanks in advance, Skanda.! table num type month name score1 score3 name score1 score2 score3 name score1 score2 score3 --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~--- table1.xls Description: MS-Excel spreadsheet table2.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Compare two columns!!
Paul, Thanks for the help.Is there any macro for this? On Tue, Mar 10, 2009 at 11:46 AM, Paul Schreiner wrote: > This is basic Excel functionality. > let's say your sample data is in rows 2,3,4 (column headings in row 1) > > to "build" your function, select Cell C2 and start the Function Wizard > select the "IF" function. > with the type-in bar in the Logical_Test box, click cell A2, then type <> > and click in cell B2. the result should read: > A2<>B2 > In the next box (Value_if_true), enter 0 > in the next box (Value_if_false), enter 1. > then hit "ok" to complete the formula. > > the result should be: > =IF(A2<>B2,1,0) > > You COULD use: > =IF(A2=B2,0,1) which tests if they are the SAME instead if different. > > In column D, use the function wizard to make: > =IF(C2=0,0,A2-B2) > > then, copy these formulas down the rows... > > P > > -- > *From:* Skanda > *To:* excel-macros@googlegroups.com > *Sent:* Tuesday, March 10, 2009 10:32:47 AM > *Subject:* $$Excel-Macros$$ Compare two columns!! > > > Hi Guys, > If I need to compare two columns in the same worksheet say column A and > Column B. If there is a difference between two corresponding cells in A and > B then result should be '1' in correspoding cell in column C else result > should be '0'.And the difference between col A andCol B should be displayed > in Col D > > Also the same scenario if we need to compare two columns in two worksheets > how is this achieved? > > Col ACol B Col C Col D >5 5 0 0 >7 2 1 5 >4 9 1-5 > > > > Thanks for your help > > > > > > --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Compare two columns!!
Ashish, Thanks for the help. This is helpful if we have less number of rows. If I have a couple of thousand how do we do it?I mean a macro or something like that. On Tue, Mar 10, 2009 at 11:38 AM, Ashish Jain <26may.1...@gmail.com> wrote: > > > ---- > > Hi Skanda, > > Try this formula based solution: > > Col A Col B Col C Col > D > Row 1 5 5 =IF(A1=B1,0,1) =A1-B1 > Row 2 7 2 =IF(A2=B2,0,1) =A2-B2 > Row 3 4 9 =IF(A3=B3,0,1) =A3-B3 > > > For more tips, tricks, downloads and problem solutions: visit --> > http://www.excelitems.com > > Regards > *Ashish **Jain* > Analyst, CSC > Microsoft Certified Application (Excel) Specialist > Technical Trainer - Excel and VBA > Author -> www.excelitems.com > +91--40-48-43 > > > > ---- > > On Mar 10, 7:32 pm, Skanda wrote: > > Hi Guys, > > If I need to compare two columns in the same worksheet say column A and > > Column B. If there is a difference between two corresponding cells in A > and > > B then result should be '1' in correspoding cell in column C else result > > should be '0'.And the difference between col A andCol B should be > displayed > > in Col D > > > > Also the same scenario if we need to compare two columns in two > worksheets > > how is this achieved? > > > > Col ACol B Col C Col D > >5 5 0 0 > >7 2 1 5 > >4 9 1-5 > > > > Thanks for your help > > > --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Compare two columns!!
Hi Guys, If I need to compare two columns in the same worksheet say column A and Column B. If there is a difference between two corresponding cells in A and B then result should be '1' in correspoding cell in column C else result should be '0'.And the difference between col A andCol B should be displayed in Col D Also the same scenario if we need to compare two columns in two worksheets how is this achieved? Col ACol B Col C Col D 5 5 0 0 7 2 1 5 4 9 1-5 Thanks for your help --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: $$Excel-Macros$$
Pradeep, You can use the "mid" function example: PRADEEP-KUMAR-VINOD-154-AB to extract 154-AB use as follows: =mid(cellnumber,startingnumber, number of characters) =mid(cellnumber,21,6) if you have the above value in cell A1 then =mid(A1,21,6) Thanks, Skanda. On Sun, Oct 19, 2008 at 9:45 AM, Mattathil M. Pradeep <[EMAIL PROTECTED]>wrote: > Dears.. > > > > How Pradeep, > > to copy particular data from one cell ? attached excel sheet for your > reference. > > > > Thanks in advance > > PRADEEP > > > > > > --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
