VBA - Copy All Unique Values Depending on the Date in an Adjacent Cell

[justinua]

I'm trying to take all unique values in a column and paste those in another cell. I found a macro that is able to do that successfully; however I'd like to add another layer to this. I would only like to copy the unique values if the adjacent cell contains a date within a specified date range.

Here is an example of the data I have now:

Code	Date
3104	4/18/2015
3599	4/23/2015
4004	4/15/2015
7158	4/25/2015
7158	4/23/2015
7158	4/23/2015

<tbody>
</tbody>

The macro I have now takes 3104, 3599, 4004 and one instance of 7158 from column A and copies it to a column on another worksheet. How do I make it so that it only takes all unique values between the dates of 4/21/15-4/24/15?

Here's the code I have so far:

Sub CreateUniqueList()Dim lastrow As Long


lastrow = Sheets("JL Data").Cells(Rows.Count, "A").End(xlUp).Row
    
    Sheets("JL Data").Range("A2:A" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets("SUMMARY JL").Range("A2"), _
    Unique:=True
     
End Sub

Thanks for your help!

 

[DocAElstein]

I'm trying to take all unique values in a column and paste those in another cell. I found a macro that is able to do that successfully; however I'd like to add another layer to this. I would only like to copy the unique values if the adjacent cell contains a date within a specified date range.
.......

. Hi
. This sounds on the face of it very simple, and there are probably infinite ways to do this with VBA or maybe even with a Formula.
. It makes it easier to answer if you provide a BEFORE .. somehting like you did , but maybe a few more bits of data to check that all scenarios are covered.
. AND then provide a AFTER showing exactly how you want your output to look based on your actual sample data in the BEFORE

Alan

 

[justinua]

. Hi
. This sounds on the face of it very simple, and there are probably infinite ways to do this with VBA or maybe even with a Formula.
. It makes it easier to answer if you provide a BEFORE .. somehting like you did , but maybe a few more bits of data to check that all scenarios are covered.
. AND then provide a AFTER showing exactly how you want your output to look based on your actual sample data in the BEFORE

Alan

Thanks for your response. Let me know if this helps clarify what I'm looking for:

Let's say I have a table consisting of the following ("Code" is Column A and "Date" is Column B):

Code	Date
3104	4/18/2015
3599	4/23/2015
4004	4/15/2015
7158	4/25/2015
7158	4/23/2015
7158	4/23/2015

<tbody>
</tbody>

I would like a query where if I said that I would like to get all unique values in the 'Code' column where the date range in the 'Date' is between a & b (for this example, let's say 4/21/15-4/25/15), then those unique values in the Code column meeting the criteria (3599, 7158) would get pasted into a column on another worksheet (The worksheet it will copy over to is called "Summary JL" and it will paste to cell "A2"). The final result should be:

Code
3599
7159


The date range criteria will vary depending on whatever date has been entered into assigned cells (the cell containing the start date will be A1, and the cell containing the end date will be B2)


Let me know if you need me to provide any other information. Thanks again for your help!

 

[DocAElstein]

...... Let me know if this helps clarify what I'm looking for:


I would like a query where if I said that I would like to get all unique values in the 'Code' column where the date range in the 'Date' is between a & b (for this example, let's say 4/21/15-4/25/15), then those unique values in the Code column meeting the criteria (3599, 7158) would get pasted into a column on another worksheet (The worksheet it will copy over to is called "Summary JL" and it will paste to cell "A2"). The final result should be:

Code
3599
7159


The date range criteria will vary depending on whatever date has been entered into assigned cells (the cell containing the start date will be A1, and the cell containing the end date will be B2)


Let me know if you need me to provide any other information........

I think I get the general point.. but
. 1) ( Obviously the 9 is a typo and should be 8? )

. 2a) are you sure A1 and B2 very unusual to have criteria like that in different columns and different rows
. 2b) In which sheet are those 2 dates.

. 3) When you answer, a few more rows would be useful, for the Before and After

 

[DocAElstein]

Hi,
. I drop off what I have for now and you may want to get back to me later….

. 1 ) Solution 1 VBA Code.

. This is much more complicated than it needs to be because
. a ) I have tried to keep it flexible for now, not being 100% sure what you want.
. b ) The easiest VBA Method, ( using the VBA Advanced Filter method with a Criteria Range ) I am trying to learn just now, so may drop off that version later.—

. So you start with this:

Using Excel 2007

-	A​	B​	C​	D​
1​	Code	Date	startdate	stopdate
2​	3104​	4/18/2015	4/21/2015	4/25/2015
3​	3599​	4/23/2015
4​	4004​	4/15/2015
5​	7158​	4/25/2015
6​	7158​	4/23/2015
7​	7158​	4/23/2015
8​

JL Data

… and initially your Worksheet “SUMMARY JL” is empty…

. You run this code:

[color=blue]Sub[/color] justinuaapoArrayMethod()
[color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] TheEnd [color=lightgreen]'If anything goes wrong go to the End instead of crashing. Useful to Edit out for Debuging. Note this is for most (unexpected) errors occuring in the program we do have others...https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo    http://excelmatters.com/2015/03/17/on-error-wtf/[/color]
Application.ScreenUpdating = [color=blue]False[/color] [color=lightgreen]'Not necerssary but speeds things up a bit, by turning screen updating off. Good to edit out for Debuging Purposes.[/color]
[color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("JL data") [color=lightgreen]'set sheet name - Give abbreviation for "unfiltered" sheet in ThisWorkbook all Objects, Properties and Methods of [color=blue]Object[/color] Worksheet obtainable to view in the intellisense given after typing . Dot[/color]
[color=blue]Dim[/color] wks2 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks2 = ThisWorkbook.Worksheets("SUMMARY JL")
[color=blue]Dim[/color] startDate [color=blue]As[/color] Date, stopDate [color=blue]As[/color] Date [color=lightgreen]'Can be dimensioned differently and still works[/color]
[color=blue]Let[/color] startDate = wks1.Range("C2").Value: [color=blue]Let[/color] stopDate = wks1.Range("D2").Value [color=lightgreen]'Bring in start and stop date[/color]
            [color=lightgreen]'                '1) Optional Start Bit to Delete Sheets / Tabs------------[/color]
            [color=lightgreen]'                Application.DisplayAlerts = False 'Prevents being asked everytime if you really want to delete the Workbook[/color]
            [color=lightgreen]'                Dim ws As Worksheet 'Tempory worksheet name. ws now has Methods and Properties of Worksheets obtained with typing . dot after it![/color]
            [color=lightgreen]'                For Each ws In ActiveWorkbook.Worksheets 'We could alternatively use something like ThisWorkbook, Workbooks("Itsname.xlxm")[/color]
            [color=lightgreen]'                    If ws.Name <> "ASheetToKeep" And ws.Name <> wks1.Name And ws.Name <> "SUMMARY JL" And InStr(ws.Name, "JL") = 0 And InStr(ws.Name, "VBA") = 0 Then  'Check that Worksheet name is not that of any that you want (Name property here returns name without .xlsm bit on end)[/color]
            [color=lightgreen]'                    ws.Delete[/color]
            [color=lightgreen]'                    Else 'Presumably then the worksheet name is That of the first sheet or any you wish to keep[/color]
            [color=lightgreen]'                    ' do nothing (Don't delete it!)[/color]
            [color=lightgreen]'                    End If[/color]
            [color=lightgreen]'                Next ws[/color]
            [color=lightgreen]'                Application.DisplayAlerts = True 'Turn it back on[/color]
            [color=lightgreen]'                '---End Bit to delete any Sheets / Tabs--------------------[/color]
            [color=lightgreen]'[/color]
' 2) Capture data, get a full Array of LookUpColumn------------------------------------
[color=blue]Dim[/color] vLkUpc [color=blue]As[/color] Long: [color=blue]Let[/color] vLkUpc = 1 [color=lightgreen]'set column number 'Column where search criteria for filtering is. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
[color=blue]Dim[/color] xx() [color=blue]As[/color] Variant: [color=blue]Let[/color] xx() = wks1.Evaluate("" & wks1.Range("A1").Address & "").CurrentRegion.Value [color=lightgreen]'One liner VBA allowed capture of range values to a dynamic Array.  (Evaluation of an address returns a Range Property...https://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/[/color]
[color=blue]Dim[/color] x() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Dynamic one dimensional array for unique headings....[/color]
    [color=lightgreen]' can from spreadsheet column offset by 1 and so then resized to row count -1....x = Application.Transpose(.Columns(1).Offset(1, 0).Resize(.Rows.Count - 1))... or just[/color]
[color=blue]Let[/color] x() = Application.WorksheetFunction.Index(xx(), 0, vLkUpc) [color=lightgreen]'Returns format type (1,1) (2,1) (3,1) (4,1) >> Index Function with second argument (row co - ordinate) set to 0 will return the entire row given by first argument ( row - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)  http://www.excelforum.com/excel-new-users-basics/1080634-vba-1-dimensional-horizontal-and-vertical-array-conventions-ha-1-2-3-4-a.html[/color]
[color=blue]Let[/color] x() = Application.WorksheetFunction.Transpose(x) [color=lightgreen]'working on 2 dimensional array of one column, conveniently by convenience returns 'Returns format type (1) (2) (3) (4) , a one dimension "psuedo" horizontal Array[/color]
    [color=blue]Dim[/color] strRows [color=blue]As[/color] [color=blue]String[/color]
[color=lightgreen]' End 2) With [A1].CurrentRegion-------------------------------------------------------[/color]
       
[color=lightgreen]' 3) Use Dictionary to get Unique values (keys) of VlookUp Column----------------------------------------[/color]
[color=lightgreen]'  For "Early binding"--requires library reference to MS Scripting Runtime - Tools>>References>>scrolldown and check the box next to Microsoft Scripting Runtime[/color]
[color=lightgreen]'  ..Or crashes at next line.....---[/color]
[color=lightgreen]'Dim dicLookupTable As Scripting.Dictionary 'Data held with a unique "Key"or Part Number.[/color]
[color=lightgreen]'Set dicLookupTable = New Scripting.Dictionary[/color]
[color=lightgreen]' The next two lines are an alternative called Late binding. (But note some Dictionary methods and properties( Such as at the end ####) will not work with it  - in those cases Early Binging must be used.[/color]
    [color=blue]Dim[/color] dicLookupTable [color=blue]As[/color] Object
    [color=blue]Set[/color] dicLookupTable = CreateObject("Scripting.Dictionary")
[color=lightgreen]' Late Binding is better when sharing files as I am here. Early Binding has the advantage that Excel intellisense[/color]
[color=lightgreen]' will then work for the Microsoft Scripüting Runtime stuff and give you suggestions after you type the .dot thing[/color]
    dicLookupTable.CompareMode = vbTextCompare [color=lightgreen]'Not quite sure wot this does yet[/color]
       
[color=blue]Dim[/color] i [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Loop Bound Variable (Count)  http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html[/color]
 [color=lightgreen]'   The method =.Item() works in a nice way that allows us to make unique keys without assigning items    http://www.snb-vba.eu/VBA_Dictionary_en.html[/color]
 [color=lightgreen]'   -- Usually the method .Item() is used to assign an item of some unique key to a vaiable.  z = dicLookupTable.Item(x(i).  If the key does not exist then it is  made...convenient   ehh?--- ( and no value will be given to the variable )[/color]
    [color=blue]Dim[/color] z [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Subtle dimensioning reason... in method =.Item() z becomes empty "" "No value is given??" snb??  Post #12 http://www.excelforum.com/excel-programming-vba-macros/1083899-copy-and-paste-entire-row-to-second-sheet-based-on-cell-value.html[/color]
        [color=blue]For[/color] i = [color=blue]LBound[/color](x()) + 1 [color=blue]To[/color] [color=blue]UBound[/color](x()) [color=lightgreen]'Start looking down column at 2 so as not to get the heading is first dic item[/color]
            z = dicLookupTable.Item(x(i)) [color=lightgreen]'You will not see anything here: Post #7   http://www.excelforum.com/excel-programming-vba-macros/1083899-copy-and-paste-entire-row-to-second-sheet-based-on-cell-value.html[/color]
        [color=blue]Next[/color] i
    [color=blue]Dim[/color] zz() [color=blue]As[/color] Variant: [color=blue]Let[/color] zz() = dicLookupTable.keys [color=lightgreen]' The unique keys are put into a 1 Dimensional Dynamic array called zz. Probably again the variant is needed as it sees the Dictionarry object initially, the usual "one liner" type assignment[/color]
            [color=lightgreen]'    Dim rResults() As Variant: Let rResults() = dicLookupTable.Items() 'Extra line helpful to examine items in watch window... as dicLookupTable in watch window just the keys!! and a limited number thereof  http://www.mrexcel.com/forum/excel-questions/832103-using-microsoft-scripting-runtime-dictionary-store-then-retrieve-range-objects-2.html[/color]
[color=lightgreen]'End of Part 3 initial set up Of MRSD and use of keys to get unique values ---------------------[/color]
           
[color=lightgreen]'  4) Array method to do the Business..[/color]
[color=blue]Dim[/color] arrResults() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Array for output, will be assigned values in a loop we can dimension its content values and in next line the size[/color]
[color=blue]ReDim[/color] arrResults(1 [color=blue]To[/color] [color=blue]UBound[/color](x(), 1) - 1, 1 [color=blue]To[/color] 1) [color=lightgreen]'Dim size of output to maximum possible which is row length of input array -1 (We have here a non.Dynamic Array)[/color]
[color=blue]Dim[/color] outr [color=blue]As[/color] Long: [color=blue]Let[/color] outr = 0 [color=lightgreen]'A "row" to be incremented by every addition to output Array[/color]
[color=blue]Dim[/color] ii [color=blue]As[/color] [color=blue]Long[/color], iii [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Loop Bound (Count) variables.  MAIN LOOP FOR EACH UNIQUE-----------------------[/color]
    [color=blue]For[/color] ii = [color=blue]LBound[/color](zz()) [color=blue]To[/color] [color=blue]UBound[/color](zz()) [color=lightgreen]'Setup outer loop. This will be used to loop through the unique values.[/color]
        [color=lightgreen]'make a string of numbers to give a "row" indicie for where all occurances of the current Unique are[/color]
        [color=blue]For[/color] iii = [color=blue]LBound[/color](x()) [color=blue]To[/color] [color=blue]UBound[/color](x()) [color=lightgreen]'Lower Bound by me is VLookUpColumnHeading, but no prob, I do not have this in my unique list as I looped there from 2 ##  and **[/color]
            [color=blue]If[/color] x(iii) = zz(ii) [color=blue]Then[/color] strRows = strRows & " " & iii [color=lightgreen]' + 1-- the usual +/-1 fiddle bit ..**  my x() column has as first the heading[/color]
        [color=blue]Next[/color] iii
    
    [color=blue]Dim[/color] rws [color=blue]As[/color] Variant: [color=blue]Let[/color] rws = Trim(strRows): [color=blue]Let[/color] rws = Split(rws, " ") [color=lightgreen]'Finally rws becomes a 1 dimension1 "Psuedo" horizontal Array of the selected row inicia. " " could be left out as a space is the default[/color]
           
    [color=blue]Dim[/color] rrws [color=blue]As[/color] Long: [color=lightgreen]'Loop Bound Variable Count for looping throught unique rows' Subtle unusual stuff here: rws is set each time to looping as many times, but any check for criteria must be done with the actual indicie value within that array!![/color]
        [color=blue]For[/color] rrws = [color=blue]LBound[/color](rws) [color=blue]To[/color] [color=blue]UBound[/color](rws) [color=blue]Step[/color] 1 [color=lightgreen]'We go throug all "rows" in the rws Array for this Unique[/color]
 
            [color=lightgreen]'The next line is the important criteria check. The "On Error / Match Pair bit" is a "bodge" to only get one unique value as is the unusual case of this program[/color]
            [color=blue]If[/color] xx(rws(rrws), 2) >= startDate And xx(rws(rrws), 2) <= stopDate [color=blue]Or[/color] xx(rws(rrws), 2) = startDate [color=blue]Or[/color] xx(rws(rrws), 2) = stopDate [color=blue]Then[/color]
                [color=blue]On[/color] [color=blue]Error[/color] [color=blue]Resume[/color] [color=blue]Next[/color] [color=lightgreen]''This error handler is for the predicted error if no match, so below line errors in which case we go on at the line just after the next[/color]
                [color=blue]Dim[/color] temp [color=blue]As[/color] String: [color=blue]Let[/color] temp = xx(rws(rrws), 1) [color=lightgreen]'Have to do this intermediate step as criteria to search for in Match only accepts something like a string in it's first argument.[/color]
                [color=blue]If[/color] Application.WorksheetFunction.Match(temp, arrResults(), 0) = -1234 [color=blue]Then[/color] [color=lightgreen]'provided something is there, we check to see if that value is already in our vLook Up Array by looking to see for a match. If it is not there then, the predicted error occurs.......This is part of "Match On Error Pair" trick for getting Unique values. (See here http://www.excelforum.com/excel-new-users-basics/1072093-match-with-on-error-on-error-resume-next-works-on-error-goto-only-works-once-err-clear.html  ). Otherwisde it does not crash as it gets a Long Number, ( the indicie going down the row, 1 , 2 , 3 or 4 etc. ) - But it will not get -1234 ! - it accepts thogh syntaxly this as OK, - most people write 0 here.[/color]
            [color=blue]Let[/color] outr = outr + 1 [color=lightgreen]'If the above errored we had no unique value yet, so we can put one in[/color]
            [color=blue]Let[/color] arrResults(outr, 1) = xx(rws(rrws), 1)
                [color=blue]Else[/color] [color=lightgreen]'We already had a Unique that met date criteria so we do not need another[/color]
                [color=blue]End[/color] [color=blue]If[/color] [color=lightgreen]'End of "On Error / Match Pair bit"[/color]
            [color=blue]Else[/color] [color=lightgreen]'Date criteria not met, so no entry in output Array[/color]
            [color=blue]End[/color] [color=blue]If[/color] [color=lightgreen]'End of main criteria check[/color]
                [color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] TheEnd [color=lightgreen]'We no longer expecting an error, so we switch back on the error handler as soon as possible for handling unexpected errors[/color]
        [color=blue]Next[/color] rrws [color=lightgreen]'This goes back to the next occurance "row" if there is one of the unique. For this particular program it is not rally needed[/color]
       
        [color=blue]Let[/color] strRows = "" [color=lightgreen]'Empty the String for collecting the row indicie of the next unique occurrances[/color]
    [color=blue]Next[/color] ii [color=lightgreen]' Continue to next unique value[/color]
 
[color=lightgreen]'   End 4)   MAIN LOOP FOR EACH UNIQUE---------------------------------------------------------------[/color]
 
[color=lightgreen]' 5) Output to SUMMARY sheet[/color]
[color=blue]Let[/color] wks2.Range("A1").Resize(1, 1).Value = Array("Code", "Date") [color=lightgreen]'A taypical step that looks cleverer then it is, I resize first cell to a range including all headings I want, and then VBA lets me assign the values in a (Heasding here)  Array to the cells in a simplw = step[/color]
[color=blue]Let[/color] wks2.Range("A2").Resize(UBound(arrResults(), 1), 1).Value = arrResults() [color=lightgreen]'Similat to the above just convenient to resize to size of Arrray i am actually outputing ( Assuming there are more than one value of the Uniques then I output empty values also, but that is useful as it actually clears those cells in case they had any in from a last run where there were more in the output Array[/color]
 
 
TheEnd: [color=lightgreen]'We come here on erroring rather than crashing. Anything that should be done before ending the macro should be done here, to make sure it will always be dine ecen if the code crashes![/color]
Application.ScreenUpdating = [color=blue]True[/color] [color=lightgreen]'Screen need to be turned back on or your screen will be "dead"!! Important to turn screen on here, incase anything goes wrong.[/color]
[color=blue]Set[/color] dicLookupTable = [color=blue]Nothing[/color]
    [color=lightgreen]'        'Err.Message 'Give Error message using property to give infomation from the Err Object which gets info Error stored when an error occurs..[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'justinuaapoArrayMethod()[/color]

………..
… then your Worksheet “SUMMARY JL” should change to look like this…

Using Excel 2007

-	A​
1​	Code
2​	3599
3​	7158
4​

SUMMARY JL

………………………………………………..

. 2 ) Formula solution

. You may like to consider a formula equivalent.

. For now I have left the formulas “opened up” and on the same sheet for clarity.. But modifying to get the results as per the VBA code is easy….

. So this would be a modified JL Data Worksheet initially


Using Excel 2007

-	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Code	Date	startdate	stopdate	Code	Code	Code	Code	Code4
2​	3104​	4/18/2015	4/21/2015	4/25/2015
3​	3599​	4/23/2015
4​	4004​	4/15/2015
5​	7158​	4/25/2015
6​	7158​	4/23/2015
7​	7158​	4/23/2015
8​

JL DataFormula

……………………………………….

. After putting in the formulas I give you below you get this

Using Excel 2007

-	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Code	Date	startdate	stopdate	Code	Code	Code	Code	Code4
2​	3104​	4/18/2015	4/21/2015	4/25/2015		3104​		3599​	3599​
3​	3599​	4/23/2015			3599​	3599​	3599​	7158​	7158​
4​	4004​	4/15/2015				4004​	7158​
5​	7158​	4/25/2015			7158​	7158​
6​	7158​	4/23/2015			7158​
7​	7158​	4/23/2015			7158​

JL DataFormula

…………………….

. I give you just the formulas in Row 2, as you drag them down as I explain after…

Excel 2007
	E	F	G	H	I
1	Code	Code	Code	Code	Code4
2		3104		3599	3599
JL DataFormula

Cell Formulas
Range	Formula
E2	{=IF(AND($B2>=$C$2,$B2<=$D$2),$A2,"")}
F2	{=IF(ISERROR(INDEX($A$2:$B$7, MATCH(0, COUNTIF($F$1:$F1,$A$2:$A$7), 0),1)),"",INDEX($A$2:$B$7, MATCH(0, COUNTIF($F$1:$F1,$A$2:$A$7), 0),1))}
G2	{=IF(ISERROR(INDEX($E$2:$E$7, MATCH(0, COUNTIF($G$1:$G1,$E$2:$E$7), 0),1)),"",INDEX($E$2:$E$7, MATCH(0, COUNTIF($G$1:$G1,$E$2:$E$7), 0),1))}
H2	{=IF(ISERROR(INDEX($G$2:$G$7, SMALL(IF(($G$2:$G$7)="", "", ROW($G$2:$G$7)-MIN(ROW($G$2:$G$7))+1), ROW(A1)),1)),"",INDEX($G$2:$G$7, SMALL(IF(($G$2:$G$7)="", "", ROW($G$2:$G$7)-MIN(ROW($G$2:$G$7))+1), ROW(A1)),1))}
I2	{=IFERROR(INDEX($G$2:$G$7, SMALL(IF(($G$2:$G$7)="", "", ROW($G$2:$G$7)-MIN(ROW($G$2:$G$7))+1), ROW(A1)),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

. ……………………….

. Notes on the formulas:

. 1) The formulas are of the “CSE” sort which are placed in one cell and dragged down …
. 2) To put each of these formulas in turn into the spreadsheet:
. 2a) copy ( Ctrl C ) the formula complete from the above table to clipboard without the { at the start and the } at the end
. 2b) select ( click in ) the cell where the formula should go
. 2c) Hit F2 or select ( click in ) the formula bar (To be on the safe side do both!! )
. 2d) paste in the formula from the clipboard ( Ctrl V ) ( check that the formula includes a = at the start )
. 2e) now you do the famous “CSE” . – That is, you hold down the keys Ctrl and Shift, and hit Enter.

. 3) select the entire range E2 to I2
. 4) click and hold on the tiny black square at the right bottom corner of this selection, and drag the entire selection down.


. Alan



P.s. 1. I can explain most of formulas in detail if you ask… a few may already be explained here I think, around Post # 6
http://www.mrexcel.com/forum/excel-...function-pull-column-b-populate-column-c.html
or here
https://app.box.com/s/hvpzlguex52ytd4wyd4vlh3ejno63uxo
or here
https://app.box.com/s/avk6paydbtame1hz7ge5zenh6ll1p35e

P.s.2. The best bits of the code version I stole from apo here
Copy and paste entire row to second sheet based on cell value

 

[justinua]

I appreciate you taking the time to put all of this together, but I haven't been able to get the VBA to work.

I'm hoping this will make what I am trying to do clearer: In my workbook, I have a list of codes (Column A Named "Code") and a list of corresponding dates in the adjacent cell (Column B named "Date"). What I would like to do is the following: If the date in B2 is between a specified date range (Start Date will be in cell C2, End date in Cell D2), then it should take the associated value in column A2 and copy it to another sheet (Sheet Name: "JL Summary"). If the date in B2 is not within that date range, then it should not copy the value in A2 over to the other sheet. This should be done for every single code in column A.

It looks like the formulas you provided in columns G, H, and I meet the criteria of what I described above , but I'm hoping for a little more clarification. I'm not sure what the G2:G7 represent in those formulas.

Also, is there an easier way that I can do this utilizing VBA? The code I've used below succeeds in copying over the unique values, but I've not been able to add in the date range criteria. Is there a way to utilize the "Criteria Range" within the advanced filter to set the date range?

[COLOR=#333333]Sub CreateUniqueList()
Dim lastrow As Long
[/COLOR][COLOR=#333333]lastrow = Sheets("JL Data").Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]Sheets("JL Data").Range("A2:A" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets("SUMMARY JL").Range("A2"), _
    Unique:=True
     [FONT=Verdana]End Sub[/FONT]

Thanks again for your help (and patience)!

 

[DocAElstein]

......
It looks like the formulas you provided in columns G, H, and I meet the criteria of what I described above , but I'm hoping for a little more clarification. I'm not sure what the G2:G7 represent in those formulas.
........

Hi,
. In General the 7 in all the formulas would represent your last row. This would need then to be increased for your actual data appropriately.

.
Alan

 

[DocAElstein]

. Hi,
. Another couple of solutions........or rather one solution and mention of possible further..

. My code ( Post #5 ) and formulas were a bit log winded, - As I said, I was not clear of some points, to which you never replied to confirm, from Post #4 ? ! ? ! .
. Anyway you appear in post # 6 to be going with my example anyway.. so

. An few things related to this and similar problems were discussed in detail, here Today
http://www.mrexcel.com/forum/excel-...ns-advancedfilter-method-calling-cavalry.html
.. Maybe worth a quick look...
........................

So:


. 1) A more simpler Code based on an input there from hiker95. Again it works on the my / your Post #4 Post #6 criteria and my screen shots Post #4...

[COLOR=blue]Sub[/COLOR] ExtractUniqueCodesBetweenDates3()
[COLOR=lightgreen]'This code came from this one: hiker95, 05/31/2015, ME857793   Post #3  http://www.mrexcel.com/forum/excel-questions/857793-help-criteriarange-visual-basic-applications-advancedfilter-method-calling-cavalry.html[/COLOR]
[COLOR=blue]Dim[/COLOR] wks1 [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wks1 = ThisWorkbook.Worksheets("JL data") [COLOR=lightgreen]'set sheet name - Give abbreviation for "unfiltered" sheet in ThisWorkbook all Objects, Properties and Methods of [COLOR=blue]Object[/COLOR] Worksheet obtainable to view in the intellisense given after typing . Dot[/COLOR]
[COLOR=blue]Dim[/COLOR] wks2 [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wks2 = ThisWorkbook.Worksheets("SUMMARY JL")
 
[COLOR=blue]Dim[/COLOR] dicOb [COLOR=blue]As[/COLOR] Object, z [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
[COLOR=blue]Set[/COLOR] dicOb = CreateObject("Scripting.Dictionary")
 
[COLOR=blue]Dim[/COLOR] o() [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
[COLOR=blue]Dim[/COLOR] lr [COLOR=blue]As[/COLOR] Long: [COLOR=blue]Let[/COLOR] lr = wks1.Cells.Find(what:="*", After:=wks1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [COLOR=lightgreen]'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/COLOR]
[COLOR=blue]Dim[/COLOR] r2 [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] [COLOR=lightgreen]'rows. Loop Bound Variable Count[/COLOR]
    [COLOR=blue]For[/COLOR] r2 = 2 [COLOR=blue]To[/COLOR] lr [COLOR=blue]Step[/COLOR] 1 [COLOR=lightgreen]' godown each row starting at row 2[/COLOR]
        [COLOR=blue]If[/COLOR] wks1.Cells(r2, 1) <> "" [COLOR=blue]Then[/COLOR] [COLOR=lightgreen]'Check that cell in column A is not empty[/COLOR]
            [COLOR=blue]If[/COLOR] wks1.Cells(r2, 2).Value >= [C2] And wks1.Cells(r2, 2) <= [D2] [COLOR=blue]Then[/COLOR] [COLOR=lightgreen]'Date criteria met, so[/COLOR]
            [COLOR=blue]Let[/COLOR] z = dicOb.Item(wks1.Cells(r2, 1).Value) [COLOR=lightgreen]''You will not see anything here: Post #7   http://www.excelforum.com/excel-programming-vba-macros/1083899-copy-and-paste-entire-row-to-second-sheet-based-on-cell-value.html[/COLOR]
[COLOR=lightgreen]'                If Not dicOb.Exists(wks1.Cells(r2, 1).Value) Then[/COLOR]
[COLOR=lightgreen]'                  dicOb.Add wks1.Cells(r2, 1).Value, wks1.Cells(r2, 1).Value[/COLOR]
[COLOR=lightgreen]'                End If[/COLOR]
            [COLOR=blue]Else[/COLOR] [COLOR=lightgreen]'Date criteria not met in column B. Do Nothing. Redundant code[/COLOR]
            [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
        [COLOR=blue]Else[/COLOR] [COLOR=lightgreen]'empty cell in column A. Do nothing. Redundant code[/COLOR]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR] r2 [COLOR=lightgreen]'go to next row[/COLOR]
    [COLOR=blue]Let[/COLOR] o() = dicOb.Keys [COLOR=lightgreen]'( 0 to 1 )[/COLOR]
    'Let o() = Array(o()) ' ( 0 to 0 ) variant which is a variant ( 0 to 1)
    o() = Application.Transpose(Array(dicOb.Keys)) [COLOR=lightgreen]'( 1 to 2 , 1 to 1 )[/COLOR]
[COLOR=lightgreen]' Output to SUMMARY sheet[/COLOR]
[COLOR=blue]Let[/COLOR] wks2.Range("A1").Resize(1, 1).Value = Array("Code", "Date") [COLOR=lightgreen]'A typical step that looks cleverer then it is, I resize first cell to a range including all headings I want, and then VBA lets me assign the values in a (Heasding here)  Array to the cells in a simplw = step[/COLOR]
[COLOR=blue]Let[/COLOR] wks2.Range("A2").Resize(UBound(o(), 1), 1).Value = o() [COLOR=lightgreen]'Similat to the above just convenient to resize to size of Arrray i am actually outputing ( Assuming there are more than one value of the Uniques then I output empty values also, but that is useful as it actually clears those cells in case they had any in from a last run where there were more in the output Array[/COLOR]
 
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

..............................
.2 )
. I am also able now to give a much simpler code based on your very first codes which you gave in Post #1 and Post #6. However, there are some important considerations of the date Formats . These were discussed in that thread today
http://www.mrexcel.com/forum/excel-...ns-advancedfilter-method-calling-cavalry.html
. I suggest you review again that Thread with that in mind. You may indeed be able to get enough detail from that thread to proceed further in writing a code yourself along the lines of your original.
. If you have difficulty in that , but would like me to write a code again for you along those lines then let me know.

Alan.