Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple Filters and Merge Cell Removals 2

Status
Not open for further replies.

Topcat04

Technical User
Sep 14, 2007
121
GB
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.
 
Hi Gavin

The vb script works great!! thank you for this on change event.

I hope you can help me drill the data correctly to the bottom level as you have then helped me be able to respond much quicker to sales enquiries.

Thanks!!
 


When you have an OR condition, in your case
[tt]
Model =Media* OR Model =*M2S* OR Model =*110*
[/tt]
so your Criteria for Model must be...
[tt]
Model Model Model
=Media* =*M2S* =*110*
[/tt]
or however many OR criteria you have for a field.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Hi Skip

Thanks for clarifying this!

Excellent support from you and Gavin, so appreciated!

all the best!
 

Sorry I misstated!!! [blush]

ANDS get listed horizontally on the same row

ORS are listed vertically in the same column.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Your welcome Topcat!
(I actually did illustrate how to AND and OR in the example I posted)

Gavin
 
Hi Gavin

A quick follow up question. Sorry!

I wanted to add a new line on row 1. which I have done.

been into Name Manager, changed the cell reference for Criteria to now start A2:J3

My cell references are now pointing to List boxes on a 2nd worksheet.

Each time I make a selection from the list box, it seems to change the criteria reference back to A1:J3 instead of keeping it at A2 as per the name manager refrence.

Any ideas why this is making this change?

 
The criteria range needs to be bounded by blank rows and columns.

The code included [range].currentregion
this is the same as selecting the topleft cell in [range] and pressing Ctr-A.




Gavin
 
Gavin

Thanks sir! Wish I could award you another star, I put the header at the top and then put a blank line above the headings and job's a good un!

Much appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top