Multiple Filters and Merge Cell Removals 2

[Topcat04]

Hi Forum

Looking for some help please.

Twice a month we receive a new pre formatted data file that contains product information and pricing updates.

This file is currently over 2000 rows.

Included in the rows are merged cells to the width of the data so Column A to Column E. Out of the 2000 rows there must be 40 rows that are merged like this. This creates an immediate challenge on using standard filters in Excel as it only goes down to the first merged cell.

What we would like to be able to do is filter on the data using the 'contains' filter - however using these standard filters this limits us to 2 filters on the same column, eg Column B has part numbers and using the contains filter we can currently only filter using contains 'Media manager' AND contains 'ST'

As the Media Manager products can be determined by ST 10, ST 16, ST 100 there are further filters that are required to get to the data. We like to use these filters 'live' whilst on the phone to clients to be able to advise them of their best options.

A) is there a way of being able to remove the merged cells easily
B) is there a way of being able to filter through the data more effectively to be able to get to the correct options available?

It would be good to get it as automated as possible so we can provide a quick response to the client.

Your help is much appreciated.

 

[Gavona]

Yes:
Select all data, then in the non-ribbon version of Excel Format, Cells, Alignment and untick merged cells (it will initially be grey reflecting that there are a mixture of settings.

Advanced filter will do the rest. Call the data range "database", the criteria range "criteria" and these will default in when you Data,Filter,AdvancedFilter.

(Personally I would consider using VBA and events to automate the application of the advanced filter. And CurrentRegion to cope with changing size of criteria range. But get it working manually first.)

Gavin

 

[Topcat04]

Hi Gavona

I have been unable to unmerge the data as you suggested, but I didnt get the advanced filter to do the rest, I could only get it to do the two filters, instead of 3 or 4 or 5 in some cases.

VBA sounds good, but as you said, a good start will be able to understand to do it manually.

any more light you can throw on the subject will be appreciated,

Thanks!

 

[Gavona]

Are you using Excel2003?

I have been unable to unmerge the data as you suggested,

I just checked. Works for me. The Merge Cells tick box is initially blue. First click puts a blue tick in the box, second removes it.

I could only get it to do the two filters, instead of 3 or 4 or 5 in some cases.

re you using Advanced Filter rather than autofilter? Please describe your criteria range.
Have you looked at Excel Help? It linked me to here:

http://office.microsoft.com/en-gb/e...ria-HA001218694.aspx?CTT=1&origin=EC001022986

On that page click on the link Multiple sets of criteria where each set includes criteria for multiple columns
an on:
Criteria to find text values that share some characters but not others




Gavin

 

[Topcat04]

SORRY!!

I meant to say ABLE to Unmerge the data as you suggested,

I will take a look at that help link, thanks!

Im using 2010 for reference,

Sorry again for typing too quickly and not checking properly,

 

[Topcat04]

HI Gavin

Sorry - I could do with a little more guidance please, as Im struggling to make this work.

I have clicked through to the help and I have also tried to name the range (not able to get it to prepopulate the name ranges)

Ive placed in the cell under the column header ="=?Media*" hoping it would then filter out all the Media Centers, but alas nothing happened.

I also tried with an exact name and nothing happened,
Sorry I must be missing a step or something..but I can not get it to work at the moment,

Any further help will be much appreciated!

 

[Topcat04]

Hi there

Any additional help on how to be able to do these multiple criteria searches will be much appreciated,

Thanks!

 

[SkipVought]

Ive placed in the cell under the column header ="=?Media*"

Are you referring to your table column header?

A) is there a way of being able to remove the merged cells easily

Select all cells and then UNMERGE

B) is there a way of being able to filter through the data more effectively to be able to get to the correct options available?

Your table filtering seems somewhat complex. Please post some sample data that illustrates that complexity and explain how you need to filter.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Thanks for your help.

I have been able to unmerge cells easily now...so thats a good start.

ive got a sample of the table ready, im trying to work out what would be the best way to post on here, Ive only typed text on here before.

Thanks so much for responding!

 

[Topcat04]

Skip

not sure you will be able to read this correctly, but hopefully it will give you an idea

Part Number	Model	Description	MSRP UKP £
	=?Media*		
			
			
Part Number	Model	Description	MSRP UKP £
05059930            	MCR1900-DDC Chassis	MCR1900-DDC - 19 slot chassis for media Converter module for MCR chassis. Dual 48vDC power expandable to dual DC power. 15 slot plates included	1086.00
05059812            	MCR-ACPWR Pwr Supply EU	MCR-ACPWR  - Redundant AC power supply for MCR1900-AC	256.00
05059815            	MCR-ACPWR Pwr Supply SA	MCR-ACPWR  - Redundant AC power supply for MCR1900-AC	256.00
05059811            	MCR-ACPWR Pwr Supply UK	MCR-ACPWR  - Redundant AC power supply for MCR1900-AC	256.00
05059820            	MCR-DCPWR Pwr Supply	MCR-DCPWR - Redundant 48vDC power supply for MCR1900-DC	414.00
05059830            	MCR-FPL faceplate	MCR-FPL - Face plate for empty slots on MCR1900	5.00
05059840            	MCR-RMK23mounting brackets	MCR-RMK23 - One set of rack mount brackets for 23 inch racks	15.00
05059999            	MCSM mounting bracket	Standalone media converter wall / rack mount bracket	8.00
05084012            	Media Converter  S-110P-M2SC2 EU	S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm multimode (SC) [2 km/1.2 miles] includes AC Power Adapter	168.00
05084015            	Media Converter  S-110P-M2SC2 SA	S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm multimode (SC) [2 km/1.2 miles] includes AC Power Adapter	168.00
05084011            	Media Converter  S-110P-M2SC2 UK	S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm multimode (SC) [2 km/1.2 miles] includes AC Power Adapter	168.00
05051010            	Media Converter C-1000-M2LC05	C-1000-M2LC05 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASESX 850nm multimode (LC) [550 m/1804 ft.]	214.00
05041960            	Media Converter C-1000-M2LC2	C-1000-M2LC2 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASELX 1310nm Extended multimode (LC) [2km /6562 ft.]	298.00
05051000            	Media Converter C-1000-M2SC05	C-1000-M2SC05 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASESX 850nm multimode (SC) [550 m/1804 ft.].	214.00

thanks!

 

[Gavona]

CSV format would have worked better (we could copy and paste into Excel) but I think I can guess where the column breaks are!

What results are you expecting from this filter?
I assume you would want to see just the rows for Part Numbers
05084015
05084011
05051010
05041960
05051000

However your criteria seems to be looking in the Part Number column whereas the Model column would be better.
Also your criteria should be ="=Media*" unless there is a character before "Media" in the Model column.



Gavin

 

[Gavona]

Try this:

Part Number,Model,Model,MSRP £
,=Media*,=*M2S*,
,=*faceplate*,,
            ,,,
Part Number,Model,Description,MSRP £
05059930,MCR1900-DDC Chassis,MCR1900-DDC Chassis    MCR1900-DDC - 19 slot chassis for media Converter module for MCR chassis. Dual 48vDC power expandable to dual DC power. 15 slot plates included    1086.00,1086
05059812,MCR-ACPWR Pwr Supply EU,MCR-ACPWR Pwr Supply EU    MCR-ACPWR  - Redundant AC power supply for MCR1900-AC    256.00,256
05059815,MCR-ACPWR Pwr Supply SA,MCR-ACPWR Pwr Supply SA    MCR-ACPWR  - Redundant AC power supply for MCR1900-AC    256.00,256
05059811,MCR-ACPWR Pwr Supply UK,MCR-ACPWR Pwr Supply UK    MCR-ACPWR  - Redundant AC power supply for MCR1900-AC    256.00,256
05059820,MCR-DCPWR Pwr Supply,MCR-DCPWR Pwr Supply    MCR-DCPWR - Redundant 48vDC power supply for MCR1900-DC    414.00,414
05059830,MCR-FPL faceplate,MCR-FPL faceplate    MCR-FPL - Face plate for empty slots on MCR1900    5.00,5
05059840,MCR-RMK23mounting brackets,MCR-RMK23mounting brackets    MCR-RMK23 - One set of rack mount brackets for 23 inch racks    15.00,15
05059999,MCSM mounting bracket,MCSM mounting bracket    Standalone media converter wall / rack mount bracket    8.00,8
05084012,Media Converter  S-110P-M2SC2 EU,S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm,168
05084015,Media Converter  S-110P-M2SC2 SA,S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm,168
05084011,Media Converter  S-110P-M2SC2 UK,S-110P-M2SC2 - 10/100 Fast Ethernet Stand-Alone Media and Rate Converter with PoE Power Sourcing. 10/100Base-TX (RJ-45) [100 m/328 ft.] to 100BASE-FX 1310nm,168
05051010,Media Converter C-1000-M2LC05,C-1000-M2LC05 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASESX 850nm multimode (LC) [550 m/1804 ft.],214
05041960,Media Converter C-1000-M2LC2,C-1000-M2LC2 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASELX 1310nm Extended multimode (LC) [2km /6562 ft.],298
05051000,Media Converter C-1000-M2SC05, C-1000-M2SC05 - Gigabit Ethernet Media Converter Module. 1000BASE-T (RJ-45) [100 m/328 ft.] to 1000BASESX 850nm multimode (SC) [550 m/1804 ft.].,214

Copy the above into Excel then use [sorry, Excel 2003] Data, Text to Columns and specify comma as the only delimiter.

The criteria are actually entered like this:
="=Media*"
="=*M2S*"
="=*faceplate*"


That is my attempt to exemplify the more complex criteria that you were originally posting about. All the criteria appearing on the first row must be true OR All the criteria in the second row. So I illustrate how to show records where:
Either the Model starts with "Media" and contains ""M2S"
OR the Model contains "faceplate"

A column heading can be repeated multiple times in the criteria range and you can use as many rows as you like.

Gavin

 

[Topcat04]

Hi Gavin

Unfortunately that was how the information displayed out on the screen as like you said, I didnt use a csv file.

I can confirm that the criteria was entered under the model column.

I must be doing something wrong with the criteria section as I can not get the criteria to work within the Data, Advanced Filter option.

Is there a step I should be taking that you think is visible that I am not doing?
Thanks for being patient with me.

 

[Gavona]

Hi,
1. Did you see this?

Also your criteria should be ="=Media*" unless there is a character before "Media" in the Model column.

2. Have you tried my exemplar?

Regards,



Gavin

 

[Topcat04]

Hi Gavin

yes I have tried your example,

I get the feeling Im just not understanding the list range and criteria range correctly in the Advanced Data Filter and the instructions from Microsoft are not making any difference either.

So sorry!

 

[Gavona]

No worries.
So did you get my example to work? Or did you try it and not get it to work?


Gavin

 

[Gavona]

In case it helps, in my example:

With the filter criteria starting in A1 and the database in A5

List Range =: A519
Criteria range =: A13

Gavin

 

[Topcat04]

hi Gavin

Thats got it!

I was setting the criteria too large.....with you showing me the exact criteria range, I got it to work!

Thanks so much for your patience!!

 

[Gavona]

Glad to help.

(Personally I would consider using VBA and events to automate the application of the advanced filter. And CurrentRegion to cope with changing size of criteria range. But get it working manually first.)

The recorded code looks like this:

Range("A5:D19").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range("A1:D3"), Unique:=False

However you can make it so the ranges change size as you add and delete criteria or records in your data table. And I like using named ranges:

Sub Applyfilter()
Range("Database").CurrentRegion.AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range("Criteria").CurrentRegion, _
    Unique:=False
End Sub

You can assign that macro to a button on the worksheet. Or make it apply automatically using events like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("criteria").CurrentRegion) Is Nothing Then Call Applyfilter
End Sub

That code goes in the sheet module. For more info

http://www.ozgrid.com/VBA/run-macros-change.htm

Gavin

 

[Topcat04]

Hi Gavin

Thanks for the automation - Im going to implement that this afternoon.

I have been working on the solution previously provided and it works well - but not quite where I want to get to - if its possible.

So you have helped me be able to filter for example on the Media Converters, by using ="=*Media*"

Now within that group of Media Converters there are versions that have SC in them and others have ST etc.

Im looking to be able to then limit the media converters to just the SC, so I used the following on the next line under the previous criteria.

="=SC*"
This kind of worked,but it seemed to pull in all the items with SC in them, rather than just Media Managers

And the the final one, which I could not get to work, was to only show out of the above selection the 1110 versions. so again I tried ="=*110*", but that didnt filter the above down any further.

Hopefully you can see that I want to continue filtering down to the only possible solutions with the filters in place at each step.

Is this something that is possible and have I just still misunderstood the steps?

As I said before, really appreciate your help and patience with my 'unknowns'

Thanks!