Extract list from source data based on advanced filter

[Kumar Shanmugam]

Hi,

I want to create a list of data extracted from base data using advanced filter. Please find attached the sample data.

 

[Nebu]

Hi:

Use the following code:

Sub Filter123()
Application.ScreenUpdating = False

i& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("A6:G" & i).ClearContents

With Sheet2.Range("A1:G1")
.CurrentRegion.AutoFilter Field:=2, Criteria1:=Sheet1.[A2]
.CurrentRegion.AutoFilter Field:=7, Criteria1:=">" & Sheet1.[B2]
End With

With Sheet2.AutoFilter.Range
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
    Sheet1.[A6].PasteSpecial
End With

Sheet1.Range("A6:G" & i).Sort key1:=Range("G6:G" & i), Order1:=xlDescending, Header:=xlNo

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Kumar Shanmugam]

Hi thanks for providing my reply on priority. I have checked your revert but unable to find the intended solution.

What I wanted is:

Once I select the zone and state, I will get the state buzz, Arrear and A%. This I have already worked out in the sample file.

Next it should also show the top branches with 2 criteria:

1.Branches havng business greater than average of all branches in that particular state excluding zero's or greater than specific value 100.
2.Branches havng arrear% greater than state arrear% (Note.Branches sorted in arrear% highest to lowest).

I have showed you the sample data. please refer the attached file.

 

[Nebu]

Hi:

Use the following code:

Sub Filter123()
Application.ScreenUpdating = False

i& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("A6:G" & i).ClearContents

With Sheet2.Range("A1:G1")
.CurrentRegion.AutoFilter Field:=2, Criteria1:=Sheet1.[A2]
.CurrentRegion.AutoFilter Field:=7, Criteria1:=">" & Sheet1.[B2]
.CurrentRegion.AutoFilter Field:=5, Criteria1:=">" & Sheet1.[C2]
End With

With Sheet2.AutoFilter.Range
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
    Sheet1.[A6].PasteSpecial
End With

Sheet1.Range("A6:G" & i).Sort key1:=Range("G6:G" & i), Order1:=xlDescending, Header:=xlNo

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks

 

[Kumar Shanmugam]

Hi I am not comfortable using VBA code. Can you please provide alternative solution other than VBA code. It will be great help for me.

 

[Marc L]

Hi !

So you can manually use an advanced filter
as you can read in Excel inner help as well on tutorial like
Introduction to Excel Advanced Filters …
​

 

[p45cal]

In the attached find the following:

1. In Sheet2, 3 more columns added to your data, being:
   • Column H shows that row's Avg buzz divided by the average of that row's zone/state Avg buzz. This shows values above 1 if a row's avg buzz value is above the state's average, and less than 1 if below.
   • Column I does the same for the A% column
   • Column J returns True if both column H and I are greater than 1. This will be used as a filter.
2. In Sheet1, some cells at N1: P2 being the criteria for the advanced filter. N2 and P2 link directly to cells A2 and B2, so when you make drop down choices in A2 and B2, the advance filter criteria update straightaway.
3. The Destination for the results of the advanced filter is cells A33:G33 (a horizontal set of headers you want in your results, in the order you want to see them in).

So all you do is apply the advanced filter manually each time as follows:
Open the Advanced filter dialogue box when Sheet1 is the active sheet:

and click OK.

There is a one line macro to do this which can be run by clicking the button in the vicinity of cell I2 of Sheet1.
So all you have to do is make choices in A2 and B2 and click the button, then look at the result as A33.

Independently of the above there is a pivot table on Sheet2 showing all the results for all zones/states which you can further filter as you wish. You can hide column U if it's too distracting.

 

[Kumar Shanmugam]

Hi thanks for the reply. I have tried to get solution. But I am not able to find the branch data. I am finding it tough. Please find attached the sample data. if the solution is without VBA code, it will be of immense help.

Also please check whether I have used any inefficient formula in the cell. Formula is processing little bit slowly not expected with less amount of data.

 

[bosco_yip]

Hi thanks for the reply. I have tried to get solution. But I am not able to find the branch data. I am finding it tough. Please find attached the sample data. if the solution is without VBA code, it will be of immense help.

Also please check whether I have used any inefficient formula in the cell. Formula is processing little bit slowly not expected with less amount of data.

1] In "Branch" column B10, formula copy down :

=IF(A10="","",INDEX(NDCC!C$2:C$1000,MATCH(C10,NDCC!D$2:D$1000,0)))

2] Don't use whole Column cell reference, which is 1 million cells per column and will cause your computer speed slow down.

=IF(A10="","",LARGE(IF(NDCC!B:B=$B$2,IF(NDCC!E:E>=$B$5,IF(NDCC!G:G>=$C$5,NDCC!D:D))),A10))

And, try to use enough cell range e.g. D$2:D$1000 instead of D:D

Regards
Bosco

 

[Kumar Shanmugam]

Hi thanks for your revert on Column cell reference. I will rectify the same.
Thank you very much on the branch formula it worked

 

[p45cal]

Hi thanks for the reply. I have tried to get solution. But I am not able to find the branch data. I am finding it tough. Please find attached the sample data. if the solution is without VBA code, it will be of immense help.

The solution you're attempting doesn't include any sort of Advanced Filter that I can see (as the title of this thread suggests it might)!
The formulae in the latest file you attached seem to take a very long time to calculate, but bosco_yip has adressed that.

BUT… there is a problem with those formulae; they are likely to mix up the rows of data. Below is a screenshot of the results of the formulae, and below that a screenshot of the source data. I have circled some of the anomalies.

There might, at some point, also be a problem with the formula in column B (msg#9) which looks up the value in the cell to the right of it, in the Business column of the NDCC sheet. If there are multiple similar values in that Business column, the MATCH might find the wrong one (if the first one is not the right one).

Please find attached the sample data. if the solution is without VBA code, it will be of immense help.

My offering contained one line of code… to help; you don't need it. Delete it. You can do it all manually as I showed and gave instructions for in msg#7.

Having said that, my offering was one which used Advanced Filter, and if you're not going to automate that with code, then it has to be a 2 step process.

I will look for a more robust formula-only solution. (Actually, I've improved on the pivot table solution I mentioned earlier. The only glitch with that is graphing the result; when I plot the second series as a line chart on the secondary y-axis, it all looks fine and changes accordingly when I choose different Sates, but when I make a choice of filter which produces no data, the chart plots nothing (fine) but when I once again choose valid filters, I lose the secondary y-axis and the line chart becomes a column chart again… grrr.)

 

[p45cal]

OK, quick and dirty solution by only tweaking your formulae: In cell B10 array-enter this formula:

=IF($A10="","",INDEX(NDCC!C$1:C$1000,LARGE(IF(NDCC!$B$2:$B$500=$B$2,IF(NDCC!$E$2:$E$500>=$B$5,IF(NDCC!$G$2:$G$500>=$C$5,ROW(NDCC!D$2:D$500)))),$A10)))

Then copy down and across, re-format cells as they were.

Later, if I get time, will look into streamlining this (and adding a chart that will update by itself), if someone else doesn't beat me to it.

 

[Kumar Shanmugam]

Ok thank you very much for reporting anomalies.
Basically my objective is to make a dashboard. If there is a change is base data (Sheet2), automatically Sheet 1 summary data changes.
I am still not a pro in using macro. Hence I avoided it.
Yes using advanced filter is the solution. but If there is a change is base data (Sheet2), automatically Sheet 1 summary data does not change. I have to manually use advanced filter each time. ( No Macross)