Solved

VBA to filter multiple items

Posted on 2013-11-21
32
264 Views
Last Modified: 2013-12-03
In my Sheet 1 in column 'N' I have hundreds of refs. In a hidden sheet called Static I have a Table [see example below]

So, for all items in the table below where Col A is CA02 I need to copy any row with CAEU, CALS, CAOT etc etc  from Sheet 1 to sheet10

I will then need to loop this so that I can do the same for CA02 then CA03 etc etc Each time copying to a different sheet. i.e. Sheet 11, Sheet 12 etc

I presume that once there is a loop I will just change the the code to reflect the changes?


CA02      CAEU
CA02      CALS
CA02      CAOT
CA02      CAUS
CA02      CAGB
CA02      CORP
CA02      CAKL
CA02      CALN
CA02      CANT
CA02      CAEX
CA02      CALQ
CA03      CAEU
CA03      CALS
CA03      CAOT
CA03      CAUS
CA03      CAGB
CA03      CORP
CA03      CAKL
CA03      CALN
CA03      CANT
CA03      CAEX
CA03      CALQ
CA08      COKL
CA08      COMT
CA08      COPL


Hope this is do-able and that I have explained myself ok

Thanks
0
Comment
Question by:Jagwarman
  • 17
  • 15
32 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39665656
Can you post a sample workbook that have this data would be easier to manipulate
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39665676
reading your post again I feel there is something not clear.

You say first I have a sheet1 that has data in Col N ... like data below
then you say if in Col A data is CA02 I want to copy all occurences of CA02 the entirerows to Sheet10


Col N and Col A makes the thing that is not clear.


Here is my understanding based on the sample you provided you want the final outcome to be

Sheet10
CA02      CAEU
CA02      CALS
CA02      CAOT
CA02      CAUS
CA02      CAGB
CA02      CORP
CA02      CAKL
CA02      CALN
CA02      CANT
CA02      CAEX
CA02      CALQ
and for sure the entire row if there is more data in the columns to also show

Sheet11
CA03      CAEU
CA03      CALS
CA03      CAOT
CA03      CAUS
CA03      CAGB
CA03      CORP
CA03      CAKL
CA03      CALN
CA03      CANT
CA03      CAEX
CA03      CALQ

Sheet12
CA08      COKL
CA08      COMT
CA08      COPL


Is this correct ???
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39665864
I have attched a file with data in column 'N' in Sheet1 with Sheets 10 11 and 12 blank except for their header row. There is also a hidden tab called static with the details CA03      CAEU
etc.

So for sheet 1 row 1 where 'N' is CAEU this would look at the table in 'Static' and identify that it is CA02 so the entire row in Sheet1
xxx      www      qqq      ggg      uuu      ddd      24/112013      aaa      lll      19/11/2013      tttt      ccccc      hhh          CAEU      zzzzz      yyyy      0-3

would go on Sheet 10 in row 2 [See Sheet Example]

It may be that my table needs to be turned around so that it becomes

    CAEU      CA02
    CALS      CA02
    CAOT      CA02
    CAUS      CA02
    CAGB      CA02
    CORP      CA02
    CAKL      CA02
    CALN      CA02
    CANT      CA02
    CAEX      CA02
    CALQ      CA02
    CAEU      CA03
    CALS      CA03
    CAOT      CA03
    CAUS      CA03
    CAGB      CA03
    CORP      CA03
    CAKL      CA03
    CALN      CA03
    CANT      CA03
    CAEX      CA03
    CALQ      CA03
    COKL      CA08
    COMT      CA08
    COPL      CA08

and that would be fine.

thanks in advance
Filter.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39666887
Noted but still not clear.
CAEU exist for CA02 and CA03 why should it go on sheet10 and not sheet11 ?
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39667347
my mistake it should only be CA02
0
 

Author Comment

by:Jagwarman
ID: 39667415
I have had a look at the file and clearly missed the fact that each group do in fact have the same codes so for each group add A in front of those in CA02 B in CA03 and C in front of those in CA08

Sorry for the confusion.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39667498
Please post an other workbook modified like you want with clear instructions based on clear codes so I can give you my comments.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39667591
I have uploaded a new spreadsheet.

Sheet 'example' shows how all items in Row 'N' that correspond to the table in static go together and would be copied to another sheet. i.e.sheet10 The best way I can describe this is that if I were to do a Vlookup of column N against the table in the tab 'static' they would look like this

ACAEU      CA02
ACAEU      CA02
BCAUS      CA03
BCAUS      CA03
BCAUS      CA03
BCAUS      CA03
ACAEU      CA02
ACAKL      CA02
BCALS      CA03
ACAKL      CA02
CCOMT      CA08
CCOMT      CA08
ACAKL      CA02
 ACALQ      CA02
 ACALQ      CA02
 ACALQ      CA02
 ACALQ      CA02

so all items rows that correspond to CA02 would be copied to Sheet10 all rows that correspond to CA03 would be copied to Tab 11 etc.

I hope this helps

Thanks
Filter-1-.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39667776
ok fine have several questions:

1) Do we need to keep data in sheet1 or we can cut paste into the new sheets ?
2) IF sheet10, 11, 12 ,,,, does not exist then we create the sheet
3) If the sheet10, 11, 12 ... do exist shall we add to that sheet at the bottom of the existing data the new data we found
or
we delete what we have in those sheet and paste the data fresh ?

Like do you intend to just distribute sheet1 in several sheets and that's it or it is a continuous workbook that will keep receiving data in sheet1 and the macro will always distribute in several sheets at the end of existing data ??

I ask these question coz usually I provide full final working solution not just some code that leave you with the headache of having to adapt to your final need just for the sake of getting points !!!!

Reason why I ask for clarity ! :)
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39668213
Hi gowflow

1) Do we need to keep data in sheet1 or we can cut paste into the new sheets ?

We need to keep the data in sheet 1

2) IF sheet10, 11, 12 ,,,, does not exist then we create the sheet

These will always exist

3) If the sheet10, 11, 12 ... do exist shall we add to that sheet at the bottom of the existing data the new data we found

we delete what we have in those sheets and paste the data fresh with the header row

4) Like do you intend to just distribute sheet1 in several sheets and that's it or it is a continuous workbook that will keep receiving data in sheet1 and the macro will always distribute in several sheets at the end of existing data

Each day the workbook will get new data on these sheets. I have other sheets in the workbook that I add new data to which I already do. It is distributed as a complete workbook

thanks for your assistance
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39668357
ok noted will do
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39668382
One more
Suppose one day you only get data for CA03 will this data go to sheet10 or sheet11 ?

Case it will go to sheet11 (which is not in accordance with the logic you already advised that the first item encountered will go sheet10 etc ...) then would you have any objection that we add in the hidden sheet Static a column C where we put the final destination it will lokk like this:

ACAEU      CA02      Sheet10
ACALS      CA02      Sheet10
 ACAOT      CA02      Sheet10
 ACAUS      CA02      Sheet10
ACAGB      CA02      Sheet10
ACORP      CA02      Sheet10
ACAKL      CA02      Sheet10
ACALN      CA02      Sheet10
ACANT      CA02      Sheet10
ACAEX      CA02      Sheet10
 ACALQ      CA02      Sheet10
BCAEU      CA03      Sheet11
BCALS      CA03      Sheet11
BCAOT      CA03      Sheet11
BCAUS      CA03      Sheet11
BCAGB      CA03      Sheet11
BCORP      CA03      Sheet11
BCAKL      CA03      Sheet11
BCALN      CA03      Sheet11
BCANT      CA03      Sheet11
BCAEX      CA03      Sheet11
BCALQ      CA03      Sheet11
CCOKL      CA08      Sheet12
CCOMT      CA08      Sheet12
CCOPL      CA08      Sheet12


Pls advise
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39668389
It will always go to the same sheet. Adding in the sheets as in your example is a very good idea

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39668412
ok great. This way you could simply update endlessly this static sheet with what ever new sheet reference you want with whatever new code that there is and the macro will work accordingly !!! No code modification necessary.

I am just puzzled at the fact that you don't want (after distribution is performed) to delete the data that is in Sheet1. The only logical answer for this would be that after running this macro you would be running other macros that would update other sheets based on sheet1 data reason why you would still need this data to be there. Is this statement correct ?

One last thing, is there a possibility somewhere to have the Code CA02, CA03 ... exist in the data in sheet1 in a certain column when you get this data downloaded or it is not a possibility ?

gowflow
0
 

Author Comment

by:Jagwarman
ID: 39668463
I am just puzzled at the fact that you don't want (after distribution is performed) to delete the data that is in Sheet1. The only logical answer for this would be that after running this macro you would be running other macros that would update other sheets based on sheet1 data reason why you would still need this data to be there. Is this statement correct ?

Correct

One last thing, is there a possibility somewhere to have the Code CA02, CA03 ... exist in the data in sheet1 in a certain column when you get this data downloaded or it is not a possibility ?

No not possible
0
 

Author Comment

by:Jagwarman
ID: 39668467
ok great. This way you could simply update endlessly this static sheet with what ever new sheet reference you want with whatever new code that there is and the macro will work accordingly !!! No code modification necessary.

this sounds brilliant. Thanks
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 29

Expert Comment

by:gowflow
ID: 39668488
perfecto !
let me go to work now !!! :)
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39668608
ok here it is:

1) This version will create the sheet if they don't exist

2) Have in Static a reference to the sheets name you need to have in Col C that are user set

3) If a code in Col N is not found in sheet Static then it will be reported in a sheet called SheetMissing this would indicate that you will need to go back and add or modify data in Sheet Static to reflect the correct data. When this happens you will simply after updating sheet Static re-run the macro and it will update the data correctly. You may try this situation by simply changing the code in Sheet1 for any row so it does not find it in sheet static.

4) The Sheet1 at some point is being Sorted by sheet like the initial sequence of data in Sheet1 is not the same that will be copied onto the final sheets and also is not the same as originally posted in sheet1 if this is a problem let me know then I will need to revise the logic.

Please try this version extensively and revert with your comments.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39668610
did you intend to include a file?
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39668614
For sure your quick !!! here is the corrected version:


1) This version has only sheet1 to start with and to run the macro goto Macroes and activate macro called DistributeData

2) This version will create the sheets if they don't exist

3) Have in Static a reference to the sheets name you need to have in Col C that are user set

4) If a code in Col N is not found in sheet Static then it will be reported in a sheet called SheetMissing this would indicate that you will need to go back and add or modify data in Sheet Static to reflect the correct data. When this happens you will simply after updating sheet Static re-run the macro and it will update the data correctly. You may try this situation by simply changing the code in Sheet1 for any row so it does not find it in sheet static.

5) The Sheet1 at some point is being Sorted by sheet like the initial sequence of data in Sheet1 is not the same that will be copied onto the final sheets and also is not the same as originally posted in sheet1 if this is a problem let me know then I will need to revise the logic.

Please try this version extensively and revert with your comments.
gowflow
Filter-1-V1.xlsm
0
 

Author Comment

by:Jagwarman
ID: 39669009
Thanks gowflow. I will, as you say, check it extensively and revert with my comments.

In the meantime have a great weekend

Rgds
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39669475
u too.
gowflow
0
 

Author Closing Comment

by:Jagwarman
ID: 39680902
brilliant it works perfectly,
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39681319
Excellent. Please feel free to let me know if you would need any more help by posting it in here. I will keep this question monitored.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39689630
gowflow,

apologies for pestering you on this one again but I have just discovered [a bug maybe] something that I was not aware of.

Where it captures the data from the Static tab and puts it on the relevant tab, it's brilliant. Where it creates a TAB 'sheetmissing' it falls over. Say for instance there are 15 items to put onto the sheetmissing tab, for some reason it is trying to go beyond the last row [1048576] and falling over.

"Loop Until ColZZ <> WS.Cells(J, "ZZ")" Application defined or object defined error



Can you assist
0
 

Author Comment

by:Jagwarman
ID: 39689634
P.S. It then comes up with "Excel cannot  complete this task with available resources. Choose less data" ?????
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39690096
let me look at it will revert v shortly. Sorry for the inconvenience.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39690107
Could  you post the workbook it would help clear it quickly ??
gowflow
0
 

Author Comment

by:Jagwarman
ID: 39690184
Thanks gowflow
testfile.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39690259
ok sorry for that try this one and let me know.

to be clear on what I changed you will need to replace these 2 lines
For I = 2 To MaxRow
    
    If ColZZ <> WS.Cells(I, "ZZ") Then

Open in new window


By these 3 lines
For I = 2 To MaxRow
    If WS.Cells(I, "ZZ") = "" Then Exit For
    
    If ColZZ <> WS.Cells(I, "ZZ") Then

Open in new window



gowflow
testfile.xlsm
0
 

Author Comment

by:Jagwarman
ID: 39692185
thanks gowflow that works fine now.

Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39692595
Your welcome sorry for the inconvenience.
gowflow
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now