How can I get a sample of professional CV?

Get a random sample

• QUESTION: Wow! I can't believe it when I run the macro it really worked you are really amazing, but I have three more request, 1. I noticed that when I run the macro the sequence of the data in sheet 1 changed, would it be possible to retain the original sequence of the data in sheet 1? 2. I want to set the first row as the header for each column in each sheet. When the macro run, I want the header in sheet 1 to be copied in sheet 2. 3. For sheet 1 I want to change the location of the Analyst name, from Column A, I want it in Column G. For sheet 3 the Analyst Name is located in Column A, the sample size is located in Column B I want the sample size to be in Column C. Thank you very much!!! Answer JN, I believe I have made all the changes you requested: My tests confirmed that the code performed as expected based on your stated requests. Sub SampleData_MultipleAnalyst() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, n As Long Dim r1a As Range, r1b As Range Dim lastcol As Long, lastrow As Long Dim sh3 As Worksheet, i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") Set sh3 = Worksheets("Sheet3") lastcol = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column lastrow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row sh1.Cells(1, lastcol + 1).Resize(1, 3).Value = Array("HeaderA", "HeaderB", "HeaderC") Set r1 = sh1.Range(sh1.Cells(2, lastcol + 1), sh1.Cells(lastrow, lastcol + 1)) r1.Formula = "=row()" r1.Offset(0, 1).Formula = "=rand()" r1.Resize(, 2).Formula = r1.Resize(, 2).Value Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 2)) r1a.Sort Key1:=r1.Offset(0, 1).Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False With r1.Offset(0, 2) .Formula = "=IF(COUNTIF($G$2:G2,G2)>VLOOKUP(G2," & sh3.Range("$A:$C").Address(1, 1, xlA1, True) & _ ",3,FALSE),NA(),""copy"")" .Formula = .Value End With Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 3)) r1a.AutoFilter Field:=lastcol + 3, Criteria1:="copy" sh1.AutoFilter.Range.Copy sh2.Range("A1") sh2.Range(r1.Resize(, 3).Address).EntireColumn.Delete 'Set r1b = r1a.Resize(n, lastcol) 'r1b.Copy sh2.Range("A1") r1a.AutoFilter r1a.Sort Key1:=r1.Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False r1.Resize(, 3).EntireColumn.Delete End Sub When I first run the macro it did not notice that the macro is copying all the data in sheet 1 instead of just getting samples needed... In sheet 1 I put the all the data,in Column G is the Analyst Name.... In Sheet 3 the Analyst Name is in Column A and the required sample is in Column C. Am I doing something wrong?Is there something I first need to do before running the macro? If I want to change the location of the Analyst Name in Sheet 1, and I also need to change the location of the Analyst Name and Sample size in sheet 3 can you please pinpoint it on the macro which part of the macro I will change? Without compromising the all the macro itself? Thank you!!! QUESTION: I have a worksheet with an Analyst name in it from 1 to 10...I want to get a random sample among them but I want to be the one to determine how many samples will be taken. For Example... From A1 to A10 the name in each cell is Jane.... I want to have a 5 random samples, but what i also want is to copy the entire row for each sample and the export it to another sheet. How will I do this..using VBA. Thank you very much. ANSWER: Jn, this worked for me. Change the value assigned to the variable "n" to how many samples that will be taken. This routine assumes that all the data in the cells are constant values (no formulas). Sub SampleData() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, n As Long Dim r1a As Range, r1b As Range Dim lastcol As Long, lastrow As Long n = 5 ' sh1.Cells(1, i).Value = "Header" & i Next Set r1 = sh1.Range(sh1.Cells(2, lastcol + 1), sh1.Cells(lastrow, lastcol + 1)) r1.Formula = "=row()" r1.Offset(0, 1).Formula = "=rand()" r1.Resize(, 2).Formula = r1.Resize(, 2).Value Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 2)) r1a.Sort Key1:=r1.Offset(0, 1).Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False With r1.Offset(0, 2) .Formula = "=IF(COUNTIF($A$2:A2,A2)>VLOOKUP(A2," & sh3.Range("$A:$B").Address(1, 1, xlA1, True) & _ ",2,FALSE),NA(),""copy"")" .Formula = .Value End With Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 3)) r1a.AutoFilter Field:=lastcol + 3, Criteria1:="copy" sh1.AutoFilter.Range.Copy sh2.Range("A1") sh2.Range(r1.Resize(, 3).Address).EntireColumn.Delete sh2.Rows(1).EntireRow.Delete 'Set r1b = r1a.Resize(n, lastcol) 'r1b.Copy sh2.Range("A1") r1a.AutoFilter r1a.Sort Key1:=r1.Offset(0, 1).Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False r1.Resize(, 3).EntireColumn.Delete sh1.Rows(1).EntireRow.Delete End Sub ---------- FOLLOW-UP ---------- QUESTION: Wow! I can't believe it when I run the macro it really worked you are really amazing, but I have three more request, 1. I noticed that when I run the macro the sequence of the data in sheet 1 changed, would it be possible to retain the original sequence of the data in sheet 1? 2. I want to set the first row as the header for each column in each sheet. When the macro run, I want the header in sheet 1 to be copied in sheet 2. 3. For sheet 1 I want to change the location of the Analyst name, from Column A, I want it in Column G. For sheet 3 the Analyst Name is located in Column A, the sample size is located in Column B I want the sample size to be in Column C. Thank you very much!!!

• Answer:

  Jn, when I tested the code previously, I had the same problem because I had not changed the formula that determines what rows to copy properly. Once I fixed it, it worked perfectly for me. I had separate countif formulas to verify that it copied exactly the specified subsets/samples. So if everything is where you stated it is, it should work. The identification of the records is done with this formula .Formula = "=IF(COUNTIF($G$2:G2,G2)>VLOOKUP(G2," & sh3.Range("$A:$C").Address(1, 1, xlA1, True) & _ ",3,FALSE),NA(),""copy"")" so in row 2 that formula would look like =If(countif($G$2:G2,G2)>Vlookup(G2,Sheet3!$A:$C,3,False),NA(),"Copy") What that says is if the count of records, looking in column G (analyst name) up to that row and starting in Row 2 (of sheet1) is greater than the specified number of records to be sample, then display the error #N/A. Otherwise display the"Copy" You can see that the Vlookup formula is looking up the number of records to sample on Sheet3 by finding the analyst name in column A and then returning the value in column C as the number of records to sample for that Analyst Name. At the time the formula is entered, the data in Sheet1 has been sorted by the random number generated - so it is "Shuffled" data at that point. As the formula is entered down the column, it continue to identify each row as to whether it should be in the sample or not. If you are having troubles, then make a copy of your workbook and put in the macro and change it as I show below Sub SampleData_MultipleAnalyst() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, n As Long Dim r1a As Range, r1b As Range Dim lastcol As Long, lastrow As Long Dim sh3 As Worksheet, i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") Set sh3 = Worksheets("Sheet3") lastcol = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column lastrow = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row sh1.Cells(1, lastcol + 1).Resize(1, 3).Value = Array("HeaderA", "HeaderB", "HeaderC") Set r1 = sh1.Range(sh1.Cells(2, lastcol + 1), sh1.Cells(lastrow, lastcol + 1)) r1.Formula = "=row()" r1.Offset(0, 1).Formula = "=rand()" r1.Resize(, 2).Formula = r1.Resize(, 2).Value Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 2)) r1a.Sort Key1:=r1.Offset(0, 1).Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False With r1.Offset(0, 2) .Formula = "=IF(COUNTIF($G$2:G2,G2)>VLOOKUP(G2," & sh3.Range("$A:$C").Address(1, 1, xlA1, True) & _ ",3,FALSE),NA(),""copy"")" .Formula = .Value End With Exit Sub ' .Formula = "=IF(COUNTIF($G$2:G2,G2)>VLOOKUP(G2," & sh3.Range("$A:$C").Address(1, 1, xlA1, True) & _ ",3,FALSE),NA(),""copy"")" .Formula = .Value End With Set r1a = sh1.Range(sh1.Cells(1, 1), sh1.Cells(lastrow, lastcol + 3)) r1a.AutoFilter Field:=lastcol + 3, Criteria1:="copy" sh1.AutoFilter.Range.Copy sh2.Range("A1") sh2.Range(r1.Resize(, 3).Address).EntireColumn.Delete 'Set r1b = r1a.Resize(n, lastcol) 'r1b.Copy sh2.Range("A1") r1a.AutoFilter r1a.Sort Key1:=r1.Resize(1, 1), Order1:=xlAscending, Header:=xlYes, MatchCase:=False r1.Resize(, 3).EntireColumn.Delete End Sub