$$Excel-Macros$$ Re: Need Help in calculation of score

[SG]

Hi Mandeep,

Sorry for the confusion. 
Final score is 100 if all are Yes.
I need the formula in Colum "I" for Score.(Distribution of Score in case of 
"NA" should be calculated here.)
Data in K:P are the values for "Yes" & "No" of data in B:H.

PFA updated sheet. The difference in headers are typo errors.

Regards,
SG

On Thursday, January 7, 2016 at 1:11:40 PM UTC+5:30, Mandeep Baluja wrote:
>
> Hi SG,
>
> Things I would like to know !!! 
> 1) What is required You need formula in column "I" or You need to 
> distribute data in "K3:P5" 
> 2) Why headers(%) B:H and k:P are different? are they being used in 
> formulas for calculation.
> 3)   What is final Score ?
>
> Can we have the result set below your question with result required 
> manually for better understanding. 
>  
> Regards,
> Mandeep baluja 
>
>
>
>
>
>

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


Book1.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Re: Need Help in calculation of score

[SG]

Hi Mandeep,

Please refer to the comment in attached sheet.
I hope it would get clear now. :)


On Thursday, January 7, 2016 at 2:06:11 PM UTC+5:30, Mandeep Baluja wrote:
>
> Hey SG,
>
> Correct me if I am wrong !! 
> 1)If Parameter M(Fatal) is No, then total score would be 0 Formula should 
> be =IF(H3="No",0
>
> 2)If any parameter is "No", then its score should get deducted from final 
> score formula should be IF(COUNTIF($B$3:$H$3,"No")>0,100-SUM(K3:P3)
>   Now on combining these two formulas 
> is =IF(H3="No",0,IF(COUNTIF($B$3:$H$3,"No")>0,100-SUM(K3:P3)
>
> Confusion !! 
> 3) if any of parameter is NA then its score must be distributed in rest 
> of the parameters.(Not to M(Fatal) parameter)
> *Score must be distributed in rest of parameters ??? Does it mean 
> formula should do distribution on which cells ? because k3:p5 is already 
> filled with your formula.Can you put the output manually below your data.*
>
> *Regards,*
> *Mandeep baluja *
>
>
>

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


Book1 - Copy.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Sampling - Please check

[SG]

Hi Experts


I need your help in choosing the samples for quality check. The only idea 
in my mind is the Random function but this wouldn’t suffice as there are 
2-3 conditions on basis of which sampling should be done.

It should be done for 4 calls per month per type (column A for type).

Different Duration slabs like out of 4 calls for each type,

One call with Duration of 1 to 10 minutes

One call with Duration of 10 to 20 minutes

One call with Duration of 20 to 30 minutes

One call with Duration of 30 to 40 minutes

In case, any slot not available, it should pick so on.

I really know it’s very tricky but need your help. PFA data for your 
reference.

Thanks in advance

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


sample.xlsx
Description: MS-Excel 2007 spreadsheet

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Thanks for your quick response Paul. The line ""In case, any slot not 
available, it should pick so on" means that in case any slot is not 
available, it should skip it & choose the call from next slab. Your trick 
is helping me very much. One more modification i need is per person 
sampling. That means, per person, 4 calls/month for all 4 slabs.
Please try one more time.

On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner wrote:
>
> Here's a possible solution.
> Basically, you need to determine which "set" the record falls into.
> I used:
>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>   IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0
> this says that if the Duration value is >0 and <= 10 minutes, then it 
> belongs in set #1.
> >10 and <= 20 is set 2, 
> >20 and <= 30 is set 3
> >30 and <= 60 is set 4  (you could make it >30, but it "looks cleaner" to 
> keep them all the same!)
>
>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>   IF(G2>TIME(0,30,0),4,0
> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD)
>
> Next, I wanted to use the month in determining the set, so I prefixed the 
> formula with:
> =MONTH(F2) &"."&
>
> giving me:
> =MONTH(F2) &"."&
>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>   IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0
>
> I added two columns (A and B).
> Put this in A2 and copied it down the list.
>
> Next, in column B I used =RAND() and copied IT down the list.
> (technically, this could have been put at the end)
>
> Next, sort these records by the RAND column.
>
> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 
> etc. in the first column.
> I then used vLookup to find the first record in the data that matches this 
> "set".
> Note: since I used =month() & "." & to come up with the set names, this is 
> TEXT, not a number.
> So, in the vlookup, you must make sure the "set" you're looking for is 
> TEXT.
> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in column 
> "L" in my case)
> Excel enters them as numeric.
> so in the VLookup(), I converted them to text with TEXT().
> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$G,3,FALSE)
>
> Now, in your data, there were no records for February that had a duration 
> of 20-30 minutes.
> So no set # 2.3
>
> I'm not sure what you want to do in this case.
> In your explanation, you said:
> "In case, any slot not available, it should pick so on."
>
> I have no idea what you mean by "it should pick so on".
>
>
> *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*
> -
>
>
> On Wednesday, January 27, 2016 7:25 AM, SG  > wrote:
>
>
>
>
>
> Hi Experts
>
> I need your help in choosing the samples for quality check. The only idea 
> in my mind is the Random function but this wouldn’t suffice as there are 
> 2-3 conditions on basis of which sampling should be done.
> It should be done for 4 calls per month per type (column A for type).
> Different Duration slabs like out of 4 calls for each type,
> One call with Duration of 1 to 10 minutes
> One call with Duration of 10 to 20 minutes
> One call with Duration of 20 to 30 minutes
> One call with Duration of 30 to 40 minutes
> In case, any slot not available, it should pick so on.
> I really know it’s very tricky but need your help. PFA data for your 
> reference.
> Thanks in advance
> -- 
> 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 breakin

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Paul, have you checked it?

On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote:
>
> Thanks for your quick response Paul. The line ""In case, any slot not 
> available, it should pick so on" means that in case any slot is not 
> available, it should skip it & choose the call from next slab. Your trick 
> is helping me very much. One more modification i need is per person 
> sampling. That means, per person, 4 calls/month for all 4 slabs.
> Please try one more time.
>
> On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner 
> wrote:
>>
>> Here's a possible solution.
>> Basically, you need to determine which "set" the record falls into.
>> I used:
>>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>>   IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0
>> this says that if the Duration value is >0 and <= 10 minutes, then it 
>> belongs in set #1.
>> >10 and <= 20 is set 2, 
>> >20 and <= 30 is set 3
>> >30 and <= 60 is set 4  (you could make it >30, but it "looks cleaner" to 
>> keep them all the same!)
>>
>>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>>   IF(G2>TIME(0,30,0),4,0
>> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD)
>>
>> Next, I wanted to use the month in determining the set, so I prefixed the 
>> formula with:
>> =MONTH(F2) &"."&
>>
>> giving me:
>> =MONTH(F2) &"."&
>>   IF(AND(G2>0,G2<=TIME(0,10,0)),1,
>>   IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3,
>>   IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0
>>
>> I added two columns (A and B).
>> Put this in A2 and copied it down the list.
>>
>> Next, in column B I used =RAND() and copied IT down the list.
>> (technically, this could have been put at the end)
>>
>> Next, sort these records by the RAND column.
>>
>> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 
>> etc. in the first column.
>> I then used vLookup to find the first record in the data that matches 
>> this "set".
>> Note: since I used =month() & "." & to come up with the set names, this 
>> is TEXT, not a number.
>> So, in the vlookup, you must make sure the "set" you're looking for is 
>> TEXT.
>> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in 
>> column "L" in my case)
>> Excel enters them as numeric.
>> so in the VLookup(), I converted them to text with TEXT().
>> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$G,3,FALSE)
>>
>> Now, in your data, there were no records for February that had a duration 
>> of 20-30 minutes.
>> So no set # 2.3
>>
>> I'm not sure what you want to do in this case.
>> In your explanation, you said:
>> "In case, any slot not available, it should pick so on."
>>
>> I have no idea what you mean by "it should pick so on".
>>
>>
>> *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*
>> -
>>
>>
>> On Wednesday, January 27, 2016 7:25 AM, SG  wrote:
>>
>>
>>
>>
>>
>> Hi Experts
>>
>> I need your help in choosing the samples for quality check. The only idea 
>> in my mind is the Random function but this wouldn’t suffice as there are 
>> 2-3 conditions on basis of which sampling should be done.
>> It should be done for 4 calls per month per type (column A for type).
>> Different Duration slabs like out of 4 calls for each type,
>> One call with Duration of 1 to 10 minutes
>> One call with Duration of 10 to 20 minutes
>> One call with Duration of 20 to 30 minutes
>> One call with Duration of 30 to 40 minutes
>> In case, any slot not available, it should pick so on.
>> I really know it’s very tricky but need your help. PFA data for your 
>> reference.
>> Thanks in advance
>> -- 
>>

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

No Paul, I didn't get it. Can you please share it again.

On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote:
>
> Yeesss.. and responded with an update.
> Did you not receive it?
>  
> *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*
> -----
>
>
> On Thursday, January 28, 2016 12:43 AM, SG  > wrote:
>
>
>
> Paul, have you checked it?
>
> On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote:
>
> Thanks for your quick response Paul. The line ""In case, any slot not 
> available, it should pick so on" means that in case any slot is not 
> available, it should skip it & choose the call from next slab. Your trick 
> is helping me very much. One more modification i need is per person 
> sampling. That means, per person, 4 calls/month for all 4 slabs.
> Please try one more time.
>
> On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner 
> wrote:
>
> Here's a possible solution.
> Basically, you need to determine which "set" the record falls into.
> I used:
>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>   IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0
> this says that if the Duration value is >0 and <= 10 minutes, then it 
> belongs in set #1.
> >10 and <= 20 is set 2, 
> >20 and <= 30 is set 3
> >30 and <= 60 is set 4  (you could make it >30, but it "looks cleaner" to 
> keep them all the same!)
>
>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>   IF(G2>TIME(0,30,0),4,0
> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD)
>
> Next, I wanted to use the month in determining the set, so I prefixed the 
> formula with:
> =MONTH(F2) &"."&
>
> giving me:
> =MONTH(F2) &"."&
>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>   IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0
>
> I added two columns (A and B).
> Put this in A2 and copied it down the list.
>
> Next, in column B I used =RAND() and copied IT down the list.
> (technically, this could have been put at the end)
>
> Next, sort these records by the RAND column.
>
> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 
> etc. in the first column.
> I then used vLookup to find the first record in the data that matches this 
> "set".
> Note: since I used =month() & "." & to come up with the set names, this is 
> TEXT, not a number.
> So, in the vlookup, you must make sure the "set" you're looking for is 
> TEXT.
> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in column 
> "L" in my case)
> Excel enters them as numeric.
> so in the VLookup(), I converted them to text with TEXT().
> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE)
>
> Now, in your data, there were no records for February that had a duration 
> of 20-30 minutes.
> So no set # 2.3
>
> I'm not sure what you want to do in this case.
> In your explanation, you said:
> "In case, any slot not available, it should pick so on."
>
> I have no idea what you mean by "it should pick so on".
>
>
> *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*-- 
> ---
>
>
> On Wednesday, January 27, 2016 7:25 AM, SG  wrote:
>
>
>
>
>
> Hi Experts
>
> I need your help in choosing the samples for quality check. The only idea 
> in my mind is the Random function but this wouldn’t suffice as there are 
> 2-3 conditions on basis of which sampling should be done.
> It should be done for 4 calls per month per type (column A for type).
> Different Duration slabs like out of 4 calls for each type,
> One call with Duration of 1 to 10 minutes
> One call with Duration of 10 to 20 minutes
>

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Hi Paul, I got the sheet. Thankyou so much for all your effort in this 
sampling. I'm really thankful.

Only one thing i observed is that, in raw data , there are 4 entries of 
Rohit in Jan but in Random Sampling section, only two are coming as sample 
for Rohit whereas it should be 4calls,per person, per month with available 
slots as per the slab. PFA for you.

Also, Rand function don't have any impact in selection of samples. Am i 
right?
Please check if this can be solved as this is the condition for sampling.

On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote:
>
> No Paul, I didn't get it. Can you please share it again.
>
> On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote:
>>
>> Yeesss.. and responded with an update.
>> Did you not receive it?
>>  
>> *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*
>> -
>>
>>
>> On Thursday, January 28, 2016 12:43 AM, SG  wrote:
>>
>>
>>
>> Paul, have you checked it?
>>
>> On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote:
>>
>> Thanks for your quick response Paul. The line ""In case, any slot not 
>> available, it should pick so on" means that in case any slot is not 
>> available, it should skip it & choose the call from next slab. Your trick 
>> is helping me very much. One more modification i need is per person 
>> sampling. That means, per person, 4 calls/month for all 4 slabs.
>> Please try one more time.
>>
>> On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner 
>> wrote:
>>
>> Here's a possible solution.
>> Basically, you need to determine which "set" the record falls into.
>> I used:
>>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>>   IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0
>> this says that if the Duration value is >0 and <= 10 minutes, then it 
>> belongs in set #1.
>> >10 and <= 20 is set 2, 
>> >20 and <= 30 is set 3
>> >30 and <= 60 is set 4  (you could make it >30, but it "looks cleaner" to 
>> keep them all the same!)
>>
>>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>>   IF(G2>TIME(0,30,0),4,0
>> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD)
>>
>> Next, I wanted to use the month in determining the set, so I prefixed the 
>> formula with:
>> =MONTH(F2) &"."&
>>
>> giving me:
>> =MONTH(F2) &"."&
>>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>>   IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0
>>
>> I added two columns (A and B).
>> Put this in A2 and copied it down the list.
>>
>> Next, in column B I used =RAND() and copied IT down the list.
>> (technically, this could have been put at the end)
>>
>> Next, sort these records by the RAND column.
>>
>> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 
>> etc. in the first column.
>> I then used vLookup to find the first record in the data that matches 
>> this "set".
>> Note: since I used =month() & "." & to come up with the set names, this 
>> is TEXT, not a number.
>> So, in the vlookup, you must make sure the "set" you're looking for is 
>> TEXT.
>> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in 
>> column "L" in my case)
>> Excel enters them as numeric.
>> so in the VLookup(), I converted them to text with TEXT().
>> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE)
>>
>> Now, in your data, there were no records for February that had a duration 
>> of 20-30 minutes.
>> So no set # 2.3
>>
>> I'm not sure what you want to do in this case.
>> In your explanation, you said:
>> "In case, any slot not available, it should pick so on."
>>
>>

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Hi Paul,

I really appreciate your help. Apologies for all the confusion. 

I have added some more data in this file. I just need  if i select Jan 
Month, so who ever agents has 4 calls with duration as per the slab must 
come in sample & if less than 4, still it would come with number of calls 
it has. It must cover all agent with number of calls they have in that 
month.  Honestly speaking, You have really done the efforts in this file 
but my purpose is not getting solved neither i'm able to do it. :(  If you 
can again relook this..please check otherwise..really thanks

Regards
SG



On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote:
>
> See below:
> *Paul*
> -
>
>
> On Friday, January 29, 2016 3:19 AM, SG > 
> wrote:
>
>
>
> Hi Paul, I got the sheet. Thankyou so much for all your effort in this 
> sampling. I'm really thankful.
>
> Only one thing i observed is that, in raw data , there are 4 entries of 
> Rohit in Jan but in Random Sampling section, only two are coming as sample 
> for Rohit whereas it should be 4calls,per person, per month with available 
> slots as per the slab. PFA for you.
> *I don't think that is a reasonable expectation.*
> *In January, rohit may appear 4 times, but it only appears TWICE in the 
> 0-10 minute category, ONCE in the 10-20 minute and ONCE in the 20-30 
> minute.*
>
> *In the sample data, there are (5) records in the 0-10 minute category for 
> January.*
> *With only (4) people, one of the (5) records will not be selected.*
> *When the data is sorted by the Rand" column, there is a "random" chance 
> that only one of the rohit records will be selected.*
>
>
> Also, Rand function don't have any impact in selection of samples. Am i 
> right?
> Please check if this can be solved as this is the condition for sampling.
> *In order for the RAND() function to affect the sampling, you must sort 
> the data.*
> *When you sort it by the Rand column, the "index" number gets recalculated 
> based on the "position".*
> *so, each time you sort the data, you'll get a different set of selections 
> for the (4) people.*
>
> *Attached you will find a version of the file with a "sort" button.*
>
>
> On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote:
>
> No Paul, I didn't get it. Can you please share it again.
>
> On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote:
>
> Yeesss.. and responded with an update.
> Did you not receive it?
>  
> *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*-- 
> ---
>
>
> On Thursday, January 28, 2016 12:43 AM, SG  wrote:
>
>
>
> Paul, have you checked it?
>
> On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote:
>
> Thanks for your quick response Paul. The line ""In case, any slot not 
> available, it should pick so on" means that in case any slot is not 
> available, it should skip it & choose the call from next slab. Your trick 
> is helping me very much. One more modification i need is per person 
> sampling. That means, per person, 4 calls/month for all 4 slabs.
> Please try one more time.
>
> On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner 
> wrote:
>
> Here's a possible solution.
> Basically, you need to determine which "set" the record falls into.
> I used:
>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>   IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0
> this says that if the Duration value is >0 and <= 10 minutes, then it 
> belongs in set #1.
> >10 and <= 20 is set 2, 
> >20 and <= 30 is set 3
> >30 and <= 60 is set 4  (you could make it >30, but it "looks cleaner" to 
> keep them all the same!)
>
>   IF(AND(G2>0,G2<=TIME(0,10,0)), 1,
>   IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2,
>   IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3,
>   IF(G2>TIME(0,30,0),4,0
> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD)
>
> Next, I wanted to use the month in determining the set, so I prefixed the 
> formula with:
> =MONTH(F2) &"."&
>
> giving me:
> =MONTH(F2) &"."&
>   IF(AND(G2>0

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Paul, apologies for bothering you so much & unable to interpret the 
expectation. Please refer sheet 2 in attached file for results per agent.
Also, I have taken duration as example of selecting sample.

On Monday, February 1, 2016 at 6:37:46 PM UTC+5:30, Paul Schreiner wrote:
>
> I'm not sure what you are expecting, or how this does not meet your 
> expectations.
>
> For January, you have (9) entries in the 0-10 minute range.
> So each of your (4) agents will get one of the samples.
>
> But you only have (3) entries in the 10-20 minute range.
> So, the 4th agent does not receive an entry.
>
> The 20-30 minute range only has ONE entry, so only the first agent 
> receives an entry.
>
> in the "over 30" range, there are two entries, so only the first two 
> agents receive entries.
>
> That all seems to work.
>
> You said: "if less than 4, still it would come with number of calls it has"
> I have no idea what that means.
> does that mean that if there are not sufficient records for that time 
> period, you want to take an entry from another?
>
> If you could:
> Take the records you've provided for January
> and show me what you would EXPECT the (4) agents to show.
> (and why?)
>
>
>
> *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*
> -
>
>
> On Monday, February 1, 2016 5:34 AM, SG > 
> wrote:
>
>
>
> Hi Paul,
>
> I really appreciate your help. Apologies for all the confusion. 
>
> I have added some more data in this file. I just need  if i select Jan 
> Month, so who ever agents has 4 calls with duration as per the slab must 
> come in sample & if less than 4, still it would come with number of calls 
> it has. It must cover all agent with number of calls they have in that 
> month.  Honestly speaking, You have really done the efforts in this file 
> but my purpose is not getting solved neither i'm able to do it. :(  If you 
> can again relook this..please check otherwise..really thanks
>
> Regards
> SG
>
>
>
> On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote:
>
> See below:
> *Paul*
> -- ---
>
>
> On Friday, January 29, 2016 3:19 AM, SG  wrote:
>
>
>
> Hi Paul, I got the sheet. Thankyou so much for all your effort in this 
> sampling. I'm really thankful.
>
> Only one thing i observed is that, in raw data , there are 4 entries of 
> Rohit in Jan but in Random Sampling section, only two are coming as sample 
> for Rohit whereas it should be 4calls,per person, per month with available 
> slots as per the slab. PFA for you.
> *I don't think that is a reasonable expectation.*
> *In January, rohit may appear 4 times, but it only appears TWICE in the 
> 0-10 minute category, ONCE in the 10-20 minute and ONCE in the 20-30 
> minute.*
>
> *In the sample data, there are (5) records in the 0-10 minute category for 
> January.*
> *With only (4) people, one of the (5) records will not be selected.*
> *When the data is sorted by the Rand" column, there is a "random" chance 
> that only one of the rohit records will be selected.*
>
>
> Also, Rand function don't have any impact in selection of samples. Am i 
> right?
> Please check if this can be solved as this is the condition for sampling.
> *In order for the RAND() function to affect the sampling, you must sort 
> the data.*
> *When you sort it by the Rand column, the "index" number gets recalculated 
> based on the "position".*
> *so, each time you sort the data, you'll get a different set of selections 
> for the (4) people.*
>
> *Attached you will find a version of the file with a "sort" button.*
>
>
> On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote:
>
> No Paul, I didn't get it. Can you please share it again.
>
> On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote:
>
> Yeesss.. and responded with an update.
> Did you not receive it?
>  
> *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*-- 
> ---
>
>
> On Thursday, January 28, 2016 12:43 AM, SG  wrot

Re: $$Excel-Macros$$ Sampling - Please check

[SG]

Hi Paul,

Added Column "D" as unique id & what needed as result is in "Result" sheet. 
It would require per month-per slot-per name >> 3 samples.
VBA Macro looks a great idea to work with this. PFA for your reference.  My 
apologies &  promise no other modification would be required after this. 

On Monday, February 1, 2016 at 8:46:32 PM UTC+5:30, Paul Schreiner wrote:
>
> I think I better understand what your data means.
> You're wanting a random sample of (4) records for each of the names in the 
> "Name" column.
> and you're wanting the (4) samples to be in each if (4) time ranges.
>
> The attached file takes care of THAT,
>
> but your additional requirement, that of what to do if you don't have a 
> match within the time range
> may be more problematic.
>
> An attempt to create a "flag" to show when a record is selected,
> then, when no entry exists, choose from those that were not selected.
> Causes a circular reference.
>
> I think the only way to get past this is to write a VBA macro that loads 
> the data into an array
> and processes the array.
>
> But before I try something like that:
>
> Is this the way the data is "presented" to you?
> is there any "unique" identifier for the records?
>
>
> *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*
> -
>
>
> On Monday, February 1, 2016 8:33 AM, SG > 
> wrote:
>
>
>
> Paul, apologies for bothering you so much & unable to interpret the 
> expectation. Please refer sheet 2 in attached file for results per agent.
> Also, I have taken duration as example of selecting sample.
>
> On Monday, February 1, 2016 at 6:37:46 PM UTC+5:30, Paul Schreiner wrote:
>
> I'm not sure what you are expecting, or how this does not meet your 
> expectations.
>
> For January, you have (9) entries in the 0-10 minute range.
> So each of your (4) agents will get one of the samples.
>
> But you only have (3) entries in the 10-20 minute range.
> So, the 4th agent does not receive an entry.
>
> The 20-30 minute range only has ONE entry, so only the first agent 
> receives an entry.
>
> in the "over 30" range, there are two entries, so only the first two 
> agents receive entries.
>
> That all seems to work.
>
> You said: "if less than 4, still it would come with number of calls it has"
> I have no idea what that means.
> does that mean that if there are not sufficient records for that time 
> period, you want to take an entry from another?
>
> If you could:
> Take the records you've provided for January
> and show me what you would EXPECT the (4) agents to show.
> (and why?)
>
>
>
> *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*-- 
> ---
>
>
> On Monday, February 1, 2016 5:34 AM, SG  wrote:
>
>
>
> Hi Paul,
>
> I really appreciate your help. Apologies for all the confusion. 
>
> I have added some more data in this file. I just need  if i select Jan 
> Month, so who ever agents has 4 calls with duration as per the slab must 
> come in sample & if less than 4, still it would come with number of calls 
> it has. It must cover all agent with number of calls they have in that 
> month.  Honestly speaking, You have really done the efforts in this file 
> but my purpose is not getting solved neither i'm able to do it. :(  If you 
> can again relook this..please check otherwise..really thanks
>
> Regards
> SG
>
>
>
> On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote:
>
> See below:
> *Paul*
> -- ---
>
>
> On Friday, January 29, 2016 3:19 AM, SG  wrote:
>
>
>
> Hi Paul, I got the sheet. Thankyou so much for all your effort in this 
> sampling. I'm really thankful.
>
> Only one thing i observed is that, in raw data , there are 4 entries of 
> Rohit in Jan but in Random Sampling section, only two are coming as sample 
> for Rohit whereas it should be 4calls,per person, per month with available 
> slots as per the slab. PFA for you.
> *I don't think that is a reasonable expectation.*
&g

$$Excel-Macros$$ Presenting Chart..Need help

[SG]

Hi Experts,

I need your help in presenting the week wise time used in an account. What 
i need is to prepare a chart in which when i select month & name, 
the no. of weeks & its values in that particular month would be the source 
data of chart.

For e.g, if i select 6 as month (which is june) & name as "a", the chart 
would display the time used by "a" in activites of weeks 23,24,25 & 26.

one more chart with if i select 6 as month , name as "a" , chart would 
display time used by "a" account wise in weeks 23,24,25,26.

Please help me with the solution. I have attached the data for you 
reference.

Thanks in advance.

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


utilization.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Re: Presenting Chart..Need help

[SG]

Hi Experts,

Please help.

On Monday, June 6, 2016 at 1:46:58 PM UTC+5:30, SG wrote:
>
> Hi Experts,
>
> I need your help in presenting the week wise time used in an account. What 
> i need is to prepare a chart in which when i select month & name, 
> the no. of weeks & its values in that particular month would be the source 
> data of chart.
>
> For e.g, if i select 6 as month (which is june) & name as "a", the chart 
> would display the time used by "a" in activites of weeks 23,24,25 & 26.
>
> one more chart with if i select 6 as month , name as "a" , chart would 
> display time used by "a" account wise in weeks 23,24,25,26.
>
> Please help me with the solution. I have attached the data for you 
> reference.
>
> Thanks in advance.
>

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Re: Presenting Chart..Need help

[SG]

Thanks for the idea Gowri. I know this trick but this time , i forgot to 
use. :)
Thanks for recalling it.

On Monday, June 6, 2016 at 3:58:16 PM UTC+5:30, a.gowri wrote:
>
> please find the attachment.​
>
> On Mon, Jun 6, 2016 at 3:15 PM, SG > 
> wrote:
>
>> Hi Experts,
>>
>> Please help.
>>
>> On Monday, June 6, 2016 at 1:46:58 PM UTC+5:30, SG wrote:
>>>
>>> Hi Experts,
>>>
>>> I need your help in presenting the week wise time used in an account. 
>>> What i need is to prepare a chart in which when i select month & name, 
>>> the no. of weeks & its values in that particular month would be the 
>>> source data of chart.
>>>
>>> For e.g, if i select 6 as month (which is june) & name as "a", the chart 
>>> would display the time used by "a" in activites of weeks 23,24,25 & 26.
>>>
>>> one more chart with if i select 6 as month , name as "a" , chart would 
>>> display time used by "a" account wise in weeks 23,24,25,26.
>>>
>>> Please help me with the solution. I have attached the data for you 
>>> reference.
>>>
>>> Thanks in advance.
>>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com 
>> .
>> Visit this group at https://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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

$$Excel-Macros$$ Highlight & add data to another sheet - help needed

[SG]

Hi Masters,

Need your help again. I need to do some changes in my file. 

First, when the user enters the start & end time with same day with their 
name, it would become red highlighted & message popped out to change the 
time to void duplicate (row 10 & 13).
Second, when they click the submit button, all data entered in sheet 1 goes 
to master sheet in same format & removes the entered data 
in sheet 1 but leave the formulas in sheet 1 so that next time when user 
enters data, it would update itself & append the data in master sheet
on again submitting it.

I have attached the sheet for your reference. please help with the solution
Thanks in advance.

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


utilization.xlsx
Description: MS-Excel 2007 spreadsheet

$$Excel-Macros$$ Re: Highlight & add data to another sheet - help needed

[SG]

Hi Experts...

please help me on this.

On Tuesday, June 7, 2016 at 4:25:32 PM UTC+5:30, SG wrote:
>
> Hi Masters,
>
> Need your help again. I need to do some changes in my file. 
>
> First, when the user enters the start & end time with same day with their 
> name, it would become red highlighted & message popped out to change the 
> time to void duplicate (row 10 & 13).
> Second, when they click the submit button, all data entered in sheet 1 
> goes to master sheet in same format & removes the entered data 
> in sheet 1 but leave the formulas in sheet 1 so that next time when user 
> enters data, it would update itself & append the data in master sheet
> on again submitting it.
>
> I have attached the sheet for your reference. please help with the solution
> Thanks in advance.
>

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

$$Excel-Macros$$ Re: Highlight & add data to another sheet - help needed

[SG]

Experts, anybody working on this please?

On Tuesday, June 7, 2016 at 4:25:32 PM UTC+5:30, SG wrote:
>
> Hi Masters,
>
> Need your help again. I need to do some changes in my file. 
>
> First, when the user enters the start & end time with same day with their 
> name, it would become red highlighted & message popped out to change the 
> time to void duplicate (row 10 & 13).
> Second, when they click the submit button, all data entered in sheet 1 
> goes to master sheet in same format & removes the entered data 
> in sheet 1 but leave the formulas in sheet 1 so that next time when user 
> enters data, it would update itself & append the data in master sheet
> on again submitting it.
>
> I have attached the sheet for your reference. please help with the solution
> Thanks in advance.
>

-- 
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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Re: $$Excel-Macros$$ Text Cleaning

[SG]

Hi Asa,
 
your code really works exact for me.Thanks for the help but can u please 
explain why you have done this "Character As String * 1".
On Monday, June 4, 2012 9:14:07 PM UTC+5:30, Asa R. wrote:

>  I re-read your query and I see you only wanted alphabet characters.  I 
> had this include numbers as well… for just alphabetical characters A-Z and 
> a-z, here is the correction:
>
> Function CleanText(Text As String) As String
>
> Dim NewText As String, Character As String * 1, Position As Long
>
> For Position = 1 To Len(Text)
>
> Character = Mid(Text, Position, 1)
>
> If Character Like "[A-z]" Then
>
> NewText = NewText & Character
>
> End If
>
> Next Position
>
> CleanText = NewText
>
> End Function
>
>  
>
> By the way, welcome to the group!
>
>  
>
> Asa
>
>  
>  
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Asa Rossoff
> *Sent:* Monday, June 04, 2012 8:38 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* RE: $$Excel-Macros$$ Text Cleaning
>
>  
>
> Hi Sonal,
>
> Another function for you:
>
> Function CleanText(Text As String) As String
>
> Dim NewText As String, Character As String * 1, Position As Long
>
> For Position = 1 To Len(Text)
>
> Character = Mid(Text, Position, 1)
>
> If Character Like "[A-z]" Or Character Like "#" Then
>
> NewText = NewText & Character
>
> End If
>
> Next Position
>
> CleanText = NewText
>
> End Function
>
>  
>
> You can use this as a UDF in a worksheet formula or call it from other 
> macros.
>
>  
>
> Asa
>
>  
>  
> *From:* excel-macros@googlegroups.com [
> mailto:excel-macros@googlegroups.com ] *On 
> Behalf Of *sonal gupta
> *Sent:* Monday, June 04, 2012 8:15 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Text Cleaning
>
>  
>  
> Hi,
>  
>  
>  
> This is my first post in this group.I have learnt many things from this 
> group.I'm finding difficulty in cleaning the range in which each cell has 
> string like " rahul;6e3-".I need a macro which will clean the special 
> characters,spaces & numbers & leave the alphabets only.
>  
>  
>  
> Thanks in advance.
>
> -- 
> 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
>

-- 
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$$ Text Cleaning

[SG]

Hi david.
that "e" was bymistake.I want to clean special characters & spaces & it's 
done by Asa's code.
 
thanks for your concern.
On Tuesday, June 5, 2012 6:11:41 AM UTC+5:30, David Grugeon wrote:

> Hi Sonal 
>
> I hope you realise that if you apply any of the suggested solutions to 
> your string " rahul;6e3-" you will get "rahule" not "rahul".  I hope this 
> is what you want.  If not we might need to see a greater selection of 
> strings to see if there is a pattern (like - always cut them off at the 
> first non-letter).
>
> Regards
> David Grugeon
>
> On 5 June 2012 01:28, sonal gupta  wrote:
>
>> Thanks ashish.I'll try it. 
>>
>> On Monday, June 4, 2012 8:51:06 PM UTC+5:30, ashish wrote: 
>>>
>>> Sub text_clean()
>>> Application.DisplayAlerts = False
>>> Application.Calculation = xlCalculationManual
>>> Dim cell As Range
>>> Dim str As String, text1 As String
>>> For Each cell In Selection
>>> str = ""
>>> text1 = ""
>>> str = cell.Text
>>> For i = 1 To Len(str)
>>> If (Asc(Mid(str, i, 1)) > 64 And Asc(Mid(str, i, 1)) < 90) Or 
>>> (Asc(Mid(str, i, 1)) > 96 And Asc(Mid(str, i, 1)) < 123) Then
>>> text1 = text1 & Mid(str, i, 1)
>>> End If
>>> Next i
>>> cell.Value = text1
>>> Next
>>> Application.DisplayAlerts = True
>>> Application.Calculation = xlCalculationAutomatic
>>> End Sub
>>>
>>>
>>>
>>> On Mon, Jun 4, 2012 at 8:44 PM, sonal gupta  wrote:
>>>
 Hi,
  
 This is my first post in this group.I have learnt many things from this 
 group.I'm finding difficulty in cleaning the range in which each cell has 
 string like " rahul;6e3-".I need a macro which will clean the special 
 characters,spaces & numbers & leave the alphabets only.
  
 Thanks in advance.

 -- 
 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+unsubscribe@**
 googlegroups.com 
>>>
>>>
>>>
>>>
>>> -- 
>>> *Regards*
>>> * *
>>> *Ashish Koul*
>>> *http://www.excelvbamacros.com/*
>>> *http://www.accessvbamacros.com/*  
>>>  
>>>  
>>> P Before printing, think about the environment.
>>>  
>>>
>>> -- 
>> 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
>>
>
>
>
> -- 
> David Grugeon
>

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

Re: $$Excel-Macros$$ Text Cleaning

[SG]

Rajan...Thanks for the help but still your code is returning numbers .
On Monday, June 4, 2012 9:11:22 PM UTC+5:30, Rajan_Verma wrote: 
>
>  Ah , *Cod* is just a integer type *variable* , that will hold the *ACSII 
> Code* or each character in string,
>
>  
>
>  
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>  
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *sonal
> *Sent:* 04 June 2012 9:04
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Text Cleaning
>
>  
>
> again thanks but i know how to run a macro what i was asking is 
> the explanation for "COD" that ypu have used  in your code.
> On Monday, June 4, 2012 8:59:23 PM UTC+5:30, Rajan_Verma wrote: 
>  
> Ok..
>
>  
>
> Press ALT+F11
>
> ALT + I M
>
> Paste this code there
>
>  
>
> Use Function Worksheet Like :
>
> = RemoveSpecialCharacters(YourString)
>
>  
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>  
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *sonal gupta
> *Sent:* 04 June 2012 8:58
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Text Cleaning
>
>  
>  
> Thanks rajan, but i'm new to macros..can you please explain the use of 
> "COD" in this.
>  
>  
>  
>
> On Monday, June 4, 2012 8:51:52 PM UTC+5:30, Rajan_Verma wrote:
>
>  Try this :
>
>  
>
> Public Function RemoveSpecialCharacters(Shname As String) As String
>
> Dim Cod As Integer
>
> Dim ShN As String
>
> For i = 1 To Len(Shname)
>
> Cod = Asc(Mid(Shname, i, 1))
>
> If (Cod > 47 And Cod < 58) Or (Cod > 64 And Cod < 
> 91) Or (Cod > 96 And Cod < 123) Then
>
> ShN = ShN & Mid(Shname, i, 1)
>
> End If
>
> Next
>
> RemoveSpecialCharacters = ShN
>
> End Function
>
>  
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>  
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *sonal gupta
> *Sent:* 04 June 2012 8:45
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Text Cleaning
>
>  
>  
> Hi,
>  
>  
>  
> This is my first post in this group.I have learnt many things from this 
> group.I'm finding difficulty in cleaning the range in which each cell has 
> string like " rahul;6e3-".I need a macro which will clean the special 
> characters,spaces & numbers & leave the alphabets only.
>  
>  
>  
> Thanks in advance.
>
> -- 
> 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
>
> -- 
> 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 securit

$$Excel-Macros$$ Heavy Files on Share drive

[SG]

Hi Experts,
 
I'm in crunch situation.Let me try to explain.We have some Excel files 
named-1,2 & 3 saved on network share of pune & they are really very heavy 
like 40 MB each. What i have to do is to fetch the data of current week for 
eg. week-27.I have analysed them & the trend of data is irregular i.e you 
have the Current week data in all the files.Each files takes so much of 
time to get open.Then, i put weeknum function in the file & copy the data 
at my end.Please suggest any solution(if any macro) if it can be resolved.
 
Thanks
SG

-- 
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$$ Heavy Files on Share drive

[SG]

hi Noorain,
 
thanks alot.I haven't tried this.Before trying, i would like to ask some 
queries.I'm not too good in macros...will this macro reduce the size of 
files placed at share drive or should i open all files on share drive & 
then run this macro at my end??? Please clear my doubts on it.
 
Thanks
 

On Thursday, July 5, 2012 6:46:25 PM UTC+5:30, NOORAIN ANSARI wrote:

> Dear SG.
>
> Please try it..
>
>
>
> Option Explicit
> Sub SHRINK_EXCEL_FILE_SIZE()
>
> Dim WSheet As Worksheet
> Dim CSheet As String 'New Worksheet
> Dim OSheet As String 'Old WorkSheet
> Dim Col As Long
> Dim ECol As Long 'Last Column
> Dim lRow As Long
> Dim BRow As Long 'Last Row
> Dim Pic As Object
>
> For Each WSheet In Worksheets
> WSheet.Activate
>  'Put the sheets in a variable to make it easy to go back and 
> forth
> CSheet = WSheet.Name
>  'Rename the sheet to its name with _Delete at the end
> OSheet = CSheet & "_Delete"
> WSheet.Name = OSheet
>  'Add a new sheet and call it the original sheets name
> Sheets.Add
> ActiveSheet.Name = CSheet
> Sheets(OSheet).Activate
>  'Find the bottom cell of data on each column and find the 
> further row
> For Col = 1 To Columns.Count 'Find the actual last bottom row
> If Cells(Rows.Count, Col).End(xlUp).Row > BRow Then
> BRow = Cells(Rows.Count, Col).End(xlUp).Row
> End If
> Next
>
>  'Find the end cell of data on each row that has data and find 
> the furthest one
> For lRow = 1 To BRow 'Find the actual last right column
> If Cells(lRow, Columns.Count).End(xlToLeft). 
> Column > ECol Then
> ECol = Cells(lRow, Columns.Count).End(xlToLeft).Column
> End If
> Next
>
>  'Copy the REAL set of data
> Range(Cells(1, 1), Cells(BRow, ECol)).Copy
> Sheets(CSheet).Activate
>  'Paste Every Thing
> Range("A1").PasteSpecial xlPasteAll
>  'Paste Column Widths
> Range("A1").PasteSpecial xlPasteColumnWidths
>
> Sheets(OSheet).Activate
> For Each Pic In ActiveSheet.Pictures
> Pic.Copy
> Sheets(CSheet).Paste
> Sheets(CSheet).Pictures(Pic.Index).Top = Pic.Top
> Sheets(CSheet).Pictures(Pic.Index).Left = Pic.Left
> Next Pic
> Sheets(CSheet).Activate
>
>  'Reset the variable for the next sheet
> BRow = 0
> ECol = 0
> Next WSheet
>
>  ' Since, Excel will automatically replace the sheet references for 
> you on your formulas,
>  ' the below part puts them back.
>  ' This is done with a simple replace, replacing _Delete with nothing
> For Each WSheet In Worksheets
> WSheet.Activate
> Cells.Replace "_Delete", ""
> Next WSheet
>
> 'Roll through the sheets and delete the original fat sheets
> For Each WSheet In Worksheets
> If Not Len(Replace(WSheet.Name, "_Delete", "")) = 
> Len(WSheet.Name) Then
> Application.DisplayAlerts = False
> WSheet.Delete
> Application.DisplayAlerts = True
> End If
> Next
> End Sub
>
> http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html
>
> -- 
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com 
> www.excelmacroworld.blogspot.com
>
> On Thu, Jul 5, 2012 at 6:09 PM, SG  wrote:
>
>> Hi Experts,
>>  
>> I'm in crunch situation.Let me try to explain.We have some Excel files 
>> named-1,2 & 3 saved on network share of pune & they are really very heavy 
>> like 40 MB each. What i have to do is to fetch the data of current week for 
>> eg. week-27.I have analysed them & the trend of data is irregular i.e you 
>> have the Current week data in all the files.Each files takes so much of 
>> time to get open.Then, i put weeknum function in the file & copy the data 
>> at my end.Please suggest any solution(if any macro) if it can be resolved.
>>  
>> Thanks
>> SG
>>
>> -- 
>> 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 

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Sorry for Replying late.Yes, i choose delimit & choose comma for it.Problem 
is this that the data is in thousandsr rows & manually it takes so much 
time.i have attached a file & i hope it clears the doubts brfore cleaning 
the data & after cleaning the data.

On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:

> Hi Experts,
>  
> I need your help again.We extract the data from database & each user got 
> some errors on different projects.I need to clean the error data to get the 
> count of errors.For this,i do text to column on error column.Then, in each 
> column after error column, i take the value one by one & copy paste the 
> data below the original data.It's too time consuming as data may expand up 
> to many columns & each column has irregular no. of errors description.I 
> have tried to explain best & need a macro to make unique row of erros of 
> all users.I have attached the excel file & a document in which i have tried 
> to explain.Looking forward for your help.
>  
> Thanks in advance.
>

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

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

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Don,
 
Thanks for the help. I haven't run the macro.I'm a begineer to the 
macro.Can you please explain the "option Explicit" & use of "resize" & 
would i run this macro after text to column splitting?

uillett wrote:

>   Should do it
>  
> Option Explicit
> Sub getlists()
> Dim lr As Long
> Dim i As Long
> Dim dr As Long
> Dim btc As Range
> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
> Set btc = Cells(2, 1).Resize(lr, 2)
> For i = 2 To lr + 1
> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
> btc.Copy Cells(dr, 1)
> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
> 'MsgBox i
> Next i
> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub
> ‘’’’’’
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 3:42 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>  
>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
> it.Problem is this that the data is in thousandsr rows & manually it takes 
> so much time.i have attached a file & i hope it clears the doubts brfore 
> cleaning the data & after cleaning the data.
>
> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>
>> Hi Experts,
>>  
>> I need your help again.We extract the data from database & each user got 
>> some errors on different projects.I need to clean the error data to get the 
>> count of errors.For this,i do text to column on error column.Then, in each 
>> column after error column, i take the value one by one & copy paste the 
>> data below the original data.It's too time consuming as data may expand up 
>> to many columns & each column has irregular no. of errors description.I 
>> have tried to explain best & need a macro to make unique row of erros of 
>> all users.I have attached the excel file & a document in which i have tried 
>> to explain.Looking forward for your help.
>>  
>> Thanks in advance.
>>
> -- 
> 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: Error Cleaning

[SG]

Hi Don,
 
It's working perfectly.but one more problem is this i have shown only 3 
columns in my sample file but in real ...i have atleast 23 columns & last 
column is the Error column on which this macro will work...now this macro 
is consolidating the data at the end.Please have a look.For instance, i 
have increased the column no to 5.

On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:

>   I wrote it to continue what you had already done. The TTC could be 
> incorporated. 
> Option explicit forces use of dim
> Look in vba help index for resize
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 8:49 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
>  Hi Don,
>  
> Thanks for the help. I haven't run the macro.I'm a begineer to the 
> macro.Can you please explain the "option Explicit" & use of "resize" & 
> would i run this macro after text to column splitting?
>
> uillett wrote:
>
>>   Should do it
>>  
>> Option Explicit
>> Sub getlists()
>> Dim lr As Long
>> Dim i As Long
>> Dim dr As Long
>> Dim btc As Range
>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>> Set btc = Cells(2, 1).Resize(lr, 2)
>> For i = 2 To lr + 1
>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>> btc.Copy Cells(dr, 1)
>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>> 'MsgBox i
>> Next i
>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>> End Sub
>> ‘’
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 3:42 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>> so much time.i have attached a file & i hope it clears the doubts brfore 
>> cleaning the data & after cleaning the data.
>>
>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I need your help again.We extract the data from database & each user got 
>>> some errors on different projects.I need to clean the error data to get the 
>>> count of errors.For this,i do text to column on error column.Then, in each 
>>> column after error column, i take the value one by one & copy paste the 
>>> data below the original data.It's too time consuming as data may expand up 
>>> to many columns & each column has irregular no. of errors description.I 
>>> have tried to explain best & need a macro to make unique row of erros of 
>>> all users.I have attached the excel file & a document in which i have tried 
>>> to explain.Looking forward for your help.
>>>  
>>> Thanks in advance.
>>>
>> -- 
>> 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 breaki

Re: $$Excel-Macros$$ Re: Error Cleaning

[SG]

Yes, if you see..from row 12 to row 40, the data is blank..can it be 
possible after running the macro...it would consolidated without such 
blanks in the data??
On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 
>
>
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 9:30 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
>  Hi Don,
>  
> It's working perfectly.but one more problem is this i have shown only 3 
> columns in my sample file but in real ...i have atleast 23 columns & last 
> column is the Error column on which this macro will work...now this macro 
> is consolidating the data at the end.Please have a look.For instance, i 
> have increased the column no to 5.
>
> On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
>
>>   I wrote it to continue what you had already done. The TTC could be 
>> incorporated. 
>> Option explicit forces use of dim
>> Look in vba help index for resize
>>  
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 8:49 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Hi Don,
>>  
>> Thanks for the help. I haven't run the macro.I'm a begineer to the 
>> macro.Can you please explain the "option Explicit" & use of "resize" & 
>> would i run this macro after text to column splitting?
>>
>> uillett wrote:
>>
>>>   Should do it
>>>  
>>> Option Explicit
>>> Sub getlists()
>>> Dim lr As Long
>>> Dim i As Long
>>> Dim dr As Long
>>> Dim btc As Range
>>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>>> Set btc = Cells(2, 1).Resize(lr, 2)
>>> For i = 2 To lr + 1
>>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>>> btc.Copy Cells(dr, 1)
>>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>>> 'MsgBox i
>>> Next i
>>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>>> End Sub
>>> ‘’
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>   
>>>  *From:* SG  
>>> *Sent:* Monday, July 16, 2012 3:42 AM
>>> *To:* excel-macros@googlegroups.com 
>>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>>  
>>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>>> so much time.i have attached a file & i hope it clears the doubts brfore 
>>> cleaning the data & after cleaning the data.
>>>
>>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>>
>>>> Hi Experts,
>>>>  
>>>> I need your help again.We extract the data from database & each user 
>>>> got some errors on different projects.I need to clean the error data to 
>>>> get 
>>>> the count of errors.For this,i do text to column on error column.Then, in 
>>>> each column after error column, i take the value one by one & copy paste 
>>>> the data below the original data.It's too time consuming as data may 
>>>> expand 
>>>> up to many columns & each column has irregular no. of errors description.I 
>>>> have tried to explain best & need a macro to make unique row of erros of 
>>>> all users.I have attached the excel file & a document in which i have 
>>>> tried 
>>>> to explain.Looking forward for your help.
>>>>  
>>>> Thanks in advance.
>>>>
>>> -- 
>>> 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 comp

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Pascal,
 
Thanks for the suggestion but after cleaning the data, we have to perform 
other things in excel.Ialso don't have knowledge of Access,I'll try it.
On Tuesday, July 17, 2012 1:03:29 PM UTC+5:30, bpascal123 wrote:

> Hi SG, 
>
> If you have so many rows and need to have data sorted in a table way, 
> Access might be a better solution than Excel.
> I understand you can have one and only Project number, one and only one 
> person Name for a project and one to many error codes. I don't have strong 
> Access skills but if you have thousand of rows for projects and any 
> multiple for these for error codes Access should perform better than Excel.
>
> Pascal Baro
> bpascal...@gmail.com
>
> On Monday, July 16, 2012 9:42:48 AM UTC+1, SG wrote: 
>>
>> Sorry for Replying late.Yes, i choose delimit & choose comma for 
>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>> so much time.i have attached a file & i hope it clears the doubts brfore 
>> cleaning the data & after cleaning the data.
>>
>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I need your help again.We extract the data from database & each user got 
>>> some errors on different projects.I need to clean the error data to get the 
>>> count of errors.For this,i do text to column on error column.Then, in each 
>>> column after error column, i take the value one by one & copy paste the 
>>> data below the original data.It's too time consuming as data may expand up 
>>> to many columns & each column has irregular no. of errors description.I 
>>> have tried to explain best & need a macro to make unique row of erros of 
>>> all users.I have attached the excel file & a document in which i have tried 
>>> to explain.Looking forward for your help.
>>>  
>>> Thanks in advance.
>>>
>>

-- 
-- 
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: Error Cleaning

[SG]

Hi Don,
 
My question is that after running the macro why data from column A to C 
is repeating & then the data from column A to E comes consolidated below it 
which actually i want.Sorry for asking so much questions.But till the time 
it's not clear to meI'm not able to implement it.

On Monday, July 16, 2012 9:03:57 PM UTC+5:30, Don Guillett wrote:

>   You might try expanding column C to find out that that is NOT 
> true
>  
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>   
>  *From:* SG  
> *Sent:* Monday, July 16, 2012 10:26 AM
> *To:* excel-macros@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>  
> Yes, if you see..from row 12 to row 40, the data is blank..can it be 
> possible after running the macro...it would consolidated without such 
> blanks in the data??
> On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: 
>>
>>
>>  
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>   
>>  *From:* SG  
>> *Sent:* Monday, July 16, 2012 9:30 AM
>> *To:* excel-macros@googlegroups.com 
>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>  
>>  Hi Don,
>>  
>> It's working perfectly.but one more problem is this i have shown only 3 
>> columns in my sample file but in real ...i have atleast 23 columns & last 
>> column is the Error column on which this macro will work...now this macro 
>> is consolidating the data at the end.Please have a look.For instance, i 
>> have increased the column no to 5.
>>
>> On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote:
>>
>>>   I wrote it to continue what you had already done. The TTC could be 
>>> incorporated. 
>>> Option explicit forces use of dim
>>> Look in vba help index for resize
>>>  
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>   
>>>  *From:* SG  
>>> *Sent:* Monday, July 16, 2012 8:49 AM
>>> *To:* excel-macros@googlegroups.com 
>>> *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning
>>>  
>>>  Hi Don,
>>>  
>>> Thanks for the help. I haven't run the macro.I'm a begineer to the 
>>> macro.Can you please explain the "option Explicit" & use of "resize" & 
>>> would i run this macro after text to column splitting?
>>>
>>> uillett wrote:
>>>
>>>>   Should do it
>>>>  
>>>> Option Explicit
>>>> Sub getlists()
>>>> Dim lr As Long
>>>> Dim i As Long
>>>> Dim dr As Long
>>>> Dim btc As Range
>>>> lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
>>>> Set btc = Cells(2, 1).Resize(lr, 2)
>>>> For i = 2 To lr + 1
>>>> dr = Cells(Rows.Count, 1).End(xlUp).Row + 1
>>>> btc.Copy Cells(dr, 1)
>>>> Cells(2, i + 1).Resize(lr).Copy Cells(dr, "c")
>>>> 'MsgBox i
>>>> Next i
>>>> Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>>>> End Sub
>>>> ‘’
>>>> Don Guillett
>>>> Microsoft Excel Developer
>>>> SalesAid Software
>>>> dguille...@gmail.com
>>>>   
>>>>  *From:* SG  
>>>> *Sent:* Monday, July 16, 2012 3:42 AM
>>>> *To:* excel-macros@googlegroups.com 
>>>> *Subject:* $$Excel-Macros$$ Re: Error Cleaning
>>>>  
>>>>  Sorry for Replying late.Yes, i choose delimit & choose comma for 
>>>> it.Problem is this that the data is in thousandsr rows & manually it takes 
>>>> so much time.i have attached a file & i hope it clears the doubts brfore 
>>>> cleaning the data & after cleaning the data.
>>>>
>>>> On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote:
>>>>
>>>>> Hi Experts,
>>>>>  
>>>>> I need your help again.We extract the data from database & each user 
>>>>> got some errors on different projects.I need to clean the error data to 
>>>>> get 
>>>>> the count of errors.For this,i do text to column on error column.Then, in 
>>>>> each column after error column, i take the value one by one & copy paste 
>>>>> the data below the original data.It's too time consuming as data may 
>>>>

$$Excel-Macros$$ Re: Error Cleaning

[SG]

Hi Experts,
 
Please find attached sheet for cleaning. What i want is this after 
splitting , each error gets a unique row like i manually done in the third 
sheet.Problem is that the no. of columns can increase & "Code used by user" 
will always be in the end,so, macro will copy the complete row from "code 
used by user" to "Region" Column & then overwrite the splitted value in the 
"Code used by Coder" value & same would be deleted from splitted column. If 
u need more explanation, please refer document in the my first post for 
this problem.I'm looking forward to the help.
 
Thanks
 

On Tuesday, July 17, 2012 8:32:12 PM UTC+5:30, bpascal123 wrote:

> It's difficult to understand and help as you've posted different version 
> of your data, at first there you start with a certain number of columns and 
> then you end up with different column like :  Study Name Coder(SC) 1.Coder 
> /Qer name for scorecards 2.Type Of Errors Final for Scorecard
> I also try to understand Don's code but honestly it makes me more 
> confused. Try to post the exact thing that you want, that is the data as it 
> comes and the data as you exactly want it
>

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




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

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi pascal,
 
i didn't understand this part of code...what should i write if i run this 
macro in new workbook
 
 Set wbIn = Workbooks("Book1 - Copy.xlsm")
'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
Set wsInOut = wbIn.Worksheets("sorted data")
Set wsIn = wbIn.Worksheets("splitted data")

On Friday, July 20, 2012 2:37:37 AM UTC+5:30, bpascal123 wrote:

>  Hi SG, 
>
> This code below should help you. You can run it from the vba ide in the 
> attached file.
> I'm not as comfortable as Don is with vba objects and methods so maybe 
> Don's code is better for this.
> I don't run if it takes time for many rows, ask again i like to search on 
> the web trick to improve coding syntax.
>
> Hope this answers what you need, 
>
> Pascal Baro
>
>  Sub SGTest()
>
> Dim wbIn As Workbook
> Dim wsIn As Worksheet
> Dim wsInOut As Worksheet
> Dim v() As Variant
> Dim r As Range
> Dim rCol As Integer
> Dim i As Long, j As Long
> Dim lastr As Long, lastc As Long
>
> Set wbIn = Workbooks("Book1 - Copy.xlsm")
> 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
> Set wsInOut = wbIn.Worksheets("sorted data")
> Set wsIn = wbIn.Worksheets("splitted data")
>
> With Application
> .Calculation = xlCalculationManual
> .DisplayAlerts = False
> .ScreenUpdating = False
> End With
>
> lastc = 0
> wsIn.Rows(1).Copy wsInOut.Rows(1)
> lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row
>
> For i = lastr To 2 Step -1
>
> Set r = wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i, 
> wsIn.Columns.Count).End(xlToLeft))
> rCol = r.Columns.Count
> 
> If rCol > 1 Then
> v = Array(r)
> wsIn.Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> wsIn.Range(wsIn.Cells(i, 1), wsIn.Cells(i, 7)).Copy _
> wsIn.Range(wsIn.Cells(i + 1, 1), wsIn.Cells(i + rCol - 1, 7))
> 
> wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i + rCol - 1, 8)).Value = _
> Application.WorksheetFunction.Transpose(v)
> 
> If rCol > lastc Then lastc = rCol
> 
> End If
> 
> Next i
>
> lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row
> wsIn.Range(wsIn.Cells(1, 9), wsIn.Cells(lastr, lastc + 9)).ClearContents
>
> With Application
> .Calculation = xlCalculationAutomatic
> .DisplayAlerts = True
> .ScreenUpdating = True
> End With
>
> Set r = Nothing
> Set wbIn = Nothing
> Set wsIn = Nothing
> Set wsInOut = Nothing
>
> End Sub
>
>

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

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi Pascal
 
Thanku so muchit really helped meappreciate your help.& even it 
increased my knowledge in VB...
plz explain below part of code...& thanks again
 
If rCol > 1 Then 
v = Array(r)
.Rows(i + 1 & ":" & i + rCol - 1).Insert

.Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
1), .Cells(i + rCol - 1, 7))

.Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _

On Saturday, July 21, 2012 9:05:09 PM UTC+5:30, bpascal123 wrote:

>
> This line should allow you to run this macro from the workbook where the 
> data is ... if the file is not xlsx (it can then be xls, xlsm, xlsb ) 
> Set wbIn = ThisWorkbook
>
> (the line...worksheet add... that was commented out i forgot to delete 
> wouldn't work anyway-i just found it easier to run the code directly on the 
> splitted data sheet)
>
> and one more change in the complete code below (with method was missing in 
> what i sent previously):
>
> ---
>  Sub SGTest()
>
> Dim wbIn As Workbook
> Dim wsIn As Worksheet
> Dim wsInOut As Worksheet
> Dim v() As Variant
> Dim r As Range
> Dim rCol As Integer
> Dim i As Long, j As Long
> Dim lastr As Long, lastc As Long
>
> Set wbIn = ThisWorkbook
> Set wsInOut = wbIn.Worksheets("sorted data")
> Set wsIn = wbIn.Worksheets("splitted data")
>
> With Application
> .Calculation = xlCalculationManual
> .DisplayAlerts = False
> .ScreenUpdating = False
> End With
>
> With wsIn
>
> lastc = 0
> .Rows(1).Copy wsInOut.Rows(1)
> lastr = .Cells(Rows.Count, 8).End(xlUp).Row
>
> For i = lastr To 2 Step -1
> 
> Set r = .Range(.Cells(i, 8), .Cells(i, 
> .Columns.Count).End(xlToLeft))
> rCol = r.Columns.Count
> 
> If rCol > 1 Then
> v = Array(r)
> .Rows(i + 1 & ":" & i + rCol - 1).Insert
> 
> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
> 1), .Cells(i + rCol - 1, 7))
> 
> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
> Application.WorksheetFunction.Transpose(v)
> 
> If rCol > lastc Then lastc = rCol
> 
> End If
> 
> Next i
> 
> lastr = .Cells(Rows.Count, 8).End(xlUp).Row
> .Range(wsIn.Cells(1, 9), .Cells(lastr, lastc + 9)).ClearContents
>
> End With
>
>
> With Application
> .Calculation = xlCalculationAutomatic
> .DisplayAlerts = True
> .ScreenUpdating = True
> End With
>
> Set r = Nothing
> Set wbIn = Nothing
> Set wsIn = Nothing
> Set wsInOut = Nothing
>
> End Sub
>
> ---
> Pascal
>
>
> On Friday, July 20, 2012 2:34:04 PM UTC+1, SG wrote: 
>>
>> hi pascal,
>>  
>> i didn't understand this part of code...what should i write if i run this 
>> macro in new workbook
>>  
>>  Set wbIn = Workbooks("Book1 - Copy.xlsm")
>> 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = "sorted data ok2"
>> Set wsInOut = wbIn.Worksheets("sorted data")
>> Set wsIn = wbIn.Worksheets("splitted data")
>>
>

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

$$Excel-Macros$$ Re: Error Cleaning

[SG]

hi pascal,
 
Thanks for explaining.It really helped me.I'm a beginner in macros so i 
used to follow all experts.

On Tuesday, July 24, 2012 5:48:08 PM UTC+5:30, bpascal123 wrote:

> This forum is a great place to learn, contributors I closely follow; 
> Noorain, Asa, Don...
>
> On Monday, July 23, 2012 5:25:31 PM UTC+1, bpascal123 wrote: 
>>
>>  My Vba coding style is much inspired from Kris who from what I know, 
>> posts here and on  excelfox.com. If you can read his code, you should 
>> learn many tricks. 
>>
>> If rCol > 1 Then
>>> v = Array(r)
>>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>>> 
>>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>>> 1), .Cells(i + rCol - 1, 7))
>>> 
>>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>>
>>  
>>
>> v = Array(r) could actually be changed to v = r or v = r.value
>> v is an array that stores each code used by user column. Using an array 
>> was the only option since later the code calls a copy transpose method but 
>> as a range (opposite to array), copy transpose can't be used on the same 
>> range as for instance H8 would keep the first value, H9 the second. Using a 
>> range copy method, I would have had to insert an additional row to 
>> transpose the data and then delete the row 8, I hope you can follow me
>>
>> .Rows(i + 1 & ":" & i + rCol - 1).Insert 
>> rCol is the variable that stores the number of code used by user, as i'm 
>> using an array, i can keep the value in the first row and add just a new 
>> row (still for H8 example)
>>
>>  .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), 
>> .Cells(i + rCol - 1, 7)) 
>> This is to copy the data from the initial line to the row i'm 
>> adding...rCol-1 same as line above rCol-1
>>
>>  .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>> Application.WorksheetFunction.Transpose(v)
>>
>> This took me some time to figure out, without this transpose function, I 
>> would have add to another loop... transpose just re-arrange data from row 
>> to column or the other way around, here v is the array that stores the code 
>> used by user values for one project or one row.
>>
>> If rCol > lastc Then lastc = rCol 
>>
>> This is for cleaning purpose, lastc stores the latest column used so to 
>> delete from H column to that column only instead of using a range H to 
>> columns.count clear contents method that could mess with your worksheet 
>> formatting or else
>>
>> ---
>> As these line are part of a loop, you can see this code in action by 
>> setting a break point (a big red dot on the left margin of a line of code) 
>> using the Vba IDE at the entry of the for loop, click Run until the break 
>> point you have inserted is highlighted in yellow, then minimize your Vba 
>> IDE window so you can see your worksheet and the data as well as some line 
>> of the Vba IDE (horizontal tile window like), then press once F8, look the 
>> position of the code advancing, and look for any changes on the data such 
>> as inserting rows, copy paste transpose...
>>
>> You can also check the value of variables or object using the watch or 
>> debug window. The watch window is more straightforward since you just need 
>> to right click a variable like v or an object like r and select "add 
>> watch", you will then be able to monitor values stored in an object or 
>> variable.
>>
>>
>> I think if you already have some knowledge of Vba, you can use these Vba 
>> tools as I'm trying to present you.
>>
>> Pascal Baro
>>
>>
>> On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote: 
>>>
>>> hi Pascal
>>>  
>>> Thanku so muchit really helped meappreciate your help.& even 
>>> it increased my knowledge in VB...
>>> plz explain below part of code...& thanks again
>>>  
>>> If rCol > 1 Then 
>>> v = Array(r)
>>> .Rows(i + 1 & ":" & i + rCol - 1).Insert
>>> 
>>> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 
>>> 1), .Cells(i + rCol - 1, 7))
>>> 
>>> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _
>>>
>>

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

1) Use concise, accurate

$$Excel-Macros$$ Calculated Field not Working

[SG]

Hi Experts,
 
I'm again in to a problem whic is silly but still i can't make it.I 
calculate the scores for individuals seperately but this time i want to 
make all under one table.For this, i have created a pivot table & for 
Calculating the Score, I have used Calculated field which is not giving me 
the right ans.I'm attaching the sheet.Plz look in to this.I have 
highlighted the column for which i nedd your help.
 
thanks in advance

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




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

$$Excel-Macros$$ Re: Calculated Field not Working

[SG]

Experts,
 
plz help mei need solution.plz look in to this 

On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote:

> Hi Experts,
>  
> I'm again in to a problem whic is silly but still i can't make it.I 
> calculate the scores for individuals seperately but this time i want to 
> make all under one table.For this, i have created a pivot table & for 
> Calculating the Score, I have used Calculated field which is not giving me 
> the right ans.I'm attaching the sheet.Plz look in to this.I have 
> highlighted the column for which i nedd your help.
>  
> thanks in advance
>

-- 
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: Calculated Field not Working

[SG]

hi vabz
 
I didn't get your point. I'm not able to catch your new column.Still the 
result is same.plz help.

On Wednesday, September 19, 2012 3:23:43 PM UTC+5:30, Vabz wrote:

> hi 
>
> PFA
>
> Check this i have added new column in Pvt Table..
>
> After updating value right click Pvt Tble & select refresh button if you 
> are not doing same to update value.. 
>
> Cheerz..
>
> Rgds//Vabs
>
> On Wed, Sep 19, 2012 at 2:45 PM, SG >wrote:
>
>> Experts,
>>  
>> plz help mei need solution.plz look in to this 
>>
>> On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I'm again in to a problem whic is silly but still i can't make it.I 
>>> calculate the scores for individuals seperately but this time i want to 
>>> make all under one table.For this, i have created a pivot table & for 
>>> Calculating the Score, I have used Calculated field which is not giving me 
>>> the right ans.I'm attaching the sheet.Plz look in to this.I have 
>>> highlighted the column for which i nedd your help.
>>>  
>>> thanks in advance
>>>
>> -- 
>> 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-...@googlegroups.com
>> .
>> To unsubscribe from this group, send email to 
>> excel-macros...@googlegroups.com .
>>  
>>  
>>
>
>

-- 
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: Calculated Field not Working

[SG]

hi Chetan/vabz
 
thanks for the help. I got the solutions.plz explain what you have done to 
get the ans.
i don't know that whose file is this which gave me ans.
plz explain.

On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote:

> check..if these help. check and let me know
>  
> Regards
> CK
>
> On Wed, Sep 19, 2012 at 2:45 PM, SG >wrote:
>
>> Experts,
>>  
>> plz help mei need solution.plz look in to this 
>>
>> On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote:
>>
>>> Hi Experts,
>>>  
>>> I'm again in to a problem whic is silly but still i can't make it.I 
>>> calculate the scores for individuals seperately but this time i want to 
>>> make all under one table.For this, i have created a pivot table & for 
>>> Calculating the Score, I have used Calculated field which is not giving me 
>>> the right ans.I'm attaching the sheet.Plz look in to this.I have 
>>> highlighted the column for which i nedd your help.
>>>  
>>> thanks in advance
>>>
>> -- 
>> 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-...@googlegroups.com
>> .
>> To unsubscribe from this group, send email to 
>> excel-macros...@googlegroups.com .
>>  
>>  
>>
>
>
>
> -- 
>
> Chethan
>
>

-- 
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: Calculated Field not Working

[SG]

hi Vabz,
 
Still i'm not able to find the difference.Though i have also used the same 
formula in calculated field in my sheet.
Plz let me know...so that i would be able to help it in other sheets.

On Thursday, September 20, 2012 6:59:33 PM UTC+5:30, Vabz wrote:

> Hi, 
>
> Just use formula insert field button to define formula.. thats it.
>
> There was error in formula given by me & was working well in chetan's file.
>
> Rgds//Vabs
>
> On Thursday, September 20, 2012 6:54:45 PM UTC+5:30, SG wrote: 
>>
>> hi Chetan/vabz
>>  
>> thanks for the help. I got the solutions.plz explain what you have done 
>> to get the ans.
>> i don't know that whose file is this which gave me ans.
>> plz explain.
>>
>> On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote:
>>
>>> check..if these help. check and let me know
>>>  
>>> Regards
>>> CK
>>>
>>> On Wed, Sep 19, 2012 at 2:45 PM, SG  wrote:
>>>
>>>> Experts,
>>>>  
>>>> plz help mei need solution.plz look in to this 
>>>>
>>>> On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote:
>>>>
>>>>> Hi Experts,
>>>>>  
>>>>> I'm again in to a problem whic is silly but still i can't make it.I 
>>>>> calculate the scores for individuals seperately but this time i want to 
>>>>> make all under one table.For this, i have created a pivot table & for 
>>>>> Calculating the Score, I have used Calculated field which is not giving 
>>>>> me 
>>>>> the right ans.I'm attaching the sheet.Plz look in to this.I have 
>>>>> highlighted the column for which i nedd your help.
>>>>>  
>>>>> thanks in advance
>>>>>
>>>> -- 
>>>> 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-...@googlegroups.com.
>>>> To unsubscribe from this group, send email to 
>>>> excel-macros...@googlegroups.com.
>>>>  
>>>>  
>>>>
>>>
>>>
>>>
>>> -- 
>>>
>>> Chethan
>>>
>>>

-- 
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: Calculated Field not Working

[SG]

any update plz...
plz help me to understand this.
On Monday, September 24, 2012 9:06:59 PM UTC+5:30, SG wrote:

> hi Vabz,
>  
> Still i'm not able to find the difference.Though i have also used the same 
> formula in calculated field in my sheet.
> Plz let me know...so that i would be able to help it in other sheets.
>
> On Thursday, September 20, 2012 6:59:33 PM UTC+5:30, Vabz wrote:
>
>> Hi, 
>>
>> Just use formula insert field button to define formula.. thats it.
>>
>> There was error in formula given by me & was working well in chetan's 
>> file.
>>
>> Rgds//Vabs
>>
>> On Thursday, September 20, 2012 6:54:45 PM UTC+5:30, SG wrote: 
>>>
>>> hi Chetan/vabz
>>>  
>>> thanks for the help. I got the solutions.plz explain what you have done 
>>> to get the ans.
>>> i don't know that whose file is this which gave me ans.
>>> plz explain.
>>>
>>> On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote:
>>>
>>>> check..if these help. check and let me know
>>>>  
>>>> Regards
>>>> CK
>>>>
>>>> On Wed, Sep 19, 2012 at 2:45 PM, SG  wrote:
>>>>
>>>>> Experts,
>>>>>  
>>>>> plz help mei need solution.plz look in to this 
>>>>>
>>>>> On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote:
>>>>>
>>>>>> Hi Experts,
>>>>>>  
>>>>>> I'm again in to a problem whic is silly but still i can't make it.I 
>>>>>> calculate the scores for individuals seperately but this time i want to 
>>>>>> make all under one table.For this, i have created a pivot table & for 
>>>>>> Calculating the Score, I have used Calculated field which is not giving 
>>>>>> me 
>>>>>> the right ans.I'm attaching the sheet.Plz look in to this.I have 
>>>>>> highlighted the column for which i nedd your help.
>>>>>>  
>>>>>> thanks in advance
>>>>>>
>>>>> -- 
>>>>> 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-...@googlegroups.com.
>>>>> To unsubscribe from this group, send email to 
>>>>> excel-macros...@googlegroups.com.
>>>>>  
>>>>>  
>>>>>
>>>>
>>>>
>>>>
>>>> -- 
>>>>
>>>> Chethan
>>>>
>>>>

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

$$Excel-Macros$$ Create user wise feedback sheet

[SG]

Hi Experts,

I need your help in preparation of a feedback sheet through macro. My 
requirement is to create name wise feedback worksheet in a workbook
 which contains  their data from "scorecard" sheet. one of the criteria is 
to create the name wise sheet for  only region "uk" & location "b" users.
I have attached feedback sheet in which "Column C" would remain fixed for 
all user sheet & also mentioned the columns names of Scorecard sheet
 from which data would get extracted. Scorecard sheet is also attached.

I'm really confused how to begin with it. Please help me in preparing it.
Thanks in advance.

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


feedback.xlsx
Description: MS-Excel 2007 spreadsheet


Scorecard.xlsx
Description: MS-Excel 2007 spreadsheet

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Hi Vabz,

Through macro because this would be prepared monthly. for this, it would 
open 4 scorecard files of a month,consolidate their scores & then prepare 
the feedback.

On Wednesday, August 20, 2014 1:07:07 PM UTC+5:30, Vabz wrote:
>
> hi
>
> how do you want to achieve result thru formula or macro??
>
> Cheers!!
>
>
>
> On Tue, Aug 19, 2014 at 7:39 PM, SG > 
> wrote:
>
>> Hi Experts,
>>
>> I need your help in preparation of a feedback sheet through macro. My 
>> requirement is to create name wise feedback worksheet in a workbook
>>  which contains  their data from "scorecard" sheet. one of the criteria 
>> is to create the name wise sheet for  only region "uk" & location "b" users.
>> I have attached feedback sheet in which "Column C" would remain fixed for 
>> all user sheet & also mentioned the columns names of Scorecard sheet
>>  from which data would get extracted. Scorecard sheet is also attached.
>>
>> I'm really confused how to begin with it. Please help me in preparing it.
>> Thanks in advance.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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$$ Create user wise feedback sheet

[SG]

Hi Vabz,

Thanks so much for the help but it's not solving the purpose. User wise 
seperate sheets are not made in feedback workbook & also the values of 
errors for "rahul" are incorrect.
Can you please check it. Also i didn't get the logic behind clicking the 
name (col d) part.

On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>
> FA macro.
>
> Also if you want to update single sheet then keep both feedback sheet & 
> scorecard sheet open together then in scorecard book double click on name 
> (col D) and u will get prompted to select sheet to update with existing 
> values.
>
> Cheers!!
>
>
> On Tue, Aug 19, 2014 at 7:39 PM, SG > 
> wrote:
>
>> Hi Experts,
>>
>> I need your help in preparation of a feedback sheet through macro. My 
>> requirement is to create name wise feedback worksheet in a workbook
>>  which contains  their data from "scorecard" sheet. one of the criteria 
>> is to create the name wise sheet for  only region "uk" & location "b" users.
>> I have attached feedback sheet in which "Column C" would remain fixed for 
>> all user sheet & also mentioned the columns names of Scorecard sheet
>>  from which data would get extracted. Scorecard sheet is also attached.
>>
>> I'm really confused how to begin with it. Please help me in preparing it.
>> Thanks in advance.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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$$ Create user wise feedback sheet

[SG]

Hi Vabz,

i'm really not getting it. how to work. on double click on name, it asks 
"please  select one of file". How hsould i start & how would it create 
userwise seperate sheets.
Please help out.

On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote:
>
> ok, chek this!!
>
> Cheers!!
>
>
> On Wed, Aug 20, 2014 at 6:11 PM, SG > 
> wrote:
>
>> Hi Vabz,
>>
>> Thanks so much for the help but it's not solving the purpose. User wise 
>> seperate sheets are not made in feedback workbook & also the values of 
>> errors for "rahul" are incorrect.
>> Can you please check it. Also i didn't get the logic behind clicking the 
>> name (col d) part.
>>
>> On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>>
>>> FA macro.
>>>
>>> Also if you want to update single sheet then keep both feedback sheet & 
>>> scorecard sheet open together then in scorecard book double click on name 
>>> (col D) and u will get prompted to select sheet to update with existing 
>>> values.
>>>
>>> Cheers!!
>>>
>>>
>>> On Tue, Aug 19, 2014 at 7:39 PM, SG  wrote:
>>>
>>>> Hi Experts,
>>>>
>>>> I need your help in preparation of a feedback sheet through macro. My 
>>>> requirement is to create name wise feedback worksheet in a workbook
>>>>  which contains  their data from "scorecard" sheet. one of the criteria 
>>>> is to create the name wise sheet for  only region "uk" & location "b" 
>>>> users.
>>>> I have attached feedback sheet in which "Column C" would remain fixed 
>>>> for all user sheet & also mentioned the columns names of Scorecard sheet
>>>>  from which data would get extracted. Scorecard sheet is also attached.
>>>>
>>>> I'm really confused how to begin with it. Please help me in preparing 
>>>> it.
>>>> Thanks in advance.
>>>>
>>>> -- 
>>>> 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...@googlegroups.com.
>>>> To post to this group, send email to excel-...@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.
>> --- 

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Hi Vabz

Great.. :) Thanku so much..please only one change is needed in total 
external errors & internal errors(row 9 &10 of feedback sheet), the sum of 
errors should come not the score.
Please can you change it.
Thanks alot ...

On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote:
>
> hi
>
> dont use double click, it is for only single sheet update.
>
> You have to just run a macro, check this file, click blue button..
>
> Make sure both files are open.
>
> Cheers!!
>
>
>
> On Wed, Aug 20, 2014 at 8:20 PM, SG > 
> wrote:
>
>> Hi Vabz,
>>
>> i'm really not getting it. how to work. on double click on name, it asks 
>> "please  select one of file". How hsould i start & how would it create 
>> userwise seperate sheets.
>> Please help out.
>>
>>
>> On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote:
>>
>>> ok, chek this!!
>>>
>>> Cheers!!
>>>
>>>
>>> On Wed, Aug 20, 2014 at 6:11 PM, SG  wrote:
>>>
>>>> Hi Vabz,
>>>>
>>>> Thanks so much for the help but it's not solving the purpose. User wise 
>>>> seperate sheets are not made in feedback workbook & also the values of 
>>>> errors for "rahul" are incorrect.
>>>> Can you please check it. Also i didn't get the logic behind clicking 
>>>> the name (col d) part.
>>>>
>>>> On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>>>>
>>>>> FA macro.
>>>>>
>>>>> Also if you want to update single sheet then keep both feedback sheet 
>>>>> & scorecard sheet open together then in scorecard book double click on 
>>>>> name 
>>>>> (col D) and u will get prompted to select sheet to update with existing 
>>>>> values.
>>>>>
>>>>> Cheers!!
>>>>>
>>>>>
>>>>> On Tue, Aug 19, 2014 at 7:39 PM, SG  wrote:
>>>>>
>>>>>> Hi Experts,
>>>>>>
>>>>>> I need your help in preparation of a feedback sheet through macro. My 
>>>>>> requirement is to create name wise feedback worksheet in a workbook
>>>>>>  which contains  their data from "scorecard" sheet. one of the 
>>>>>> criteria is to create the name wise sheet for  only region "uk" & 
>>>>>> location 
>>>>>> "b" users.
>>>>>> I have attached feedback sheet in which "Column C" would remain fixed 
>>>>>> for all user sheet & also mentioned the columns names of Scorecard sheet
>>>>>>  from which data would get extracted. Scorecard sheet is also 
>>>>>> attached.
>>>>>>
>>>>>> I'm really confused how to begin with it. Please help me in preparing 
>>>>>> it.
>>>>>> Thanks in advance.
>>>>>>
>>>>>> -- 
>>>>>> 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...@googlegroups

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Vabz sirhats offthnku so much...really gr88
now i have some questions so that i can clear my doubt. what you have 
created a code name " create old feedback " & what is the use of 
Option Explicit
Public MyFile As String
Public Stopped As Boolean



On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote:
>
> FA
>
> Cheers!!
>
>
> On Thu, Aug 21, 2014 at 1:43 PM, SG > 
> wrote:
>
>> Hi Vabz
>>
>> Great.. :) Thanku so much..please only one change is needed in total 
>> external errors & internal errors(row 9 &10 of feedback sheet), the sum of 
>> errors should come not the score.
>> Please can you change it.
>> Thanks alot ...
>>
>>
>> On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote:
>>
>>> hi
>>>
>>> dont use double click, it is for only single sheet update.
>>>
>>> You have to just run a macro, check this file, click blue button..
>>>
>>> Make sure both files are open.
>>>
>>> Cheers!!
>>>
>>>
>>>
>>> On Wed, Aug 20, 2014 at 8:20 PM, SG  wrote:
>>>
>>>> Hi Vabz,
>>>>
>>>> i'm really not getting it. how to work. on double click on name, it 
>>>> asks "please  select one of file". How hsould i start & how would it 
>>>> create 
>>>> userwise seperate sheets.
>>>> Please help out.
>>>>
>>>>
>>>> On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote:
>>>>
>>>>> ok, chek this!!
>>>>>
>>>>> Cheers!!
>>>>>
>>>>>
>>>>> On Wed, Aug 20, 2014 at 6:11 PM, SG  wrote:
>>>>>
>>>>>> Hi Vabz,
>>>>>>
>>>>>> Thanks so much for the help but it's not solving the purpose. User 
>>>>>> wise seperate sheets are not made in feedback workbook & also the values 
>>>>>> of 
>>>>>> errors for "rahul" are incorrect.
>>>>>> Can you please check it. Also i didn't get the logic behind clicking 
>>>>>> the name (col d) part.
>>>>>>
>>>>>> On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>>>>>>
>>>>>>> FA macro.
>>>>>>>
>>>>>>> Also if you want to update single sheet then keep both feedback 
>>>>>>> sheet & scorecard sheet open together then in scorecard book double 
>>>>>>> click 
>>>>>>> on name (col D) and u will get prompted to select sheet to update with 
>>>>>>> existing values.
>>>>>>>
>>>>>>> Cheers!!
>>>>>>>
>>>>>>>
>>>>>>> On Tue, Aug 19, 2014 at 7:39 PM, SG  wrote:
>>>>>>>
>>>>>>>> Hi Experts,
>>>>>>>>
>>>>>>>> I need your help in preparation of a feedback sheet through macro. 
>>>>>>>> My requirement is to create name wise feedback worksheet in a workbook
>>>>>>>>  which contains  their data from "scorecard" sheet. one of the 
>>>>>>>> criteria is to create the name wise sheet for  only region "uk" & 
>>>>>>>> location 
>>>>>>>> "b" users.
>>>>>>>> I have attached feedback sheet in which "Column C" would remain 
>>>>>>>> fixed for all user sheet & also mentioned the columns names of 
>>>>>>>> Scorecard 
>>>>>>>> sheet
>>>>>>>>  from which data would get extracted. Scorecard sheet is also 
>>>>>>>> attached.
>>>>>>>>
>>>>>>>> I'm really confused how to begin with it. Please help me in 
>>>>>>>> preparing it.
>>>>>>>> Thanks in advance.
>>>>>>>>
>>>>>>>> -- 
>>>>>>>> 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
>>>>>>&g

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Thanku so much vabz for clarification...

On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>
> Old module is for creating files in wrokbook insted in feedback sheet.
>
> I have also created Macro for updating only 1 sheet, wherein you needed to 
> keep open scorecard & feedback book. After that doble click on name of 
> person whose record you wanted to update, post double clicking on name a 
> userform will appear in which you will be asked to select excel book in 
> which employee sheet is situated & post selecting wb name (feedback sheet 
> in your case) data will get updated.
>
> Cheers!!
>
> On Aug 21, 2014 5:24 PM, "SG" > wrote:
>
>> Vabz sirhats offthnku so much...really gr88
>> now i have some questions so that i can clear my doubt. what you have 
>> created a code name " create old feedback " & what is the use of 
>> Option Explicit
>> Public MyFile As String
>> Public Stopped As Boolean
>>
>>
>>
>> On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote:
>>>
>>> FA
>>>
>>> Cheers!!
>>>
>>>
>>> On Thu, Aug 21, 2014 at 1:43 PM, SG  wrote:
>>>
>>>> Hi Vabz
>>>>
>>>> Great.. :) Thanku so much..please only one change is needed in 
>>>> total external errors & internal errors(row 9 &10 of feedback sheet), the 
>>>> sum of errors should come not the score.
>>>> Please can you change it.
>>>> Thanks alot ...
>>>>
>>>>
>>>> On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote:
>>>>
>>>>> hi
>>>>>
>>>>> dont use double click, it is for only single sheet update.
>>>>>
>>>>> You have to just run a macro, check this file, click blue button..
>>>>>
>>>>> Make sure both files are open.
>>>>>
>>>>> Cheers!!
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Aug 20, 2014 at 8:20 PM, SG  wrote:
>>>>>
>>>>>> Hi Vabz,
>>>>>>
>>>>>> i'm really not getting it. how to work. on double click on name, it 
>>>>>> asks "please  select one of file". How hsould i start & how would it 
>>>>>> create 
>>>>>> userwise seperate sheets.
>>>>>> Please help out.
>>>>>>
>>>>>>
>>>>>> On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote:
>>>>>>
>>>>>>> ok, chek this!!
>>>>>>>
>>>>>>> Cheers!!
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Aug 20, 2014 at 6:11 PM, SG  wrote:
>>>>>>>
>>>>>>>> Hi Vabz,
>>>>>>>>
>>>>>>>> Thanks so much for the help but it's not solving the purpose. User 
>>>>>>>> wise seperate sheets are not made in feedback workbook & also the 
>>>>>>>> values of 
>>>>>>>> errors for "rahul" are incorrect.
>>>>>>>> Can you please check it. Also i didn't get the logic behind 
>>>>>>>> clicking the name (col d) part.
>>>>>>>>
>>>>>>>> On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>>>>>>>>
>>>>>>>>> FA macro.
>>>>>>>>>
>>>>>>>>> Also if you want to update single sheet then keep both feedback 
>>>>>>>>> sheet & scorecard sheet open together then in scorecard book double 
>>>>>>>>> click 
>>>>>>>>> on name (col D) and u will get prompted to select sheet to update 
>>>>>>>>> with 
>>>>>>>>> existing values.
>>>>>>>>>
>>>>>>>>> Cheers!!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Tue, Aug 19, 2014 at 7:39 PM, SG  wrote:
>>>>>>>>>
>>>>>>>>>> Hi Experts,
>>>>>>>>>>
>>>>>>>>>> I need your help in preparation of a feedback sheet through 
>>>>>>>>>> macro. My requirement is to create name wise feedback worksheet in a 
>&

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Hi Vabzzz..

I need one more help in this. As you have seen the scorecard file, that 
should be summary of four weeks & thehn the feedback would be prepared. Can 
you please also help me in consolidation of those four files in to one 
file. It would be a great help. 

On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote:
>
> Thanku so much vabz for clarification...
>
> On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>>
>> Old module is for creating files in wrokbook insted in feedback sheet.
>>
>> I have also created Macro for updating only 1 sheet, wherein you needed 
>> to keep open scorecard & feedback book. After that doble click on name of 
>> person whose record you wanted to update, post double clicking on name a 
>> userform will appear in which you will be asked to select excel book in 
>> which employee sheet is situated & post selecting wb name (feedback sheet 
>> in your case) data will get updated.
>>
>> Cheers!!
>>
>> On Aug 21, 2014 5:24 PM, "SG"  wrote:
>>
>>> Vabz sirhats offthnku so much...really gr88
>>> now i have some questions so that i can clear my doubt. what you have 
>>> created a code name " create old feedback " & what is the use of 
>>> Option Explicit
>>> Public MyFile As String
>>> Public Stopped As Boolean
>>>
>>>
>>>
>>> On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote:
>>>>
>>>> FA
>>>>
>>>> Cheers!!
>>>>
>>>>
>>>> On Thu, Aug 21, 2014 at 1:43 PM, SG  wrote:
>>>>
>>>>> Hi Vabz
>>>>>
>>>>> Great.. :) Thanku so much..please only one change is needed in 
>>>>> total external errors & internal errors(row 9 &10 of feedback sheet), the 
>>>>> sum of errors should come not the score.
>>>>> Please can you change it.
>>>>> Thanks alot ...
>>>>>
>>>>>
>>>>> On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote:
>>>>>
>>>>>> hi
>>>>>>
>>>>>> dont use double click, it is for only single sheet update.
>>>>>>
>>>>>> You have to just run a macro, check this file, click blue button..
>>>>>>
>>>>>> Make sure both files are open.
>>>>>>
>>>>>> Cheers!!
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Aug 20, 2014 at 8:20 PM, SG  wrote:
>>>>>>
>>>>>>> Hi Vabz,
>>>>>>>
>>>>>>> i'm really not getting it. how to work. on double click on name, it 
>>>>>>> asks "please  select one of file". How hsould i start & how would it 
>>>>>>> create 
>>>>>>> userwise seperate sheets.
>>>>>>> Please help out.
>>>>>>>
>>>>>>>
>>>>>>> On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote:
>>>>>>>
>>>>>>>> ok, chek this!!
>>>>>>>>
>>>>>>>> Cheers!!
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Aug 20, 2014 at 6:11 PM, SG  wrote:
>>>>>>>>
>>>>>>>>> Hi Vabz,
>>>>>>>>>
>>>>>>>>> Thanks so much for the help but it's not solving the purpose. User 
>>>>>>>>> wise seperate sheets are not made in feedback workbook & also the 
>>>>>>>>> values of 
>>>>>>>>> errors for "rahul" are incorrect.
>>>>>>>>> Can you please check it. Also i didn't get the logic behind 
>>>>>>>>> clicking the name (col d) part.
>>>>>>>>>
>>>>>>>>> On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote:
>>>>>>>>>
>>>>>>>>>> FA macro.
>>>>>>>>>>
>>>>>>>>>> Also if you want to update single sheet then keep both feedback 
>>>>>>>>>> sheet & scorecard sheet open together then in scorecard book double 
>>>>>>>>>> click 
>>>>>>>>>> on name (col D) and u will get prompted t

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Hi Vabz,

Any update on this. Can it be consolidated in any other way.

please,can anybody else also look in to this.

On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote:
>
> some of the criterias are to consolidate the
> scores of users for region "UK" & location "b". The header & yellow 
> highlighted column(formulas) wouldn't change in "
> consolidate scorecard." sheet. It would sum the values of column F,G 
> ,(J,K,L) & (N,O,P) from scorecard files.
> Also for column R - TAT , if all are hit, then it would be hit but it 
> would be miss even if one of them is miss. I have attached two
> scorecards & one consolidate file in which summary would come but this 
> would gonna work for atleast 4 scorecard files which would kept in a 
> folder. 
>
> On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote:
>>
>> Hi
>>
>> File which you shared contains data for one week only.
>>
>> Cheers!!
>>
>>
>> On Mon, Aug 25, 2014 at 6:49 PM, SG  wrote:
>>
>>> Hi Vabzzz..
>>>
>>> I need one more help in this. As you have seen the scorecard file, that 
>>> should be summary of four weeks & thehn the feedback would be prepared. Can 
>>> you please also help me in consolidation of those four files in to one 
>>> file. It would be a great help. 
>>>
>>>
>>> On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote:
>>>>
>>>> Thanku so much vabz for clarification...
>>>>
>>>> On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>>>>>
>>>>> Old module is for creating files in wrokbook insted in feedback sheet.
>>>>>
>>>>> I have also created Macro for updating only 1 sheet, wherein you 
>>>>> needed to keep open scorecard & feedback book. After that doble click on 
>>>>> name of person whose record you wanted to update, post double clicking on 
>>>>> name a userform will appear in which you will be asked to select excel 
>>>>> book 
>>>>> in which employee sheet is situated & post selecting wb name (feedback 
>>>>> sheet in your case) data will get updated.
>>>>>
>>>>> Cheers!!
>>>>>
>>>>> On Aug 21, 2014 5:24 PM, "SG"  wrote:
>>>>>
>>>>>> Vabz sirhats offthnku so much...really gr88
>>>>>> now i have some questions so that i can clear my doubt. what you have 
>>>>>> created a code name " create old feedback " & what is the use of 
>>>>>> Option Explicit
>>>>>> Public MyFile As String
>>>>>> Public Stopped As Boolean
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote:
>>>>>>>
>>>>>>> FA
>>>>>>>
>>>>>>> Cheers!!
>>>>>>>
>>>>>>>
>>>>>>> On Thu, Aug 21, 2014 at 1:43 PM, SG  wrote:
>>>>>>>
>>>>>>>> Hi Vabz
>>>>>>>>
>>>>>>>> Great.. :) Thanku so much..please only one change is needed in 
>>>>>>>> total external errors & internal errors(row 9 &10 of feedback sheet), 
>>>>>>>> the 
>>>>>>>> sum of errors should come not the score.
>>>>>>>> Please can you change it.
>>>>>>>> Thanks alot ...
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote:
>>>>>>>>
>>>>>>>>> hi
>>>>>>>>>
>>>>>>>>> dont use double click, it is for only single sheet update.
>>>>>>>>>
>>>>>>>>> You have to just run a macro, check this file, click blue button..
>>>>>>>>>
>>>>>>>>> Make sure both files are open.
>>>>>>>>>
>>>>>>>>> Cheers!!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Wed, Aug 20, 2014 at 8:20 PM, SG  wrote:
>>>>>>>>>
>>>>>>>>>> Hi Vabz,
>>>>>>>>>>
>

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Vabz,
This would be used for monthly feedback. For monthly, i have to consolidate 
those 4 scorecard files which are prepared on weekly baisi & then i prepare 
the feedback for each.
This requires the consolidation of  scores of those 4 files.
I hope i'm able to clear all doubts.


On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote:
>
> How do you want data in feed back sheet, do you want to add for 2 period 
> or in separate sheet?
>
>
> On Tue, Aug 26, 2014 at 1:11 PM, SG > 
> wrote:
>
>> Hi Vabz,
>>
>> Any update on this. Can it be consolidated in any other way.
>>
>>  please,can anybody else also look in to this.
>>
>> On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote:
>>>
>>> some of the criterias are to consolidate the
>>> scores of users for region "UK" & location "b". The header & yellow 
>>> highlighted column(formulas) wouldn't change in "
>>> consolidate scorecard." sheet. It would sum the values of column F,G 
>>> ,(J,K,L) & (N,O,P) from scorecard files.
>>> Also for column R - TAT , if all are hit, then it would be hit but it 
>>> would be miss even if one of them is miss. I have attached two
>>> scorecards & one consolidate file in which summary would come but this 
>>> would gonna work for atleast 4 scorecard files which would kept in a 
>>> folder. 
>>>
>>> On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote:
>>>>
>>>> Hi
>>>>
>>>> File which you shared contains data for one week only.
>>>>
>>>> Cheers!!
>>>>
>>>>
>>>> On Mon, Aug 25, 2014 at 6:49 PM, SG  wrote:
>>>>
>>>>> Hi Vabzzz..
>>>>>
>>>>> I need one more help in this. As you have seen the scorecard file, 
>>>>> that should be summary of four weeks & thehn the feedback would be 
>>>>> prepared. Can you please also help me in consolidation of those four 
>>>>> files 
>>>>> in to one file. It would be a great help. 
>>>>>
>>>>>
>>>>> On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote:
>>>>>>
>>>>>> Thanku so much vabz for clarification...
>>>>>>
>>>>>> On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>>>>>>>
>>>>>>> Old module is for creating files in wrokbook insted in feedback 
>>>>>>> sheet.
>>>>>>>
>>>>>>> I have also created Macro for updating only 1 sheet, wherein you 
>>>>>>> needed to keep open scorecard & feedback book. After that doble click 
>>>>>>> on 
>>>>>>> name of person whose record you wanted to update, post double clicking 
>>>>>>> on 
>>>>>>> name a userform will appear in which you will be asked to select excel 
>>>>>>> book 
>>>>>>> in which employee sheet is situated & post selecting wb name (feedback 
>>>>>>> sheet in your case) data will get updated.
>>>>>>>
>>>>>>> Cheers!!
>>>>>>>
>>>>>>> On Aug 21, 2014 5:24 PM, "SG"  wrote:
>>>>>>>
>>>>>>>> Vabz sirhats offthnku so much...really gr88
>>>>>>>> now i have some questions so that i can clear my doubt. what you 
>>>>>>>> have created a code name " create old feedback " & what is the use of 
>>>>>>>> Option Explicit
>>>>>>>> Public MyFile As String
>>>>>>>> Public Stopped As Boolean
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote:
>>>>>>>>>
>>>>>>>>> FA
>>>>>>>>>
>>>>>>>>> Cheers!!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Thu, Aug 21, 2014 at 1:43 PM, SG  wrote:
>>>>>>>>>
>>>>>>>>>> Hi Vabz
>>>>>>>>>>
>>>>>>>>>> Great.. :) Thanku so much..please only one change is needed 
>>>>>>>>>> in total external errors & intern

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

yes sir..absolutely right..

is it feasible na?

On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote:
>
> Ok. Do you want to first merge 4 weekly scorecard in to 1 and then ctrate 
> employee wise feedback sheet. 
>
> 2nd part is ready now you want macro for first part.
>
> Am I correct.
> On Aug 26, 2014 2:38 PM, "SG" > wrote:
>
>> Vabz,
>> This would be used for monthly feedback. For monthly, i have to 
>> consolidate those 4 scorecard files which are prepared on weekly baisi & 
>> then i prepare the feedback for each.
>> This requires the consolidation of  scores of those 4 files.
>> I hope i'm able to clear all doubts.
>>
>>
>> On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote:
>>>
>>> How do you want data in feed back sheet, do you want to add for 2 period 
>>> or in separate sheet?
>>>
>>>
>>> On Tue, Aug 26, 2014 at 1:11 PM, SG  wrote:
>>>
>>>> Hi Vabz,
>>>>
>>>> Any update on this. Can it be consolidated in any other way.
>>>>
>>>>  please,can anybody else also look in to this.
>>>>
>>>> On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote:
>>>>>
>>>>> some of the criterias are to consolidate the
>>>>> scores of users for region "UK" & location "b". The header & yellow 
>>>>> highlighted column(formulas) wouldn't change in "
>>>>> consolidate scorecard." sheet. It would sum the values of column F,G 
>>>>> ,(J,K,L) & (N,O,P) from scorecard files.
>>>>> Also for column R - TAT , if all are hit, then it would be hit but it 
>>>>> would be miss even if one of them is miss. I have attached two
>>>>> scorecards & one consolidate file in which summary would come but this 
>>>>> would gonna work for atleast 4 scorecard files which would kept in a 
>>>>> folder. 
>>>>>
>>>>> On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote:
>>>>>>
>>>>>> Hi
>>>>>>
>>>>>> File which you shared contains data for one week only.
>>>>>>
>>>>>> Cheers!!
>>>>>>
>>>>>>
>>>>>> On Mon, Aug 25, 2014 at 6:49 PM, SG  wrote:
>>>>>>
>>>>>>> Hi Vabzzz..
>>>>>>>
>>>>>>> I need one more help in this. As you have seen the scorecard file, 
>>>>>>> that should be summary of four weeks & thehn the feedback would be 
>>>>>>> prepared. Can you please also help me in consolidation of those four 
>>>>>>> files 
>>>>>>> in to one file. It would be a great help. 
>>>>>>>
>>>>>>>
>>>>>>> On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote:
>>>>>>>>
>>>>>>>> Thanku so much vabz for clarification...
>>>>>>>>
>>>>>>>> On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>>>>>>>>>
>>>>>>>>> Old module is for creating files in wrokbook insted in feedback 
>>>>>>>>> sheet.
>>>>>>>>>
>>>>>>>>> I have also created Macro for updating only 1 sheet, wherein you 
>>>>>>>>> needed to keep open scorecard & feedback book. After that doble click 
>>>>>>>>> on 
>>>>>>>>> name of person whose record you wanted to update, post double 
>>>>>>>>> clicking on 
>>>>>>>>> name a userform will appear in which you will be asked to select 
>>>>>>>>> excel book 
>>>>>>>>> in which employee sheet is situated & post selecting wb name 
>>>>>>>>> (feedback 
>>>>>>>>> sheet in your case) data will get updated.
>>>>>>>>>
>>>>>>>>> Cheers!!
>>>>>>>>>
>>>>>>>>> On Aug 21, 2014 5:24 PM, "SG"  wrote:
>>>>>>>>>
>>>>>>>>>> Vabz sirhats offthnku so much...really gr88
>>>>>>>>>> now i have some questions so that i can clear my doubt. what you 
>>>>>>>>>> have cre

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

gr88...then will you please help me on this.

On Tuesday, August 26, 2014 4:27:32 PM UTC+5:30, Vabz wrote:
>
> yes
>
>
> On Tue, Aug 26, 2014 at 3:36 PM, SG > 
> wrote:
>
>> yes sir..absolutely right..
>>
>> is it feasible na?
>>
>> On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote:
>>
>>> Ok. Do you want to first merge 4 weekly scorecard in to 1 and then 
>>> ctrate employee wise feedback sheet. 
>>>
>>> 2nd part is ready now you want macro for first part.
>>>
>>> Am I correct.
>>> On Aug 26, 2014 2:38 PM, "SG"  wrote:
>>>
>>>> Vabz,
>>>> This would be used for monthly feedback. For monthly, i have to 
>>>> consolidate those 4 scorecard files which are prepared on weekly baisi & 
>>>> then i prepare the feedback for each.
>>>>  This requires the consolidation of  scores of those 4 files.
>>>> I hope i'm able to clear all doubts.
>>>>
>>>>
>>>> On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote:
>>>>>
>>>>> How do you want data in feed back sheet, do you want to add for 2 
>>>>> period or in separate sheet?
>>>>>
>>>>>
>>>>> On Tue, Aug 26, 2014 at 1:11 PM, SG  wrote:
>>>>>
>>>>>> Hi Vabz,
>>>>>>
>>>>>> Any update on this. Can it be consolidated in any other way.
>>>>>>
>>>>>>  please,can anybody else also look in to this.
>>>>>>
>>>>>> On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote:
>>>>>>>
>>>>>>> some of the criterias are to consolidate the
>>>>>>> scores of users for region "UK" & location "b". The header & yellow 
>>>>>>> highlighted column(formulas) wouldn't change in "
>>>>>>> consolidate scorecard." sheet. It would sum the values of column F,G 
>>>>>>> ,(J,K,L) & (N,O,P) from scorecard files.
>>>>>>> Also for column R - TAT , if all are hit, then it would be hit but 
>>>>>>> it would be miss even if one of them is miss. I have attached two
>>>>>>> scorecards & one consolidate file in which summary would come but 
>>>>>>> this would gonna work for atleast 4 scorecard files which would kept in 
>>>>>>> a 
>>>>>>> folder. 
>>>>>>>
>>>>>>> On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote:
>>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> File which you shared contains data for one week only.
>>>>>>>>
>>>>>>>> Cheers!!
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Aug 25, 2014 at 6:49 PM, SG  wrote:
>>>>>>>>
>>>>>>>>> Hi Vabzzz..
>>>>>>>>>
>>>>>>>>> I need one more help in this. As you have seen the scorecard file, 
>>>>>>>>> that should be summary of four weeks & thehn the feedback would be 
>>>>>>>>> prepared. Can you please also help me in consolidation of those four 
>>>>>>>>> files 
>>>>>>>>> in to one file. It would be a great help. 
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote:
>>>>>>>>>>
>>>>>>>>>> Thanku so much vabz for clarification...
>>>>>>>>>>
>>>>>>>>>> On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote:
>>>>>>>>>>>
>>>>>>>>>>> Old module is for creating files in wrokbook insted in feedback 
>>>>>>>>>>> sheet.
>>>>>>>>>>>
>>>>>>>>>>> I have also created Macro for updating only 1 sheet, wherein you 
>>>>>>>>>>> needed to keep open scorecard & feedback book. After that doble 
>>>>>>>>>>> click on 
>>>>>>>>>>> name of person whose record you wanted to update, post double 
>>>>>>>>>>> cl

Re: $$Excel-Macros$$ Create user wise feedback sheet

[SG]

Vabz sir, thanku so much
really you helped me alot...Hats off:)

On Tuesday, August 26, 2014 11:46:12 PM UTC+5:30, Vabz wrote:
>
> place all sheets to be updated in one folder, run macro & select that 
> folder, & data will get consolidated in this book.
>
> Cheers!!
>
>
> On Tue, Aug 26, 2014 at 4:49 PM, SG > 
> wrote:
>
>> gr88...then will you please help me on this.
>>
>>
>> On Tuesday, August 26, 2014 4:27:32 PM UTC+5:30, Vabz wrote:
>>
>>> yes
>>>
>>>
>>> On Tue, Aug 26, 2014 at 3:36 PM, SG  wrote:
>>>
>>>> yes sir..absolutely right..
>>>>
>>>> is it feasible na?
>>>>
>>>> On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote:
>>>>
>>>>> Ok. Do you want to first merge 4 weekly scorecard in to 1 and then 
>>>>> ctrate employee wise feedback sheet. 
>>>>>
>>>>> 2nd part is ready now you want macro for first part.
>>>>>
>>>>> Am I correct.
>>>>> On Aug 26, 2014 2:38 PM, "SG"  wrote:
>>>>>
>>>>>> Vabz,
>>>>>> This would be used for monthly feedback. For monthly, i have to 
>>>>>> consolidate those 4 scorecard files which are prepared on weekly baisi & 
>>>>>> then i prepare the feedback for each.
>>>>>>  This requires the consolidation of  scores of those 4 files.
>>>>>> I hope i'm able to clear all doubts.
>>>>>>
>>>>>>
>>>>>> On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote:
>>>>>>>
>>>>>>> How do you want data in feed back sheet, do you want to add for 2 
>>>>>>> period or in separate sheet?
>>>>>>>
>>>>>>>
>>>>>>> On Tue, Aug 26, 2014 at 1:11 PM, SG  wrote:
>>>>>>>
>>>>>>>> Hi Vabz,
>>>>>>>>
>>>>>>>> Any update on this. Can it be consolidated in any other way.
>>>>>>>>
>>>>>>>>  please,can anybody else also look in to this.
>>>>>>>>
>>>>>>>> On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote:
>>>>>>>>>
>>>>>>>>> some of the criterias are to consolidate the
>>>>>>>>> scores of users for region "UK" & location "b". The header & 
>>>>>>>>> yellow highlighted column(formulas) wouldn't change in "
>>>>>>>>> consolidate scorecard." sheet. It would sum the values of column 
>>>>>>>>> F,G ,(J,K,L) & (N,O,P) from scorecard files.
>>>>>>>>> Also for column R - TAT , if all are hit, then it would be hit but 
>>>>>>>>> it would be miss even if one of them is miss. I have attached two
>>>>>>>>> scorecards & one consolidate file in which summary would come but 
>>>>>>>>> this would gonna work for atleast 4 scorecard files which would kept 
>>>>>>>>> in a 
>>>>>>>>> folder. 
>>>>>>>>>
>>>>>>>>> On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote:
>>>>>>>>>>
>>>>>>>>>> Hi
>>>>>>>>>>
>>>>>>>>>> File which you shared contains data for one week only.
>>>>>>>>>>
>>>>>>>>>> Cheers!!
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Mon, Aug 25, 2014 at 6:49 PM, SG  wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi Vabzzz..
>>>>>>>>>>>
>>>>>>>>>>> I need one more help in this. As you have seen the scorecard 
>>>>>>>>>>> file, that should be summary of four weeks & thehn the feedback 
>>>>>>>>>>> would be 
>>>>>>>>>>> prepared. Can you please also help me in consolidation of those 
>>>>>>>>>>> four files 
>>>>>>>>>>> in to one file. It would be a great help. 
>>>>>>>>>>>
>>>>>>>>>>>
&g

$$Excel-Macros$$ Consolidating Multiple files

[SG]

Hi Experts,
 
I need your help in one of my problem.I want to consolidate the 
multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
one excel file.I have tried but unable to create a macro on it.There is 
only one criteria in this consolidation is that on column D of every sheet 
is a date.What i want is to extract the data on basis of a particular week 
number let's say we are in week 50 & i want the data of week-49.Currently 
i'm doing this manually.I apply the weeknum formula in column where data 
gets end let's say that's column M & then extract the data.
 
Please help me on this with a macro & thanks in advance.

-- 
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.

Re: $$Excel-Macros$$ Consolidating Multiple files

[SG]

hi Rajan,
 
Thanks for the reply.I have taken the macro from 2nd link.
However on running the macro, the code in 4th line
'Dim Fso As New Scripting.FileSystemObject' 
gives an complie error 'user-defined type not defined'.
 
please help on this.
On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote:

>  *See if it helps*
>
> * *
>
> *1)  **
> http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/*
>
> *2)  **
> http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/*
>
> *3)  **
> http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/*
>
> * *
>
> * *
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com  [mailto:
> excel-...@googlegroups.com ] *On Behalf Of *SG
> *Sent:* 14 December 2012 7:19
> *To:* excel-...@googlegroups.com 
> *Subject:* $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> Hi Experts,
>  
>  
>  
> I need your help in one of my problem.I want to consolidate the 
> multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
> one excel file.I have tried but unable to create a macro on it.There is 
> only one criteria in this consolidation is that on column D of every sheet 
> is a date.What i want is to extract the data on basis of a particular week 
> number let's say we are in week 50 & i want the data of week-49.Currently 
> i'm doing this manually.I apply the weeknum formula in column where data 
> gets end let's say that's column M & then extract the data.
>  
>  
>  
> Please help me on this with a macro & thanks in advance.
>
> -- 
> 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 post to this group, send email to excel-...@googlegroups.com
> .
> To unsubscribe from this group, send email to 
> excel-macros...@googlegroups.com .
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>  
>  
>

-- 
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.

Re: $$Excel-Macros$$ Consolidating Multiple files

[SG]

may be i'm silly with the question, but really not getting it that you have 
replied.

On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote:

>  *Ah..*
>
> * *
>
> *You can add reference “Microsoft scripting runtime”*
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com  [mailto:
> excel-...@googlegroups.com ] *On Behalf Of *SG
> *Sent:* 14 December 2012 7:54
> *To:* excel-...@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> hi Rajan,
>  
>  
>  
> Thanks for the reply.I have taken the macro from 2nd link.
>  
> However on running the macro, the code in 4th line
>  
> 'Dim Fso As New Scripting.FileSystemObject' 
>  
> gives an complie error 'user-defined type not defined'.
>  
>  
>  
> please help on this.
> On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote:
>
>  *See if it helps*
>
> * *
>
> *1)**  **
> http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/*
>
> *2)**  **
> http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/*
>
> *3)**  **
> http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/*
>
> * *
>
> * *
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
> Behalf Of *SG
> *Sent:* 14 December 2012 7:19
> *To:* excel-...@googlegroups.com
> *Subject:* $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> Hi Experts,
>  
>  
>  
> I need your help in one of my problem.I want to consolidate the 
> multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
> one excel file.I have tried but unable to create a macro on it.There is 
> only one criteria in this consolidation is that on column D of every sheet 
> is a date.What i want is to extract the data on basis of a particular week 
> number let's say we are in week 50 & i want the data of week-49.Currently 
> i'm doing this manually.I apply the weeknum formula in column where data 
> gets end let's say that's column M & then extract the data.
>  
>  
>  
> Please help me on this with a macro & thanks in advance.
>
> -- 
> 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 post to this group, send email to excel-...@googlegroups.com.
> To unsubscribe from this group, send email to 
> excel-macros...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>  
>  
>
> -- 
> 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 post to this group, send email to excel-...@googlegroups.com
> .
> To unsubscribe from this group, send email to 
> excel-macros...@googlegroups.com .
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>  
>

Re: $$Excel-Macros$$ Consolidating Multiple files

[SG]

thanks but the macro is not entering in to the loop.
I have edited the macro lil bit at my end.I have removed the part which is 
for saving the complied file & i think it's not going to effect anyway.
please suggest..

On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote:

>  *Ok,*
>
> * *
>
> *Open Visula Basic Editor*
>
> *Goto the Tool- Reference –Select **Microsoft scripting runtime***
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com  [mailto:
> excel-...@googlegroups.com ] *On Behalf Of *SG
> *Sent:* 14 December 2012 8:26
> *To:* excel-...@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> may be i'm silly with the question, but really not getting it that you 
> have replied.
>  
>
> On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote:
>
>  *Ah..*
>
> * *
>
> *You can add reference “Microsoft scripting runtime”*
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
> Behalf Of *SG
> *Sent:* 14 December 2012 7:54
> *To:* excel-...@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> hi Rajan,
>  
>  
>  
> Thanks for the reply.I have taken the macro from 2nd link.
>  
> However on running the macro, the code in 4th line
>  
> 'Dim Fso As New Scripting.FileSystemObject' 
>  
> gives an complie error 'user-defined type not defined'.
>  
>  
>  
> please help on this.
> On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote:
>
>  *See if it helps*
>
> * *
>
> *1)**  **
> http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/*
>
> *2)**  **
> http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/*
>
> *3)**  **
> http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/*
>
> * *
>
> * *
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> * *
>
> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
> Behalf Of *SG
> *Sent:* 14 December 2012 7:19
> *To:* excel-...@googlegroups.com
> *Subject:* $$Excel-Macros$$ Consolidating Multiple files
>
>  
>  
> Hi Experts,
>  
>  
>  
> I need your help in one of my problem.I want to consolidate the 
> multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
> one excel file.I have tried but unable to create a macro on it.There is 
> only one criteria in this consolidation is that on column D of every sheet 
> is a date.What i want is to extract the data on basis of a particular week 
> number let's say we are in week 50 & i want the data of week-49.Currently 
> i'm doing this manually.I apply the weeknum formula in column where data 
> gets end let's say that's column M & then extract the data.
>  
>  
>  
> Please help me on this with a macro & thanks in advance.
>
> -- 
> 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 post to this group, send email to excel-...@googlegroups.com.
> To unsubscribe from this group, send email to 
> excel-macros...@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>  
>  
>
> -- 
> 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 answ

Re: $$Excel-Macros$$ Consolidating Multiple files

[SG]

hi Paul,
 
Sorry for the inconvenience.PFA file & names of excel file are 1,2,3.
Please suggest.

On Friday, December 14, 2012 9:03:11 PM UTC+5:30, Paul Schreiner wrote:

>   I don't think we can work that way...
>  
> You're using someone ELSE's macro with YOUR files and folders
> and you've admitted that you've "edited the macro a little bit"...
>  
> and you want us to suggest how to fix it without seeing 
> the macro, your files and folders, and what edit's you've done?
>  
> Well.. I GUESS I could suggest one thing:
>  
> please give us more information.
> Perhaps attach a file.
>  
> At the LEAST, attach the macro and tell us what your file names and folder 
> names are...
>  
>
> *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:* SG >
> *To:* excel-...@googlegroups.com 
> *Sent:* Fri, December 14, 2012 10:11:52 AM
> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>
> thanks but the macro is not entering in to the loop.
> I have edited the macro lil bit at my end.I have removed the part which is 
> for saving the complied file & i think it's not going to effect anyway.
> please suggest..
>
> On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote:
>
>>  *Ok,*
>>
>> * *
>>
>> *Open Visula Basic Editor*
>>
>> *Goto the Tool- Reference –Select **Microsoft scripting runtime***
>>
>> * *
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> * *
>>
>> *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On 
>> Behalf Of *SG
>> *Sent:* 14 December 2012 8:26
>> *To:* excel-...@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>>
>>  
>>  
>> may be i'm silly with the question, but really not getting it that you 
>> have replied.
>>  
>>
>> On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote:
>>
>>  *Ah..*
>>
>> * *
>>
>> *You can add reference “Microsoft scripting runtime”*
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> * *
>>
>> *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On 
>> Behalf Of *SG
>> *Sent:* 14 December 2012 7:54
>> *To:* excel-...@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>>
>>  
>>  
>> hi Rajan,
>>  
>>  
>>  
>> Thanks for the reply.I have taken the macro from 2nd link.
>>  
>> However on running the macro, the code in 4th line
>>  
>> 'Dim Fso As New Scripting.FileSystemObject' 
>>  
>> gives an complie error 'user-defined type not defined'.
>>  
>>  
>>  
>> please help on this.
>> On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote:
>>
>>  *See if it helps*
>>
>> * *
>>
>> *1)**  **http://excelpoweruser. wordpress.com/2012/06/07/ 
>> compile-worksheets/<http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/>
>> *
>>
>> *2)**  **http://excelpoweruser. wordpress.com/2011/08/12/ 
>> compiling-workbooks-2/<http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/>
>> *
>>
>> *3)**  **http://excelpoweruser. wordpress.com/2011/07/06/ 
>> compiling-workbooks/<http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/>
>> *
>>
>> * *
>>
>> * *
>>
>> * *
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> * *
>>
>> *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On 
>> Behalf Of *SG
>> *Sent:* 14 December 2012 7:19
>> *To:* excel-...@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Consolidating Multiple files
>>
>>  
>>  
>> Hi Experts,
>>  
>>  
>>  
>> I need your help in one of my problem.I want to consolidate the 
>> multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
>> 

Re: $$Excel-Macros$$ Consolidating Multiple files

[SG]

experts please help me with this.i have also attached the file .

On Monday, December 17, 2012 1:59:39 PM UTC+5:30, SG wrote:

> hi Paul,
>  
> Sorry for the inconvenience.PFA file & names of excel file are 1,2,3.
> Please suggest.
>
> On Friday, December 14, 2012 9:03:11 PM UTC+5:30, Paul Schreiner wrote:
>
>>   I don't think we can work that way...
>>  
>> You're using someone ELSE's macro with YOUR files and folders
>> and you've admitted that you've "edited the macro a little bit"...
>>  
>> and you want us to suggest how to fix it without seeing 
>> the macro, your files and folders, and what edit's you've done?
>>  
>> Well.. I GUESS I could suggest one thing:
>>  
>> please give us more information.
>> Perhaps attach a file.
>>  
>> At the LEAST, attach the macro and tell us what your file names and 
>> folder names are...
>>  
>>
>> *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:* SG 
>> *To:* excel-...@googlegroups.com
>> *Sent:* Fri, December 14, 2012 10:11:52 AM
>> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>>
>> thanks but the macro is not entering in to the loop.
>> I have edited the macro lil bit at my end.I have removed the part which 
>> is for saving the complied file & i think it's not going to effect anyway.
>> please suggest..
>>
>> On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote:
>>
>>>  *Ok,*
>>>
>>> * *
>>>
>>> *Open Visula Basic Editor*
>>>
>>> *Goto the Tool- Reference –Select **Microsoft scripting runtime***
>>>
>>> * *
>>>
>>> * *
>>>
>>> *Regards*
>>>
>>> *Rajan verma*
>>>
>>> *+91 7838100659 [IM-Gtalk]*
>>>
>>> * *
>>>
>>> *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] 
>>> *On Behalf Of *SG
>>> *Sent:* 14 December 2012 8:26
>>> *To:* excel-...@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>>>
>>>  
>>>  
>>> may be i'm silly with the question, but really not getting it that you 
>>> have replied.
>>>  
>>>
>>> On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote:
>>>
>>>  *Ah..*
>>>
>>> * *
>>>
>>> *You can add reference “Microsoft scripting runtime”*
>>>
>>> * *
>>>
>>> *Regards*
>>>
>>> *Rajan verma*
>>>
>>> *+91 7838100659 [IM-Gtalk]*
>>>
>>> * *
>>>
>>> *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] 
>>> *On Behalf Of *SG
>>> *Sent:* 14 December 2012 7:54
>>> *To:* excel-...@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files
>>>
>>>  
>>>  
>>> hi Rajan,
>>>  
>>>  
>>>  
>>> Thanks for the reply.I have taken the macro from 2nd link.
>>>  
>>> However on running the macro, the code in 4th line
>>>  
>>> 'Dim Fso As New Scripting.FileSystemObject' 
>>>  
>>> gives an complie error 'user-defined type not defined'.
>>>  
>>>  
>>>  
>>> please help on this.
>>> On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote:
>>>
>>>  *See if it helps*
>>>
>>> * *
>>>
>>> *1)**  **http://excelpoweruser. wordpress.com/2012/06/07/ 
>>> compile-worksheets/<http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/>
>>> *
>>>
>>> *2)**  **http://excelpoweruser. wordpress.com/2011/08/12/ 
>>> compiling-workbooks-2/<http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/>
>>> *
>>>
>>> *3)**  **http://excelpoweruser. wordpress.com/2011/07/06/ 
>>> compiling-workbooks/<http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/>
>>> *
>>>
>>> * *
>>>
>

$$Excel-Macros$$ Re: Consolidating Multiple files

[SG]

hi all,
 
PFA file for your reference.

On Tuesday, December 18, 2012 11:00:16 AM UTC+5:30, Prince wrote:

> Hi SG, 
>
> Please share your file with us.
>
> Regards
> Prince
>
> On Friday, 14 December 2012 19:18:59 UTC+5:30, SG wrote: 
>>
>> Hi Experts,
>>  
>> I need your help in one of my problem.I want to consolidate the 
>> multiple excel files of a folder with same name & numbered as 1,2,3 in to a 
>> one excel file.I have tried but unable to create a macro on it.There is 
>> only one criteria in this consolidation is that on column D of every sheet 
>> is a date.What i want is to extract the data on basis of a particular week 
>> number let's say we are in week 50 & i want the data of week-49.Currently 
>> i'm doing this manually.I apply the weeknum formula in column where data 
>> gets end let's say that's column M & then extract the data.
>>  
>> Please help me on this with a macro & thanks in advance.
>>
>

-- 
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to 
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.




working.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

$$Excel-Macros$$ Extract Data on basis of different Criterias

[SG]

Hi Experts,
 
I want to create a report in which data would be extracted on basis of 
atleast 4 filters from base data sheet.please help me with a macro example.
 
Thanks in advance.

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

Re: $$Excel-Macros$$ Extract Data on basis of different Criterias

[SG]

thanx for the response.
 
PFA example.What i want is to filter the data as oer the "Form" sheet & 
extract name & it's accuracy & paste in the data section.the data is so 
huge that's why i want it by macro.

On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote:

> HI 
>
> Pl share sample file.
>
> Thanks
>
>
> On Wed, Jul 3, 2013 at 7:30 PM, SG >wrote:
>
>> Hi Experts,
>>  
>> I want to create a report in which data would be extracted on basis of 
>> atleast 4 filters from base data sheet.please help me with a macro example.
>>  
>> Thanks in advance.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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 group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.




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

Re: $$Excel-Macros$$ Extract Data on basis of different Criterias

[SG]

hi Ashish,
 
The data is in lakhs & advance filter would allow me to filter only one 
criteria.I have atleast 4 filters in this data.
plz experts help me with a macro.

On Wednesday, July 3, 2013 8:29:18 PM UTC+5:30, ashish wrote:

> u can do it using advance filter
>
>
> On Wed, Jul 3, 2013 at 7:55 PM, SG >wrote:
>
>> thanx for the response.
>>  
>> PFA example.What i want is to filter the data as oer the "Form" sheet & 
>> extract name & it's accuracy & paste in the data section.the data is so 
>> huge that's why i want it by macro.
>>  
>> On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote:
>>
>>>  HI 
>>>
>>> Pl share sample file.
>>>
>>> Thanks
>>>
>>>
>>>  On Wed, Jul 3, 2013 at 7:30 PM, SG  wrote:
>>>
>>>>  Hi Experts,
>>>>  
>>>> I want to create a report in which data would be extracted on basis of 
>>>> atleast 4 filters from base data sheet.please help me with a macro example.
>>>>  
>>>> Thanks in advance.
>>>>
>>>> -- 
>>>> 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<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...@**googlegroups.com.
>>>> To post to this group, send email to excel-...@googlegroups.com. 
>>>>
>>>> Visit this group at 
>>>> http://groups.google.com/**group/excel-macros<http://groups.google.com/group/excel-macros>
>>>> .
>>>> For more options, visit 
>>>> https://groups.google.com/**groups/opt_out<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...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>  
>>  
>>
>
>
>
> -- 
> *Regards*
> * *
> *Ashish Koul*
>
>
>  *Visit*
> *My Excel Blog <http://www.excelvbamacros.com/>*
> Like Us on 
> Facebook<http://www.facebook.com/p

Re: $$Excel-Macros$$ Extract Data on basis of different Criterias

[SG]

hi Ashish,
 
thanks for the help.please help me in result as i want only name & accuracy 
to show.

On Wednesday, July 3, 2013 8:49:33 PM UTC+5:30, ashish wrote:

> try the attachment . if ur data is in lakhs best is u use access 
>
> Regards
> Ashish
>
>
>
>
> On Wed, Jul 3, 2013 at 8:42 PM, SG >wrote:
>
>> hi Ashish,
>>  
>> The data is in lakhs & advance filter would allow me to filter only one 
>> criteria.I have atleast 4 filters in this data.
>> plz experts help me with a macro.
>>  
>> On Wednesday, July 3, 2013 8:29:18 PM UTC+5:30, ashish wrote:
>>
>>>  u can do it using advance filter
>>>  
>>>
>>> On Wed, Jul 3, 2013 at 7:55 PM, SG  wrote:
>>>
>>>> thanx for the response.
>>>>  
>>>> PFA example.What i want is to filter the data as oer the "Form" sheet & 
>>>> extract name & it's accuracy & paste in the data section.the data is so 
>>>> huge that's why i want it by macro.
>>>>  
>>>> On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote:
>>>>
>>>>>  HI 
>>>>>
>>>>> Pl share sample file.
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>>  On Wed, Jul 3, 2013 at 7:30 PM, SG  wrote:
>>>>>
>>>>>>  Hi Experts,
>>>>>>  
>>>>>> I want to create a report in which data would be extracted on basis 
>>>>>> of atleast 4 filters from base data sheet.please help me with a macro 
>>>>>> example.
>>>>>>  
>>>>>> Thanks in advance.
>>>>>>
>>>>>> -- 
>>>>>> 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/**discu**ssexcel<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...@**googlegroups.**com.
>>>>>> To post to this group, send email to excel-...@googlegroups.com. 
>>>>>>
>>>>>> Visit this group at 
>>>>>> http://groups.google.com/**group**/excel-macros<http://groups.google.com/group/excel-macros>
>>>>>> .
>>>>>> For more options, visit 
>>>>>> https://groups.google.com/**grou**ps/opt_out<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<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 securi

$$Excel-Macros$$ Macro Problem...plzzz help

[SG]

hi Experts,

I'm in a great problem as i'm beginner in macros.I have created a template 
in which the data would be extracted on basis on 3 criterias.However, by 
using macro, i'm unable to extract it.For criterias, i have created 
drop-down list by using Data validation & name manager.

Now first problem is if i add some data in "base data" then it is not 
updated in Drop downs & second problem is based on criteria, data is not 
extracted.I don't know where i'm lagging behind.please help me with the 
solution.

Thanks in advance.

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




rough.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

$$Excel-Macros$$ Re: Macro Problem...plzzz help

[SG]

plz experts..spare some time on my problem.
looking forward for your help.
 

On Friday, July 5, 2013 5:25:27 PM UTC+5:30, SG wrote:

> hi Experts,
>
> I'm in a great problem as i'm beginner in macros.I have created a template 
> in which the data would be extracted on basis on 3 criterias.However, by 
> using macro, i'm unable to extract it.For criterias, i have created 
> drop-down list by using Data validation & name manager.
>
> Now first problem is if i add some data in "base data" then it is not 
> updated in Drop downs & second problem is based on criteria, data is not 
> extracted.I don't know where i'm lagging behind.please help me with the 
> solution.
>
> Thanks in advance.
>

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

Re: $$Excel-Macros$$ Macro Problem...plzzz help

[SG]

Thanks for the reply bt it doesn't solve my purpose.Let me try one more 
time to explain.
What i want is that when i select domain, then, only name & accuracy in 
that domain will display on report sheet..
then ,if i select any project in that domain, then it's correponding name & 
accuracy will display on report sheet.
then if i select any ques in that project,then it's correponding name & 
accuracy will display on report sheet.
It needs 3 criteria based filters on base data which will retrieve 
corresponding name & accuracy
& display them on report sheet.

please sir, help me with a solution. 
On Saturday, July 6, 2013 10:30:04 AM UTC+5:30, Vabz wrote:

> Hi 
>
> PFA, You have to ensure criteria range is in same sheet in which you apply 
> filter.
>
> you also need to extend your range to accommodate rest of data..
>
>
>
> Thaks
>
>
> On Fri, Jul 5, 2013 at 5:25 PM, SG >wrote:
>
>> hi Experts,
>>
>> I'm in a great problem as i'm beginner in macros.I have created a 
>> template in which the data would be extracted on basis on 3 
>> criterias.However, by using macro, i'm unable to extract it.For criterias, 
>> i have created drop-down list by using Data validation & name manager.
>>
>> Now first problem is if i add some data in "base data" then it is not 
>> updated in Drop downs & second problem is based on criteria, data is not 
>> extracted.I don't know where i'm lagging behind.please help me with the 
>> solution.
>>
>> Thanks in advance.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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 group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

$$Excel-Macros$$ Macro needed to add worksheet

[SG]

Hi Experts,

Once again I neeed your help.I have one excel worksheet which is common for 
all workbooks. However, that worksheet is not added in the workbooks. I 
have atleast 50 excel workbooks in a folder in which i have to add that 
single worksheet manually.The name of all worbooks are same only the week 
number is different in filenames like report wk-1,report wk-2 & so 
on.Please help me with a macro which would add that single sheet in all 
workbooks of that folder one by one.

Thanks in advance

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

Re: $$Excel-Macros$$ Macro needed to add worksheet

[SG]

Thanks for the help but it's not adding my sheet in rest of the workbooks.
i have attached that single sheet & added the macro in it.Please suggest 
Where i'm going wrong.

On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote:

>  
> Sub copy_sheet()
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
>
>
> Dim fld As Object, fil As Object, fso As Object
> Dim wkb As Workbook
>
> Set fso = CreateObject("scripting.filesystemobject")
> Set fld = fso.getfolder("C:\Users\admin\Desktop\test") ' folder having 
> files
>
> For Each fil In fld.Files
> Set wkb = Workbooks.Open(fil.Path)
> ThisWorkbook.Sheets("sample").Copy After:=wkb.Sheets(wkb.Sheets.Count)
> wkb.Save
> wkb.Close
> Set wkb = Nothing
> Next
>
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
>
>
>
> End Sub
>
>
> see if it helps
>
>
>
> On Mon, Sep 9, 2013 at 7:45 PM, SG >wrote:
>
>>  Hi Experts,
>>
>> Once again I neeed your help.I have one excel worksheet which is common 
>> for all workbooks. However, that worksheet is not added in the workbooks. I 
>> have atleast 50 excel workbooks in a folder in which i have to add that 
>> single worksheet manually.The name of all worbooks are same only the week 
>> number is different in filenames like report wk-1,report wk-2 & so 
>> on.Please help me with a macro which would add that single sheet in all 
>> workbooks of that folder one by one.
>>
>> Thanks in advance
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
>
> -- 
> *Regards*
> * *
> *Ashish Koul*
>
>
>  *Visit*
> *My Excel Blog <http://www.excelvbamacros.com/>*
> Like Us on 
> Facebook<http://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897>
> Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
>
>
> P Before printing, think about the environment.
>  
>  
>

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


Book1.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ Macro needed to add worksheet

[SG]

Hi Ravi,
 
i think the below code is incomplete.please correct me if i'm wrong.
 
" Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
Destination:=Workbooks(Mid(fil.Name, 1, _"

On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote:

>  Try this and change you folder name accordingly …
>
>  
>
>  
>
> Sub copy_sheet()
>
>  
>
> Application.ScreenUpdating = False
>
> Application.DisplayAlerts = False
>
>  
>
>  
>
> primewb = ActiveWorkbook.Name
>
>  
>
> Dim fld As Object, fil As Object, fso As Object
>
> Dim wkb As Workbook
>
>  
>
>  
>
> Set fso = CreateObject("scripting.filesystemobject")
>
> Set fld = fso.getfolder("C:\Users\Admin\Desktop\Some VBA Tricks") ' folder 
> having files
>
>  
>
>  
>
> For Each fil In fld.Files
>
> Set wkb = Workbooks.Open(fil.Path)
>
> Sheets.Add after:=Sheets(Sheets.Count)
>
> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
> Destination:=Workbooks(Mid(fil.Name, 1, _
>
> WorksheetFunction.Find(".", fil.Name) - 
> 1)).Sheets(Sheets.Count).Range("A1")
>
> wkb.Save
>
> wkb.Close
>
> Set wkb = Nothing
>
> Next
>
>  
>
> Application.ScreenUpdating = True
>
> Application.DisplayAlerts = True
>
>  
>
>  
>
> End Sub
>
>  
>
>  
>
> * *
>
> *Warm Regards,*
>
> *Ravi Kumar.*
>
>  
>
> *From:* excel-...@googlegroups.com  [mailto:
> excel-...@googlegroups.com ] *On Behalf Of *SG
> *Sent:* Tuesday, September 10, 2013 2:36 PM
> *To:* excel-...@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>
>  
>  
> Thanks for the help but it's not adding my sheet in rest of the workbooks.
> i have attached that single sheet & added the macro in it.Please suggest 
> Where i'm going wrong.
>  
>
> On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote:
>
>   
>  
> Sub copy_sheet()
>  
>  
>  
> Application.ScreenUpdating = False
>  
> Application.DisplayAlerts = False
>  
>  
>  
>  
>  
> Dim fld As Object, fil As Object, fso As Object
>  
> Dim wkb As Workbook
>  
>  
>  
> Set fso = CreateObject("scripting.filesystemobject")
>  
> Set fld = fso.getfolder("C:\Users\admin\Desktop\test") ' folder having 
> files
>  
>  
>  
> For Each fil In fld.Files
>  
> Set wkb = Workbooks.Open(fil.Path)
>  
> ThisWorkbook.Sheets("sample").Copy After:=wkb.Sheets(wkb.Sheets.Count)
>  
> wkb.Save
>  
> wkb.Close
>  
> Set wkb = Nothing
>  
> Next
>  
>  
>  
> Application.ScreenUpdating = True
>  
> Application.DisplayAlerts = True
>  
>  
>  
>  
>  
>  
>  
> End Sub
>  
>  
>  
>  
>  
> see if it helps
>  
>  
>  
>  
>  
> On Mon, Sep 9, 2013 at 7:45 PM, SG  wrote:
>
>  Hi Experts,
>
> Once again I neeed your help.I have one excel worksheet which is common 
> for all workbooks. However, that worksheet is not added in the workbooks. I 
> have atleast 50 excel workbooks in a folder in which i have to add that 
> single worksheet manually.The name of all worbooks are same only the week 
> number is different in filenames like report wk-1,report wk-2 & so 
> on.Please help me with a macro which would add that single sheet in all 
> workbooks of that folder one by one.
>
> Thanks in advance
>
> -- 
> 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 

Re: $$Excel-Macros$$ Macro needed to add worksheet

[SG]

hi Ashish,
 
it's running perfectly without any error but it's not adding the sheet in 
workbook.

On Tuesday, September 10, 2013 7:54:02 PM UTC+5:30, ashish wrote:

> is it giving you any error
>
>
> On Tue, Sep 10, 2013 at 2:54 PM, Ravi Kumar 
> > wrote:
>
>>  Try this and change you folder name accordingly …
>>  
>> ** **
>>
>> ** **
>>
>> Sub copy_sheet()
>>
>> ** **
>>
>> Application.ScreenUpdating = False
>>
>> Application.DisplayAlerts = False
>>
>> ** **
>>
>> ** **
>>
>> primewb = ActiveWorkbook.Name
>>  
>> ** **
>>
>> Dim fld As Object, fil As Object, fso As Object
>>
>> Dim wkb As Workbook
>>
>> ** **
>>
>> ** **
>>
>> Set fso = CreateObject("scripting.filesystemobject")
>>
>> Set fld = fso.getfolder("C:\Users\Admin\Desktop\Some VBA Tricks") ' 
>> folder having files
>>  
>> ** **
>>
>> ** **
>>
>> For Each fil In fld.Files
>>
>> Set wkb = Workbooks.Open(fil.Path)
>>
>> Sheets.Add after:=Sheets(Sheets.Count)
>>
>> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
>> Destination:=Workbooks(Mid(fil.Name, 1, _
>>
>> WorksheetFunction.Find(".", fil.Name) - 
>> 1)).Sheets(Sheets.Count).Range("A1")
>>  
>> wkb.Save
>>
>> wkb.Close
>>
>> Set wkb = Nothing
>>
>> Next
>>
>> ** **
>>
>> Application.ScreenUpdating = True
>>
>> Application.DisplayAlerts = True
>>
>> ** **
>>
>> ** **
>>
>> End Sub
>>
>> ** **
>>
>> ** **
>>
>> * *
>>
>> *Warm Regards,*
>>
>> *Ravi Kumar.*
>>
>> ** **
>>
>> *From:* excel-...@googlegroups.com  [mailto:
>> excel-...@googlegroups.com ] *On Behalf Of *SG
>> *Sent:* Tuesday, September 10, 2013 2:36 PM
>> *To:* excel-...@googlegroups.com 
>> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>>  
>> ** **
>>  
>> Thanks for the help but it's not adding my sheet in rest of the workbooks.
>> i have attached that single sheet & added the macro in it.Please suggest 
>> Where i'm going wrong.
>>  
>>
>> On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote:
>>
>>  ** **
>>  
>> Sub copy_sheet()
>>  
>> ** **
>>  
>> Application.ScreenUpdating = False
>>  
>> Application.DisplayAlerts = False
>>  
>> ** **
>>  
>> ** **
>>  
>> Dim fld As Object, fil As Object, fso As Object
>>  
>> Dim wkb As Workbook
>>  
>> ** **
>>  
>> Set fso = CreateObject("scripting.filesystemobject")
>>  
>> Set fld = fso.getfolder("C:\Users\admin\Desktop\test") ' folder having 
>> files
>>  
>> ** **
>>  
>> For Each fil In fld.Files
>>  
>> Set wkb = Workbooks.Open(fil.Path)
>>  
>> ThisWorkbook.Sheets("sample").Copy After:=wkb.Sheets(wkb.Sheets.Count)
>> 
>>  
>> wkb.Save
>>  
>> wkb.Close
>>  
>> Set wkb = Nothing
>>  
>> Next
>>  
>> ** **
>>  
>> Application.ScreenUpdating = True
>>  
>> Application.DisplayAlerts = True
>>  
>> ** **
>>  
>> ** **
>>  
>> ** **
>>  
>> End Sub
>>  
>> ** **
>>  
>> ** **
>>  
>> see if it helps
>>  
>> ** **
>>  
>> ** **
>>  
>> On Mon, Sep 9, 2013 at 7:45 PM, SG  wrote:
>>
>>  Hi Experts,
>>
>> Once again I neeed your help.I have one excel worksheet which is common 
>> for all workbooks. However, that worksheet is not added in the workbooks. I 
>> have atleast 50 excel workbooks in a folder in which i have to add that 
>> single worksheet manually.The name of all worbooks are same only the week 
>> number is different in filenames like report wk-1,report wk-2 & so 
>> on.Please help me with a macro which would add that single sheet in all 
>> workbooks of that folder one by one.
>>
>> Thanks in advance
>>
>> -- 
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’

Re: $$Excel-Macros$$ Macro needed to add worksheet

[SG]

hi Ravi,
 
This code is throwing error "subscript out of range".
Please suggest.
On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote:

>  No dear,
>
>  
>
> Point 1)
>
> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
> Destination:=Workbooks(Mid(fil.Name, 1, _
>
> WorksheetFunction.Find(".", fil.Name) - 
> 1)).Sheets(Sheets.Count).Range("A1")
>
>  
>
> Point 2)
>
> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
> Destination:=Workbooks(Mid(fil.Name, 1,  WorksheetFunction.Find(".", 
> fil.Name) - 1)).Sheets(Sheets.Count).Range("A1")
>
>  
>
> Above code is like in single line you can say. When we use “_” it will 
> continue our current line with next new line. You can see above number 1 
> and 2 point both are same
>
>  
>
> * *
>
> *Warm Regards,*
>
> *Ravi Kumar.*
>
>  
>
> *From:* excel-...@googlegroups.com  [mailto:
> excel-...@googlegroups.com ] *On Behalf Of *SG
> *Sent:* Wednesday, September 11, 2013 5:33 PM
> *To:* excel-...@googlegroups.com 
> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>
>  
>  
> Hi Ravi,
>  
>  
>  
> i think the below code is incomplete.please correct me if i'm wrong.
>  
>  
>  
> " Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
> Destination:=Workbooks(Mid(fil.Name, 1, _"
>  
>
> On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote:
>
>  Try this and change you folder name accordingly …
>
>  
>
>  
>
> Sub copy_sheet()
>
>  
>
> Application.ScreenUpdating = False
>
> Application.DisplayAlerts = False
>
>  
>
>  
>
> primewb = ActiveWorkbook.Name
>
>  
>
> Dim fld As Object, fil As Object, fso As Object
>
> Dim wkb As Workbook
>
>  
>
>  
>
> Set fso = CreateObject("scripting.filesystemobject")
>
> Set fld = fso.getfolder("C:\Users\Admin\Desktop\Some VBA Tricks") ' folder 
> having files
>
>  
>
>  
>
> For Each fil In fld.Files
>
> Set wkb = Workbooks.Open(fil.Path)
>
> Sheets.Add after:=Sheets(Sheets.Count)
>
> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
> Destination:=Workbooks(Mid(fil.Name, 1, _
>
> WorksheetFunction.Find(".", fil.Name) - 
> 1)).Sheets(Sheets.Count).Range("A1")
>
> wkb.Save
>
> wkb.Close
>
> Set wkb = Nothing
>
> Next
>
>  
>
> Application.ScreenUpdating = True
>
> Application.DisplayAlerts = True
>
>  
>
>  
>
> End Sub
>
>  
>
>  
>
> * *
>
> *Warm Regards,*
>
> *Ravi Kumar.*
>
>  
>
> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
> Behalf Of *SG
> *Sent:* Tuesday, September 10, 2013 2:36 PM
> *To:* excel-...@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>
>  
>  
> Thanks for the help but it's not adding my sheet in rest of the workbooks.
> i have attached that single sheet & added the macro in it.Please suggest 
> Where i'm going wrong.
>  
>
> On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote:
>
>   
>  
> Sub copy_sheet()
>  
>  
>  
> Application.ScreenUpdating = False
>  
> Application.DisplayAlerts = False
>  
>  
>  
>  
>  
> Dim fld As Object, fil As Object, fso As Object
>  
> Dim wkb As Workbook
>  
>  
>  
> Set fso = CreateObject("scripting.filesystemobject")
>  
> Set fld = fso.getfolder("C:\Users\admin\Desktop\test") ' folder having 
> files
>  
>  
>  
> For Each fil In fld.Files
>  
> Set wkb = Workbooks.Open(fil.Path)
>  
> ThisWorkbook.Sheets("sample").Copy After:=wkb.Sheets(wkb.Sheets.Count)
>  
> wkb.Save
>  
> wkb.Close
>  
> Set wkb = Nothing
>  
> Next
>  
>  
>  
> Application.ScreenUpdating = True
>  
> Application.DisplayAlerts = True
>  
>  
>  
>  
>  
>  
>  
> End Sub
>  
>  
>  
>  
>  
> see if it helps
>  
>  
>  
>  
>  
> On Mon, Sep 9, 2013 at 7:45 PM, SG  wrote:
>
>  Hi Experts,
>
> Once again I neeed your help.I have one excel worksheet which is common 
> for all workbooks. However, that worksheet is not added in the workbooks. I 
> have atleast 50 excel workbooks in a folder in which i have to add that 
> single worksheet manually.The name of all worbooks are same only the week 

Re: $$Excel-Macros$$ Macro needed to add worksheet

[SG]

Hi Experts
 
please help
 

On Wednesday, September 11, 2013 7:55:14 PM UTC+5:30, SG wrote:

>  hi Ravi,
>  
> This code is throwing error "subscript out of range".
> Please suggest.
> On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote:
>
>>  No dear,
>>
>>  
>>
>> Point 1)
>>
>> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
>> Destination:=Workbooks(Mid(fil.Name, 1, _
>>
>> WorksheetFunction.Find(".", fil.Name) - 
>> 1)).Sheets(Sheets.Count).Range("A1")
>>
>>  
>>
>> Point 2)
>>
>> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
>> Destination:=Workbooks(Mid(fil.Name, 1,  WorksheetFunction.Find(".", 
>> fil.Name) - 1)).Sheets(Sheets.Count).Range("A1")
>>
>>  
>>
>> Above code is like in single line you can say. When we use “_” it will 
>> continue our current line with next new line. You can see above number 1 
>> and 2 point both are same
>>
>>  
>>
>> * *
>>
>> *Warm Regards,*
>>
>> *Ravi Kumar.*
>>
>>  
>>
>> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
>> Behalf Of *SG
>> *Sent:* Wednesday, September 11, 2013 5:33 PM
>> *To:* excel-...@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>>
>>  
>>  
>> Hi Ravi,
>>  
>>  
>>  
>> i think the below code is incomplete.please correct me if i'm wrong.
>>  
>>  
>>  
>> " Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
>> Destination:=Workbooks(Mid(fil.Name, 1, _"
>>  
>>
>> On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote:
>>
>>  Try this and change you folder name accordingly …
>>
>>  
>>
>>  
>>
>> Sub copy_sheet()
>>
>>  
>>
>> Application.ScreenUpdating = False
>>
>> Application.DisplayAlerts = False
>>
>>  
>>
>>  
>>
>> primewb = ActiveWorkbook.Name
>>
>>  
>>
>> Dim fld As Object, fil As Object, fso As Object
>>
>> Dim wkb As Workbook
>>
>>  
>>
>>  
>>
>> Set fso = CreateObject("scripting.filesystemobject")
>>
>> Set fld = fso.getfolder("C:\Users\Admin\Desktop\Some VBA Tricks") ' 
>> folder having files
>>
>>  
>>
>>  
>>
>> For Each fil In fld.Files
>>
>> Set wkb = Workbooks.Open(fil.Path)
>>
>> Sheets.Add after:=Sheets(Sheets.Count)
>>
>> Workbooks(primewb).Sheets("sample").Range("A4:c22").Copy 
>> Destination:=Workbooks(Mid(fil.Name, 1, _
>>
>> WorksheetFunction.Find(".", fil.Name) - 
>> 1)).Sheets(Sheets.Count).Range("A1")
>>
>> wkb.Save
>>
>> wkb.Close
>>
>> Set wkb = Nothing
>>
>> Next
>>
>>  
>>
>> Application.ScreenUpdating = True
>>
>> Application.DisplayAlerts = True
>>
>>  
>>
>>  
>>
>> End Sub
>>
>>  
>>
>>  
>>
>> * *
>>
>> *Warm Regards,*
>>
>> *Ravi Kumar.*
>>
>>  
>>
>> *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On 
>> Behalf Of *SG
>> *Sent:* Tuesday, September 10, 2013 2:36 PM
>> *To:* excel-...@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet
>>
>>  
>>  
>> Thanks for the help but it's not adding my sheet in rest of the workbooks.
>> i have attached that single sheet & added the macro in it.Please suggest 
>> Where i'm going wrong.
>>  
>>
>> On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote:
>>
>>   
>>  
>> Sub copy_sheet()
>>  
>>  
>>  
>> Application.ScreenUpdating = False
>>  
>> Application.DisplayAlerts = False
>>  
>>  
>>  
>>  
>>  
>> Dim fld As Object, fil As Object, fso As Object
>>  
>> Dim wkb As Workbook
>>  
>>  
>>  
>> Set fso = CreateObject("scripting.filesystemobject")
>>  
>> Set fld = fso.getfolder("C:\Users\admin\Desktop\test") ' folder having 
>> files
>>  
>>  
>>  
>> For Each fil In fld.Files
>>  
>> Set wkb = Wo

$$Excel-Macros$$ Values for Radio Buttons

[SG]

Hi experts

Please help me with your effective solution. I’m working on a training 
template in which individual scores would be analyzed & scores for all 
trainees comes in summary sheet. For this, in each individual sheet I have 
used radio buttons. What I need is for a parameter, if I click under 1, it 
generate value 1, under 2, it generate value 2 & so on.

The problems come when I click the first button of second point & it 
generates the value 6. It should generate 1 so that I can lookup the unique 
value for each point in summary sheet to get the scores collated.  I have 
attached the same for your reference. Please help me on this ASAP.

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


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

Re: $$Excel-Macros$$ Values for Radio Buttons

[SG]

O yes.
Thanku so much. I guess the idea behind its working  is that you have 
unfreezed the linked cell in properties.
Am i right sir?? 

On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote:
>
> Something like this?
>
>
> On Mon, Feb 17, 2014 at 4:58 PM, SG >wrote:
>
>>
>>
>> Hi experts
>>
>> Please help me with your effective solution. I’m working on a training 
>> template in which individual scores would be analyzed & scores for all 
>> trainees comes in summary sheet. For this, in each individual sheet I have 
>> used radio buttons. What I need is for a parameter, if I click under 1, it 
>> generate value 1, under 2, it generate value 2 & so on.
>>
>> The problems come when I click the first button of second point & it 
>> generates the value 6. It should generate 1 so that I can lookup the unique 
>> value for each point in summary sheet to get the scores collated.  I have 
>> attached the same for your reference. Please help me on this ASAP.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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 group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Values for Radio Buttons

[SG]

okeyzzThanku so much for explanation.
One more thing, i'm unable to find this group box in toolbox. Is this is 
the Microsoft Form 2.0 frame?? if yes, then how you have grouped these 
radio buttons in this box. Because on dragging this, we have to use frame 
object for option button in which linked cell is not available.

On Monday, February 17, 2014 6:51:55 PM UTC+5:30, Abhi wrote:
>
> Not exactly!
>
> For Option Buttons to act individually, you need to keep them in a group. 
> The box you are seeing around the 5 buttons is a Frame (came from same 
> toolbox) and when the option buttons are inside a frame, they are grouped 
> and work individually from others. Since there were 3 questions, there are 
> 3 groups. For each question you might add from here on now, you would need 
> to copy the buttons as well as frame and link them to another reference 
> cell.
>
> Let me know if you need any further help with this.
>
> Regards, 
>
> Abhishek 
>
>
> On Mon, Feb 17, 2014 at 6:42 PM, SG >wrote:
>
>> O yes.
>> Thanku so much. I guess the idea behind its working  is that you have 
>> unfreezed the linked cell in properties.
>> Am i right sir?? 
>>
>>
>> On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote:
>>
>>> Something like this?
>>>
>>>
>>> On Mon, Feb 17, 2014 at 4:58 PM, SG  wrote:
>>>
>>>>
>>>>
>>>> Hi experts
>>>>
>>>> Please help me with your effective solution. I’m working on a training 
>>>> template in which individual scores would be analyzed & scores for all 
>>>> trainees comes in summary sheet. For this, in each individual sheet I have 
>>>> used radio buttons. What I need is for a parameter, if I click under 1, it 
>>>> generate value 1, under 2, it generate value 2 & so on.
>>>>
>>>> The problems come when I click the first button of second point & it 
>>>> generates the value 6. It should generate 1 so that I can lookup the 
>>>> unique 
>>>> value for each point in summary sheet to get the scores collated.  I have 
>>>> attached the same for your reference. Please help me on this ASAP.
>>>>
>>>> -- 
>>>> 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...@googlegroups.com.
>>>> To post to this group, send email to excel-...@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 group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Values for Radio Buttons

[SG]

Hi Abhi,

Thanku so much for the clarification.

On Monday, February 17, 2014 7:41:40 PM UTC+5:30, Abhi wrote:
>
> It's from the same form control toolbox you have picked the option button 
> from. Upon hovering the mouse pointer over the control, it says "Group Box 
> (Form Control)". You pick the group box first, draw it to the size on 
> sheet, then you put option buttons in there. After you have put all the 
> buttons, just right click on any of them (without selecting the group box) 
> and choose the reference cell where the value would be placed.
>
>
> On Mon, Feb 17, 2014 at 7:23 PM, SG >wrote:
>
>> okeyzzThanku so much for explanation.
>> One more thing, i'm unable to find this group box in toolbox. Is this is 
>> the Microsoft Form 2.0 frame?? if yes, then how you have grouped these 
>> radio buttons in this box. Because on dragging this, we have to use frame 
>> object for option button in which linked cell is not available.
>>
>>
>> On Monday, February 17, 2014 6:51:55 PM UTC+5:30, Abhi wrote:
>>
>>> Not exactly!
>>>
>>> For Option Buttons to act individually, you need to keep them in a 
>>> group. The box you are seeing around the 5 buttons is a Frame (came from 
>>> same toolbox) and when the option buttons are inside a frame, they are 
>>> grouped and work individually from others. Since there were 3 questions, 
>>> there are 3 groups. For each question you might add from here on now, you 
>>> would need to copy the buttons as well as frame and link them to another 
>>> reference cell.
>>>
>>> Let me know if you need any further help with this.
>>>
>>> Regards, 
>>>
>>> Abhishek 
>>>
>>>
>>> On Mon, Feb 17, 2014 at 6:42 PM, SG  wrote:
>>>
>>>> O yes.
>>>> Thanku so much. I guess the idea behind its working  is that you have 
>>>> unfreezed the linked cell in properties.
>>>> Am i right sir?? 
>>>>
>>>>
>>>> On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote:
>>>>
>>>>> Something like this?
>>>>>
>>>>>
>>>>> On Mon, Feb 17, 2014 at 4:58 PM, SG  wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> Hi experts
>>>>>>
>>>>>> Please help me with your effective solution. I’m working on a 
>>>>>> training template in which individual scores would be analyzed & scores 
>>>>>> for 
>>>>>> all trainees comes in summary sheet. For this, in each individual sheet 
>>>>>> I 
>>>>>> have used radio buttons. What I need is for a parameter, if I click 
>>>>>> under 
>>>>>> 1, it generate value 1, under 2, it generate value 2 & so on.
>>>>>>
>>>>>> The problems come when I click the first button of second point & it 
>>>>>> generates the value 6. It should generate 1 so that I can lookup the 
>>>>>> unique 
>>>>>> value for each point in summary sheet to get the scores collated.  I 
>>>>>> have 
>>>>>> attached the same for your reference. Please help me on this ASAP.
>>>>>>
>>>>>> -- 
>>>>>> 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.
>>>>>> --- 
>>>&

$$Excel-Macros$$ Sort Dynamic range - Help needed

[SG]

Hi Experts,

I need a solution to sort the data on basis of location & highest marks. 
For this, i have created a dynamic named range to call it by macro & then 
sort. My problem comes when the range also selects the blank formulas in 
sheet which shouldn't be selected. Only visible data should selected in 
that range. Please help me with the solution.

Thanks in advance.

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


Dynamic Range.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: $$Excel-Macros$$ Sort Dynamic range - Help needed

[SG]

Hi vabz,

Thanks for the reply but this is not solving my purpose.The data is not 
sorted on location & highest marks & i don't want to paste the visible data 
anywhere else. The sorting done only at that location i.e from colun A to D.

On Thursday, April 17, 2014 2:02:46 PM UTC+5:30, Vabz wrote:
>
> PFA
>
> Thx
>
>
> On Thu, Apr 17, 2014 at 1:28 PM, SG >wrote:
>
>>
>>
>> Hi Experts,
>>
>> I need a solution to sort the data on basis of location & highest marks. 
>> For this, i have created a dynamic named range to call it by macro & then 
>> sort. My problem comes when the range also selects the blank formulas in 
>> sheet which shouldn't be selected. Only visible data should selected in 
>> that range. Please help me with the solution.
>>
>> Thanks in advance.
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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.

$$Excel-Macros$$ Summarise data based on multiple combination of criterias

[SG]

Hi Experts,

I'm looking for a solution to summarise the data based on multiple 
criterias in drop downs.  My data has 3 dropdowns.I have tried it using 
advance filter 
but it fails when all criterias selected as "All". The problem starts when 
any of the dropdown is selected as "All" .I'm unable to find the correct 
solution. 
Please help me with a solution.PFA sheet for your reference.

Thanks in advance

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


Cost.xlsx
Description: MS-Excel 2007 spreadsheet

Re: $$Excel-Macros$$ Summarise data based on multiple combination of criterias

[SG]

Amazing Vabz...thanksss..Can you please explain the idea behind this???

On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote:
>
> Hi
>
> In cell C2 put * (only asterik) instead of ALL.
>
> Cheers!!
>
>
> On Wed, Jun 18, 2014 at 6:10 PM, SG > 
> wrote:
>
>>
>> Hi Experts,
>>
>> I'm looking for a solution to summarise the data based on multiple 
>> criterias in drop downs.  My data has 3 dropdowns.I have tried it using 
>> advance filter 
>> but it fails when all criterias selected as "All". The problem starts 
>> when any of the dropdown is selected as "All" .I'm unable to find the 
>> correct solution. 
>> Please help me with a solution.PFA sheet for your reference.
>>
>> Thanks in advance
>>
>> -- 
>> 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...@googlegroups.com .
>> To post to this group, send email to excel-...@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$$ Summarise data based on multiple combination of criterias

[SG]

yeah got it...one more thing..in reports, we can't say customer to select * 
for "All" because it's not user friendly. What to do in that case??

On Wednesday, June 18, 2014 6:53:29 PM UTC+5:30, Vabz wrote:
>
> when you say "ALL", for excel / computer it is * (asterik), remember old 
> days when DOS were used & where command was Dir *.*
>
> Cheers!!
>
>
>
> On Wed, Jun 18, 2014 at 6:48 PM, SG > 
> wrote:
>
>> Amazing Vabz...thanksss..Can you please explain the idea behind this???
>>
>>
>> On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote:
>>
>>> Hi
>>>
>>> In cell C2 put * (only asterik) instead of ALL.
>>>
>>> Cheers!!
>>>
>>>
>>> On Wed, Jun 18, 2014 at 6:10 PM, SG  wrote:
>>>
>>>>
>>>> Hi Experts,
>>>>
>>>> I'm looking for a solution to summarise the data based on multiple 
>>>> criterias in drop downs.  My data has 3 dropdowns.I have tried it using 
>>>> advance filter 
>>>> but it fails when all criterias selected as "All". The problem starts 
>>>> when any of the dropdown is selected as "All" .I'm unable to find the 
>>>> correct solution. 
>>>> Please help me with a solution.PFA sheet for your reference.
>>>>
>>>> Thanks in advance
>>>>
>>>> -- 
>>>> 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...@googlegroups.com.
>>>> To post to this group, send email to excel-...@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...@googlegroups.com .
>> To post to this group, send email to excel-...@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.co

Re: $$Excel-Macros$$ Summarise data based on multiple combination of criterias

[SG]

Thankyou so much Vabz

On Wednesday, June 18, 2014 8:50:04 PM UTC+5:30, Vabz wrote:
>
> Chek this,,,
>
>
> On Wed, Jun 18, 2014 at 7:08 PM, SG > 
> wrote:
>
>> yeah got it...one more thing..in reports, we can't say customer to select 
>> * for "All" because it's not user friendly. What to do in that case??
>>
>>
>> On Wednesday, June 18, 2014 6:53:29 PM UTC+5:30, Vabz wrote:
>>
>>> when you say "ALL", for excel / computer it is * (asterik), remember old 
>>> days when DOS were used & where command was Dir *.*
>>>
>>> Cheers!!
>>>
>>>
>>>
>>> On Wed, Jun 18, 2014 at 6:48 PM, SG  wrote:
>>>
>>>> Amazing Vabz...thanksss..Can you please explain the idea behind this???
>>>>
>>>>
>>>> On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> In cell C2 put * (only asterik) instead of ALL.
>>>>>
>>>>> Cheers!!
>>>>>
>>>>>
>>>>> On Wed, Jun 18, 2014 at 6:10 PM, SG  wrote:
>>>>>
>>>>>>
>>>>>> Hi Experts,
>>>>>>
>>>>>> I'm looking for a solution to summarise the data based on multiple 
>>>>>> criterias in drop downs.  My data has 3 dropdowns.I have tried it using 
>>>>>> advance filter 
>>>>>> but it fails when all criterias selected as "All". The problem starts 
>>>>>> when any of the dropdown is selected as "All" .I'm unable to find the 
>>>>>> correct solution. 
>>>>>> Please help me with a solution.PFA sheet for your reference.
>>>>>>
>>>>>> Thanks in advance
>>>>>>
>>>>>> -- 
>>>>>> 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...@googlegroups.com.
>>>>>> To post to this group, send email to excel-...@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 a