Re: $$Excel-Macros$$ database copy paste issue from one workbook to another workbook

[Kat]

Hi Paul,

Thank you for your response. The first time it copied all is fine. However, 
the second time it still copies all. I need it to copy only the updated 
data at next blank row instead. Please see attached the screenshot for the 
result that I want it to be. For first time, it copies until row 7. For 
second time, there are updated data, so it copies all the updated data at 
next blank row. Thanks for your help.

On Wednesday, 27 January 2016 20:24:06 UTC+8, Paul Schreiner wrote:
>
> OK, I think I need a better example of what you HAVE and what you want the 
> RESULT to be.
>
> In the sample files you provided, one was a blank sheet and the other had 
> sample data.
> your original macro looks like it compared the two workbooks/worksheets
> and for each row was intended to compare column A of the "fortest" 
> workbook to column C of the "tested" workbook (and compare B to D and C to 
> E)
> If they differed, add the record from "fortest" to the END of the data in 
> "Tested".
>
> since in the example file, "tested" is blank, 
> it meant that ALL non-blank rows in "fortest" were different than 
> "tested", 
> so the macro would copy them all.
> the result would be that "tested" would look identical to "fortest".
>
>
> since that is what my macro DID, from your response that isn't what you 
> intended.
>
> So, please provide an example of what the files like "before" and what you 
> want the files to look like after the macro runs.
>
> *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 Tuesday, January 26, 2016 8:20 PM, Kat  > wrote:
>
>
>
> Hi Paul,
>
> The code works. However, it doesn't achieve the desired result. The code 
> doesn''t show all the updated data in the next blank row. It doesn't seem 
> to compare the database in both workboooks.  
>
>
> Cheers 
>
> On Tuesday, 26 January 2016 21:20:47 UTC+8, Paul Schreiner wrote:
>
> I updated the macro to this.
> give it a try:
>
> Option Explicit
> Sub test()
> Dim varSheetA As Variant
> Dim varSheetB As Variant
> Dim strRangeToCheck As String
> Dim strRangeToC As String
> Dim iRow As Long
> Dim iCol As Long
> Dim wbkA As Workbook
> Dim eRow As Long
>
> Set wbkA = Workbooks.Open(Filename:="C:\ temp\vba\fortest.xlsx")
> strRangeToCheck = "A:C"
> strRangeToC = "C:E"
> 'Debug.Print Now
> varSheetA = wbkA.Worksheets("Sheet1"). Range(strRangeToCheck)
> varSheetB = ThisWorkbook.Worksheets(" Sheet1").Range(strRangeToC)
> 'Debug.Print Now
> 'Warning:  VarSheetA and VarSheetB result in arrays of the ENTIRE 
> respective workbook
> '   using LBound() to UBound() causes the ENTIRE workbook to be 
> processed,
> '   regardless of how much is actually used.
> '  I'd suggest using something like:
> '  thisworkbook.select
> '  MaxRow = ActiveCell.SpecialCells( xlLastCell).Row
> '  MaxCol = ActiveCell.SpecialCells( xlLastCell).Column
> '  For iRow = 1 to MaxRow
> '  For iCol = 1 to MaxCol
> '- -
> For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
> For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
> If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
> ' Cells are identical.
> ' Do nothing.
> Else
> eRow = ThisWorkbook.Worksheets(" Sheet1").Cells(Rows.Count, 
> 3).End(xlUp).Row + 1
> ThisWorkbook.Sheets("Sheet1"). Range("C" & eRow & ":E" & 
> eRow).Value = wbkA.Sheets("Sheet1").Range(" A" & iRow & ":C" & iRow).Value
> Exit For
> End If
> Next
> Next
> wbkA.Close savechanges:=False
> End Sub
> *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, January 25, 2016 8:14 PM, Kat  wrote:
>
>
>
> Hi Paul,
>
> Please see attached the fortest.xlsx file for your attention.
>
>
> Cheers
>
> On Tuesday, 26 January 2016 04:18:22 UTC+8, Paul Schreiner wrote:
>
> you've only sent the "Tested.xlsm" file
> Can you please include the fortest.xlsx file?
> Otherwise, I would need to GUESS what  file looks like.
> It LOOKS like you're comparing columns A-C in one workbook to range C-E in 
> another.
>
> However:
> you have:
>
> Set varSheetA = wbkA.Worksheets("Sheet1")
> Set varSheetB = ThisWorkbook.Worksheets(" Sheet1")
>
> followed by:
> varSheetA = Worksheets("Sheet1").Range( strRangeToCheck)
> varSheetB = Worksheets("Sheet1").Range( 

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

$$Excel-Macros$$ Client in Santa Clara, CA need SAP SD/FI Consultant--Need GC or Citizens ONLY

[MADHU-GenuineItLlc]

Hi Partner,

Hope you are doing great …

Please send resume to ma...@genuineitllc.com



*Job Title:** SAP SD/FI Consultant*

*Location: **Santa Clara, CA, US*



*Need GC or Citizens ONLY*



*Qualification*

Must have 8+ years of experience with *SAP SD*

Must have 2+ years of experience with *FI Revenue Recognition*



---

*With Warm Regards *

*Madhu Roy*

GenuineIT LLC *Certified Women Owned Business*

Making IT Happen…

Ph: 832 539 3475

Fax: 281-605-5969

*E-Mail* :ma...@genuineitllc.com

*Gmail *: *nandimadhu74 , madhu.genuineitllc*

*YM  *: madhu_recruiter



Looking ahead to work with you for better Business prospects



According to Bill S.1618 Title III passed by the 105th US Congress, this
message is not considered as "Spam" as we have included the contact
information. If you wish to be removed from our mailing list, please
respond with "remove" in the subject field. We apologize for any
inconvenience caused.

Please consider the environment before printing this e-mail

-- 
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$$ Client in Miami, FL need SuccessFactors Learning Management System Consultant

[MADHU-GenuineItLlc]

Hi Partner,

Hope you are doing great …

Please send resume to ma...@genuineitllc.com



*Job Title:** SAP SF-LMS Consultant*

*Location: **Miami, FL**.*



*JD:-*

Candidates with insights into Learning Management System strategy who can
design & deliver solutions for our global clients. Consultant should be
able to analyze & document requirements and implement a SuccessFactors
Learning Management solution and integrate the same with HR Systems



*Skills required:*

· Certification in *SuccessFactors Learning Management System (LMS)*

· Experience in Learning Management System Advanced Analytics

· Detailed knowledge & experience in SuccessFactors LAUNCH Project
Methodology & LMS specific deliverables

· Integration of the LMS module to SuccessFactors Employee Profile,
Employee Central or third party HCM data system

· Excellent understanding of Learning Management business processes
in Global Organizations

· Ability to provide consulting services based on Learning
Management System best practices and advise clients

· Ability to translate the Learning requirements into Learning
Management System objects

· Integrate SuccessFactors Learning Management System solution with
3rd party systems

· Good understanding of XML & RSS Feeds

· Experience in working with SuccessFactors Development teams to
complete the design & development of SuccessFactors LMS module

· Ability to provide user training to System Administrators,
Learning Management System process owners and Learning Management System
teams

· Comfortable working with XML and SFSF Data Models

· Deep understanding of BizX Platform Data Model and how it relates
to other modules in the Talent Suite



---

*With Warm Regards *

*Madhu Roy*

GenuineIT LLC *Certified Women Owned Business*

Making IT Happen…

Ph: 832 539 3475

Fax: 281-605-5969

*E-Mail* :ma...@genuineitllc.com

*Gmail *: *nandimadhu74 , madhu.genuineitllc*

*YM  *: madhu_recruiter



Looking ahead to work with you for better Business prospects



According to Bill S.1618 Title III passed by the 105th US Congress, this
message is not considered as "Spam" as we have included the contact
information. If you wish to be removed from our mailing list, please
respond with "remove" in the subject field. We apologize for any
inconvenience caused.

Please consider the environment before printing this e-mail

-- 
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$$ Urgent need HANA Security Architect ---Santa Clara, CA

[MADHU-GenuineItLlc]

Hi Partner,

Hope you are doing great …

Please send resume to ma...@genuineitllc.com





*Title**:** HANA Security Architect*

*Location**: **Santa Clara, CA.*



*Overview:*

we know that with the right people on board, anything is possible. The
quality, integrity, and commitment of our employees have been key factors
in our company’s growth and market presence. By hiring the best people and
helping them grow both professionally and personally, we ensure a bright
future for client and for the people who work here.





*Qualifications:*

ü  The candidate should be a strong team player and have the ability to
work independently.
The job description of the consultant is as follows.

- Experience designing and implementing security for SAP HANA
- Experience Creating customized roles in HANA DB for Developers, Modelers,
Technical Admins, End Users, Power Users.
- Design, Restrict and Control authorizations for HANA DB objects and
Packages/Contents based on System Privileges, Objects Privileges and
Analytic Privileges for various Schema Users
- Define controls to restrict Create, Drop, Alter other DB Admin rights on
HANA DB Schema and its objects
- Design transportation of Security Objects/Roles within HANA Landscape
- Define user creation and Role assignment processes
- Set up password policies
- Create Groups in BOBJ Server for different type of Users (Admins,
Managers, Developers, End Users etc..)
- Define and Control access to various Folders
- Setup SSO with AD or LDAP for SAP, Enterprise and other types of
Authentication
- Define & Control access at Object level for different types of BOBJ
Reports (Full control, View, Schedule, View on Demand)
- Transportation of Security objects within BOBJ Landscape
- Integration of BOBJ Security with HANA DB Security Roles

This is pure hands on role



---

*With Warm Regards *

*Madhu Roy*

GenuineIT LLC *Certified Women Owned Business*

Making IT Happen…

Ph: 832 539 3475

Fax: 281-605-5969

*E-Mail* :ma...@genuineitllc.com

*Gmail *: *nandimadhu74 , madhu.genuineitllc*

*YM  *: madhu_recruiter



Looking ahead to work with you for better Business prospects



According to Bill S.1618 Title III passed by the 105th US Congress, this
message is not considered as "Spam" as we have included the contact
information. If you wish to be removed from our mailing list, please
respond with "remove" in the subject field. We apologize for any
inconvenience caused.

Please consider the environment before printing this e-mail

-- 
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$$ database copy paste issue from one workbook to another workbook

[Paul Schreiner]

OK, I think I need a better example of what you HAVE and what you want the 
RESULT to be.
In the sample files you provided, one was a blank sheet and the other had 
sample data.your original macro looks like it compared the two 
workbooks/worksheetsand for each row was intended to compare column A of the 
"fortest" workbook to column C of the "tested" workbook (and compare B to D and 
C to E)If they differed, add the record from "fortest" to the END of the data 
in "Tested".
since in the example file, "tested" is blank, it meant that ALL non-blank rows 
in "fortest" were different than "tested", so the macro would copy them all.the 
result would be that "tested" would look identical to "fortest".

since that is what my macro DID, from your response that isn't what you 
intended.
So, please provide an example of what the files like "before" and what you want 
the files to look like after the macro runs.
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 Tuesday, January 26, 2016 8:20 PM, Kat  wrote:
 
 

 Hi Paul,

The code works. However, it doesn't achieve the desired result. The code 
doesn''t show all the updated data in the next blank row. It doesn't seem to 
compare the database in both workboooks.  


Cheers 

On Tuesday, 26 January 2016 21:20:47 UTC+8, Paul Schreiner wrote:
I updated the macro to this.give it a try:
Option ExplicitSub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim strRangeToC As String
Dim iRow As Long
Dim iCol As Long
Dim wbkA As Workbook
Dim eRow As Long
Set wbkA = Workbooks.Open(Filename:="C:\ temp\vba\fortest.xlsx")strRangeToCheck 
= "A:C"
strRangeToC = "C:E"
'Debug.Print Now
varSheetA = wbkA.Worksheets("Sheet1"). Range(strRangeToCheck)
varSheetB = ThisWorkbook.Worksheets(" Sheet1").Range(strRangeToC)
'Debug.Print Now'Warning:  VarSheetA and VarSheetB result in arrays of the 
ENTIRE respective workbook
'   using LBound() to UBound() causes the ENTIRE workbook to be processed,
'   regardless of how much is actually used.
'  I'd suggest using something like:
'  thisworkbook.select
'  MaxRow = ActiveCell.SpecialCells( xlLastCell).Row
'  MaxCol = ActiveCell.SpecialCells( xlLastCell).Column
'  For iRow = 1 to MaxRow
'  For iCol = 1 to MaxCol
'- -
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
    If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
    ' Cells are identical.
    ' Do nothing.
    Else
    eRow = ThisWorkbook.Worksheets(" Sheet1").Cells(Rows.Count, 
3).End(xlUp).Row + 1
    ThisWorkbook.Sheets("Sheet1"). Range("C" & eRow & ":E" & 
eRow).Value = wbkA.Sheets("Sheet1").Range(" A" & iRow & ":C" & iRow).Value
    Exit For
    End If
    Next
Next
wbkA.Close savechanges:=False
End Sub
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, January 25, 2016 8:14 PM, Kat  wrote:
 
 

 Hi Paul,

Please see attached the fortest.xlsx file for your attention.


Cheers

On Tuesday, 26 January 2016 04:18:22 UTC+8, Paul Schreiner wrote:
you've only sent the "Tested.xlsm" fileCan you please include the fortest.xlsx 
file?Otherwise, I would need to GUESS what  file looks like.It LOOKS like 
you're comparing columns A-C in one workbook to range C-E in another.
However:you have:
Set varSheetA = wbkA.Worksheets("Sheet1")Set varSheetB = 
ThisWorkbook.Worksheets(" Sheet1")
followed by:varSheetA = Worksheets("Sheet1").Range( strRangeToCheck)
varSheetB = Worksheets("Sheet1").Range( strRangeToC)
which will cause varSheetA and varSheetB to be defined for the currently active 
workbook.
If you can send me a sample data file, I'd be glad to put something together 
for you.
 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 Sunday, January 24, 2016 8:32 PM, Kat  wrote:
 
 

 Hi guys,

I want to copy paste database from one Workbook A to Workbook B. When new row 
data is added in Workbook A, the excel vba code will compare whether the data 
is exist in Workbook B. If no, new row data needs to be copied and pasted 
automatically to last existing row in Workbook B. I've had issue with my code. 
Can 

Re: $$Excel-Macros$$ Merge multiple Workbook

[Devendra Sahay]

Hi Sandeep,

I am sharing the code, If any group member can modify the code according to
you, would be helpful.

*** If we can assign few header names, of which data we want to extract
from the workbooks. It would be relevant. *


*Code *

Sub Merge_Files()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Long
Dim col_no As Long, row_no As Long
Dim arr_ws As Variant
Dim ws1 As Worksheet, wb1 As Workbook
Dim col_ws1 As Long, row_ws1 As Long

Set wb1 = ThisWorkbook
Set ws1 = ActiveSheet

Set d = Application.FileDialog(msoFileDialogFolderPicker)
d.Show
 fldpath = d.SelectedItems(1) & "\"

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(fldpath)
i = 0
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
Debug.Print objFile
i = i + 1
Workbooks.Open FileName:=objFile
row_no = ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count,
1)).End(xlUp).Row
col_no = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1,
Columns.Count)).End(xlToLeft).Column
arr_ws = ActiveSheet.Range(Cells(1, 1), Cells(row_no, col_no))
ActiveWorkbook.Close savechanges = no
ws1.Activate
If ws1.Range("A1").Value <> "" Then
row_ws1 = ActiveSheet.Range(Cells(Rows.Count, 1),
Cells(Rows.Count, 1)).End(xlUp).Row + 1
col_ws1 = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1,
Columns.Count)).End(xlToLeft).Column
Else
row_ws1 = 1
col_ws1 = 1
End If
ws1.Range(Cells(row_ws1, 1), Cells(row_ws1 + row_no - 1, col_no)) =
arr_ws

Next objFile
End Sub





On Wed, Jan 27, 2016 at 2:19 PM, Sandeep Chhajer 
wrote:

> Hi Devendra,
>
> Thanks for your reply. The problem is the headers are not same in all the
> files. But some column headers are same in all the workbooks.
>
> So,  my requiremnt is a Macro which can search the column with that
> particular header name (common accross All the workbooks and paste it.
>
> Regards,
>
> Sandeep K Chhajer.
>
> Sent from my BlackBerry 10 smartphone.
> *From: *Devendra Sahay
> *Sent: *Wednesday 27 January 2016 10:59
> *To: *excel-macros@googlegroups.com
> *Reply To: *excel-macros@googlegroups.com
> *Subject: *Re: Fw: $$Excel-Macros$$ Merge multiple Workbook
>
> Hi Sandeep,
>
>
> I have a code, which simply goes to specific folder & open the workbooks
> one by one & copy the data and paste them in same sheet (Where the marcro
> is written).
>
> ** Make sure all the files have same header, because it copies all the
> data including the header.
>
> So if this is relevant, I can share the code.
>
> Thanks.
> Devendra
>
> On Tue, Jan 26, 2016 at 8:52 PM, Sandeep Chhajer  > wrote:
>
>> Hi,
>>
>>
>> Any way to solve this problem.
>>
>> Sent from my BlackBerry 10 smartphone.
>> *From: *Sandeep Chhajer 
>> *Sent: *Sunday 24 January 2016 18:07
>> *To: *Excel Macros
>> *Reply To: *excel-macros@googlegroups.com
>> *Subject: *$$Excel-Macros$$ Merge multiple Workbook
>>
>> Hi Excel guru,
>>
>> I have found macros for merging multiple workbook (kept in a single
>> folder) into a single worksheet.
>>
>> But my requirement is a step ahead...can that macro to be changed to copy
>> only specified column from every wrokbook (Sheet1). Like it will search in
>> all the workbooks (sheet1 row no 1 to 6 ) kept in a folder; and wherever it
>> will find "Name" , "Emp code"  or " Net payment" then copy the range into
>> new sheet one after one.
>>
>> the code I am using (got from website) for merging the workbooks are
>> pasted below. (Any thing new to solve the above problem is also welcome)
>>
>>
>> Sub simpleXlsMerger()
>> Dim bookList As Workbook
>> Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As
>> Object
>> Application.ScreenUpdating = False
>> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>>
>> 'change folder path of excel files here
>> Set dirObj = mergeObj.Getfolder("C:WorkingMTMT 2015 16MT Q3Actuas All  ")
>> Set filesObj = dirObj.Files
>> For Each everyObj In filesObj
>> Set bookList = Workbooks.Open(everyObj)
>>
>> 'change "A2" with cell reference of start point for every files here
>> 'for example "B3:IV" to merge all files start from columns B and rows 3
>> 'If you're files using more than IV column, change it to the latest column
>> 'Also change "A" column on "A65536" to the same column as start point
>> Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
>> ThisWorkbook.Worksheets(1).Activate
>>
>> 'Do not change the following column. It's not the same column as above
>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>> Application.CutCopyMode = False
>> bookList.Close
>> Next
>> End Sub
>>
>> Thanking you in advance.
>> --
>> Regards,
>> CA Sandeep Kumar Chhajer
>>
>> --
>> Are you =EXP(E:RT) or 

$$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$$ Merge multiple Workbook

[Sandeep Chhajer]

  Hi Devendra, Thanks for your reply. The problem is the headers are not same in all the files. But some column headers are same in all the workbooks.So,  my requiremnt is a Macro which can search the column with that particular header name (common accross All the workbooks and paste it.Regards,Sandeep K Chhajer.     Sent from my BlackBerry 10 smartphone.From: Devendra SahaySent: Wednesday 27 January 2016 10:59To: excel-macros@googlegroups.comReply To: excel-macros@googlegroups.comSubject: Re: Fw: $$Excel-Macros$$ Merge multiple WorkbookHi Sandeep,I have a code, which simply goes to specific folder & open the workbooks one by one & copy the data and paste them in same sheet (Where the marcro is written).** Make sure all the files have same header, because it copies all the data including the header. So if this is relevant, I can share the code.Thanks.Devendra On Tue, Jan 26, 2016 at 8:52 PM, Sandeep Chhajer  wrote:  Hi,Any way to solve this problem.Sent from my BlackBerry 10 smartphone.From: Sandeep Chhajer Sent: Sunday 24 January 2016 18:07To: Excel MacrosReply To: excel-macros@googlegroups.comSubject: $$Excel-Macros$$ Merge multiple WorkbookHi Excel guru,I have found macros for merging multiple workbook (kept in a single folder) into a single worksheet.But my requirement is a step ahead...can that macro to be changed to copy only specified column from every wrokbook (Sheet1). Like it will search in all the workbooks (sheet1 row no 1 to 6 ) kept in a folder; and wherever it will find "Name" , "Emp code"  or " Net payment" then copy the range into new sheet one after one.the code I am using (got from website) for merging the workbooks are pasted below. (Any thing new to solve the above problem is also welcome)Sub simpleXlsMerger()Dim bookList As WorkbookDim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As ObjectApplication.ScreenUpdating = FalseSet mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files hereSet dirObj = mergeObj.Getfolder("C:WorkingMTMT 2015 16MT Q3Actuas All  ")Set filesObj = dirObj.FilesFor Each everyObj In filesObjSet bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here'for example "B3:IV" to merge all files start from columns B and rows 3'If you're files using more than IV column, change it to the latest column'Also change "A" column on "A65536" to the same column as start pointRange("A2:IV" & Range("A65536").End(xlUp).Row).CopyThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as aboveRange("A65536").End(xlUp).Offset(1, 0).PasteSpecialApplication.CutCopyMode = FalsebookList.CloseNextEnd Sub      Thanking you in advance.-- Regards,CA Sandeep Kumar Chhajer




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

Re: $$Excel-Macros$$ Merge multiple Workbook

[Sandeep Chhajer]

  Thanks Devendra, I will send you some sample files for better understanding my requirements.Thanks again for your help.  Sent from my BlackBerry 10 smartphone.From: Devendra SahaySent: Wednesday 27 January 2016 15:32To: excel-macros@googlegroups.comReply To: excel-macros@googlegroups.comSubject: Re: $$Excel-Macros$$ Merge multiple WorkbookHi Sandeep,I am sharing the code, If any group member can modify the code according to you, would be helpful. ** If we can assign few header names, of which data we want to extract from the workbooks. It would be relevant. Code Sub Merge_Files()Dim objFSO As ObjectDim objFolder As ObjectDim objFile As ObjectDim i As LongDim col_no As Long, row_no As LongDim arr_ws As VariantDim ws1 As Worksheet, wb1 As WorkbookDim col_ws1 As Long, row_ws1 As LongSet wb1 = ThisWorkbookSet ws1 = ActiveSheetSet d = Application.FileDialog(msoFileDialogFolderPicker)d.Show fldpath = d.SelectedItems(1) & "\"'Create an instance of the FileSystemObjectSet objFSO = CreateObject("Scripting.FileSystemObject")'Get the folder objectSet objFolder = objFSO.GetFolder(fldpath)i = 0'loops through each file in the directory and prints their names and pathFor Each objFile In objFolder.FilesDebug.Print objFile    i = i + 1    Workbooks.Open FileName:=objFile    row_no = ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row    col_no = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1, Columns.Count)).End(xlToLeft).Column    arr_ws = ActiveSheet.Range(Cells(1, 1), Cells(row_no, col_no))    ActiveWorkbook.Close savechanges = no    ws1.Activate    If ws1.Range("A1").Value <> "" Then    row_ws1 = ActiveSheet.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row + 1    col_ws1 = ActiveSheet.Range(Cells(1, Columns.Count), Cells(1, Columns.Count)).End(xlToLeft).Column    Else    row_ws1 = 1    col_ws1 = 1    End If    ws1.Range(Cells(row_ws1, 1), Cells(row_ws1 + row_no - 1, col_no)) = arr_ws    Next objFileEnd SubOn Wed, Jan 27, 2016 at 2:19 PM, Sandeep Chhajer  wrote:  Hi Devendra, Thanks for your reply. The problem is the headers are not same in all the files. But some column headers are same in all the workbooks.So,  my requiremnt is a Macro which can search the column with that particular header name (common accross All the workbooks and paste it.Regards,



-- 
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$$ Client in Topeka, KS need PeopleSoft Functional Consultant

[MADHU-GenuineItLlc]

Hi Partner,

Hope you are doing great …

Please send resume to ma...@genuineitllc.com



*Job Title:** PeopleSoft Functional Consultant*

*Duration: **10 + months*

*Location: **Topeka, KS*



JD:-

Candidate must have supported an *insurance client with PeopleSoft
application version 8.4/9.x with accounting background.*

This is a support engagement and the consultant should be able to take up
related tasks, and may require to support during outside office hours



*Must have skills*

· Modules – PeopleSoft Financials  (*GL , AP Functional
 Knowledge)*

· Experience as a functional application specialist - ability to
support, configure  & co-ordinate independently for applications in
production

· Solid understanding and work experience as a PeopleSoft
functional consultant

· Minimum of five years current, hands-on PeopleSoft experience

· Experience working on GL and Account Payables areas

· Good to have experience with PeopleTools 8.5x

· *PeopleSoft Financials Management preferable version 8.4 /9.x*

· Good communication and written skills.



*Nice to have skills*

· Builds confidence and trust with customers

· Displays effective analytical skills

· Consistently produces clear, concise status reports



---

*With Warm Regards *

*Madhu Roy*

GenuineIT LLC *Certified Women Owned Business*

Making IT Happen…

Ph: 832 539 3475

Fax: 281-605-5969

*E-Mail* :ma...@genuineitllc.com

*Gmail *: *nandimadhu74 , madhu.genuineitllc*

*YM  *: madhu_recruiter



Looking ahead to work with you for better Business prospects



According to Bill S.1618 Title III passed by the 105th US Congress, this
message is not considered as "Spam" as we have included the contact
information. If you wish to be removed from our mailing list, please
respond with "remove" in the subject field. We apologize for any
inconvenience caused.

Please consider the environment before printing this e-mail

-- 
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$$ Sampling - Please check

[Paul Schreiner]

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,0this 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,0doesn'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 asthere are 2-3 
conditions on basis of which sampling should be done. It should be done for 4 
calls per month per type (column Afor 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.


 
  

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

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