Help Printing Rows with specific terms.

**DrOct** · 06-23-2008, 04:31 PM

I'm fairly new to Excel so please excuse my ignorance.

My boss has been sent an excel sheet with a list of financial transactions on it. She wants to be able to search for a specific term, and display and then print every row in which that term appears. For example if we were interested in the term "John" we would want to see every row that contained "John" regardless of what column it was in, and what other text was around it (could be "John Gas" or "John takes client out to dinner" or "John Smith" etc), and then be able to print out all of those rows without printing out the rest of the list.

Is there an easy way to do this? I've figured out how to apply filters to specific columns, but we need to be able to find the term in any column, and regardless it won't print out the entire filtered list on one page anyway.

I'm using a rather old version of Excel (2002) but my boss has Excel 2007, so if it's something that can be done in the most recent version but not my older version that's fine, we just need to be able to do it somewhere.

If there is any other information that I could provide that would be helpful, please let me know!

**ChemistB** · 06-23-2008, 05:35 PM

How many columns of information are there?

You said that even if you filtered so that only the rows with "John" are there, it still won't print on a single page? Why not?

ChemistB

**DrOct** · 06-24-2008, 11:56 AM

The spreadsheet has nearly 1200 rows and 17 columns (you can see why we want to be able to do some automated filtering, rather than trying to go through manually).

The filtering I was able to do was for individual columns, but that won't get me every instance where a name or term shows up, just every instance within that column. For some reason when I try to print the filtered list, it will simply print each page of the document with whatever filtered elements are on that page. So for example if there are 5 instances on page 1 it will print page one with those 5, if there are none for the next 7 pages it will print blank pages until it reaches another page that has a few instances, and then print those, and so on and so forth.

Its possible this is only a problem with my version of Excel, and it wouldn't do this on my boss's computer, but even if that's the case, as I said that won't really fulfill our needs, as it will only print out the instances that show up in that individual column. We'd have to do it over again for each column, and then go through and manually find and eliminate all of the repeated instances (where the term showed up more than once in the same row in different columns).

**ChemistB** · 06-24-2008, 12:17 PM

I attached an example of the Advanced Filter. The problem is that the criteria must be on a separate line for each column (see example) so your criteria table will be 18 columns (1 for the headers). You also need unique headers for each of your columns. You can place the criteria table off to the right or down near the bottom of your worksheet but I don't think it works on a separate worksheet.

As to your problem with printing. Do you have manual page breaks in the workbook? That would cause what you are describing.

ChemistB

**DrOct** · 06-24-2008, 12:46 PM

Thank so much for all of your help!

Ok, here comes the part where I'm new to Excel. I have no idea how to apply this to the spreadsheet I have. I haven't really done anything with advanced filters before, so... how do I use this?

**ChemistB** · 06-24-2008, 01:25 PM

Hi DrOct,

Okay, the setup.

Your data needs to be in consecutive rows with a unique header at the top of each row.

Copy and paste the headers to where you're going to have your criteria range (as I said before, at the top, bottom, to the right, where ever)
Directly beneath the header criteria, start typing in *John* (the *'s act as wildcards). Type one under each header that you want to look for John in and each in a new row.

Explaination: If the criteria are in the same row, then Excel uses "And", if they are in separate rows, then it's "Or". You want "Or."

Done with setup, now Data>Filter>Advanced. Include your headers in the range for both the "List Range" and the "Criteria" range. Your data should filter. If you're having problems, upload a small sample of your spreadsheet.

ChemistB

**DrOct** · 06-24-2008, 02:29 PM

Well I think I have the filter and the criteria set up correctly, but I'm still having trouble. I can get everything in the example you sent me to work the way I want (I've been adding new rows with data to experiment in trying to pin down the problem in my spreadsheet). I can't find any difference between the way I have the criteria and data set up in the example and the way they're set up in the spreadsheet I'm working with.

Most of the time when it seems like I have everything set up right all of the data disappears (like it's all been filtered out) which doesn't make sense because I know for a fact that several of the rows contain the term I'm looking for, so why are they getting filtered out?

Further as an experiment I tried to get ride of all of the criteria except in one column where I knew the term showed up, I told it to filter and... nothing happened. All of the data stayed right where it was, whether a row had the term or not nothing seemed to be filtered. This is driving me crazy!

Is it possible that there is some problem because the data I'm working with is linked? ie the data all ads up/subtracts so that at the bottom you end up with a balance sheet.

I'll try to figure out what I can do when it comes to posting an example of part of the spreadsheet. The problem is that it's financial data from a client of ours so I'll need to figure out if I can edit the info enough to make it work, without revealing private information.

**ChemistB** · 06-24-2008, 02:36 PM

I'd like to see your criteria list (copy it to an empty workbook and you can delete the headers if they're confidential) and a copy of the Advanced Filter popup window (Do an Alt+Print Screen and paste to a Word document) so I can see if you're setting it up properly. The fact that cells are linked shouldn't change things.

ChemistB

**DrOct** · 06-24-2008, 03:09 PM

Ok, here is a copy of my criteria table. I'm still working on figuring out a way to upload the Advanced filtering pop-up window as when I paste it into a word document it is too large for me to upload to this forum...

**ChemistB** · 06-24-2008, 03:16 PM

Well, it looks like you set up your criteria list correctly. Can you zip/compress the Word file? Or just copy and paste what's in each of the boxes and what's checked off or not checked off. Thanks

ChemistB

**DrOct** · 06-24-2008, 03:16 PM

Ok, got a jpg of the Advanced filtering Pop Up window. It is attached here.

**ChemistB** · 06-24-2008, 03:26 PM

Okay, my guess.....
You have 9 columns and only 9 rows in your Criteria range. Are you including your headers in that range? Also need to include headers in the List range too.

ChemistB