• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Search

Mat_W

New Member
Hi

I have a worksheet with a large amount of sales data based on delivery dates. I want to search the data based on the delivery month so it displays in another worksheet. I'm struggling to find the simplest/best solution. I want all the data in one row and is a mix of numbers and text so not Pivot. I thought Index/Match would work but my brain has now fried....

Any help or guidance would be much appreciated.

I've attached a small sample file to help show what I'm trying to do. Hope that's not too vague.

Many thanks in advance.

Mat
 

Attachments

  • example.xlsx
    10.8 KB · Views: 9
Mat_W
Could You use basic 'Filter' to get Your needed rows to show?
It no need none formulas.
... and if really needs something to other sheet, then You could select all and do copy&paste.
 
Advanced Filter Copy would do the job.

Set up criteria range on Delivery Log (Ex: J2:J3). Make sure J2 holds exact match for column header "Delivery Month", and J3 =D1

Use macro recorder. Go to Data tab and set up Advanced Filter Copy as below.
59712

Hit Ok. Then go into macro recorder and edit resulting code to something like below to make it a bit more dynamic.
Code:
    Sheets("Data").Range("C2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("J2:J3"), CopyToRange:=Range("C3:E3"), Unique:=False

Then copy that into Worksheet module for "Delivery Log" and add conditions for executing the code (i.e. when D1 value is changed).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandle:
Application.EnableEvents = False
If Not Intersect(Target, [D1]) Is Nothing Then
    Sheets("Data").Range("C2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("J2:J3"), CopyToRange:=Range("C3:E3"), Unique:=False
End If
ErrHandle:
Application.EnableEvents = True
End Sub

See attached sample.
 

Attachments

  • example_Adv_Filter.xlsm
    19.8 KB · Views: 3
Here's formula version of the solution using INDEX/AGGREGATE combo.

You can do something similar with INDEX/SMALL(IF()) array formula as well.

Note that there are bunch of named ranges set up.
1. Named range(s) prefixed with d is Data range
2. Named range(s) prefixed with c is Criteria range
3. k_value is for giving kth value to SMALL() (i.e. Aggregate(15,6,)) formula.
4. Offset_Value is fixed value to offset ROW() calculation, based on where the data range starts.

See attached.
 

Attachments

  • example_Formula_Extract.xlsb
    13 KB · Views: 5
Advanced Filter Copy would do the job.

Set up criteria range on Delivery Log (Ex: J2:J3). Make sure J2 holds exact match for column header "Delivery Month", and J3 =D1

Use macro recorder. Go to Data tab and set up Advanced Filter Copy as below.
View attachment 59712

Hit Ok. Then go into macro recorder and edit resulting code to something like below to make it a bit more dynamic.
Code:
    Sheets("Data").Range("C2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("J2:J3"), CopyToRange:=Range("C3:E3"), Unique:=False

Then copy that into Worksheet module for "Delivery Log" and add conditions for executing the code (i.e. when D1 value is changed).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandle:
Application.EnableEvents = False
If Not Intersect(Target, [D1]) Is Nothing Then
    Sheets("Data").Range("C2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("J2:J3"), CopyToRange:=Range("C3:E3"), Unique:=False
End If
ErrHandle:
Application.EnableEvents = True
End Sub

See attached sample.

Thanks so much Chihiro.. that worked a treat and saved me a few grey hairs. One more thing if possible.. How do I change to code to paste values only?

Thank very much.

Mat
 
You can't with Advanced Filter.

I typically use code to set format after import. Or just format the source to match desired output.
 
Hopefully there is not too much longer to wait before you can have
= FILTER( Table1[[Customer]:[Model]], Table1[Delivery Month]=DeliveryMonth )
 
Back
Top