OK, here goes:
I have a spreadsheet that looks like this (more or less, except that it’s much bigger):
Name Task1 Task2 Task3 Task4 Location Year
Bob Trash Trash Trash Mop NYC 2011
Steve Trash Mop Sweep Mop NYC 2010
Mike Sweep Mop Mop Trash LA 2010
Pat Sweep Mop Mop Mop LA 2010
What I want to do is create some kind of process that would take each row that has the word “Trash” in any (not necessarily all, but at least one) of columns B, C, D or E, and reproduce that row in another worksheet in the same workbook.
So I’d end up with a second worksheet like this:
Name Task1 Task2 Task3 Task4 Location Year
Bob Trash Trash Trash Mop NYC 2011
Steve Trash Mop Sweep Mop NYC 2010
Mike Sweep Mop Mop Trash LA 2010
For reasons completely beyond my control, I can’t use a pivot table (although if this is a good solution, I’d like to know about it anyway).
Any help very much appreciated.
Yes, I see that my rows and columns are all off, but I think it’s pretty clear. If it’s not, I’ll try to do better.
It looks like you’re trying to make a copy on another worksheet.
Might I ask what you’re trying to accomplish by doing this?
Well, I’m trying to make a new worksheet that contains a subset of the rows in the first worksheet.
I’ve been asked to do this by someone at work. He wants a worksheet showing only those items that meet certain criteria. What his ultimate business purpose is, I have no idea.
I think I’ve got it – advanced filter for multiple criteria. It filters just fine, except I can’t get it to put the results in another workbook. Still working on that.
Does this need to be dynamic? If it’s a one off just filter on Sheet1 then copy/paste to Sheet2.
Alternatively something like this would work (in cell A1 of Sheet 2 then fill right/down to cover all the cells in Sheet1.
IF(ISNA(MATCH("Trash",Sheet1!1:1,0 )),"",Sheet1!A1)
This will give you blank lines in Sheet2 where there’s a ‘trash’ on sheet one. You can then sort Sheet2 to move the lines to the bottom.
There are other formulas that would do the filling more automatically but it’s going to get complex (and potentially very slow depending on the data). At that stage I’d consider writing a custom function/macro to do it.
SD
Not workbook, worksheet (by my reading of the OP). Good solution if it doesn’t have to be dynamic.
Thanks, everyone. I used the Advanced Filter to do the filtering I need. I still can’t figure out how to make the filtered results go to a new worksheet, though. Which is what I’ve been asked to do, unfortunately.
Have you tried v-lookup? I’m not sure it’ll work for exactly what you want, but I think with some tinkering it’ll get you there, or close. Unfortunately, I’m not an expert with it, so I can’t tell you specifics.
I got it! I had about an hour to fool around with this earlier today, and here’s what worked:
Advanced Filter gives me the results I want – I can filter the list by various things in each row, or multiple things. The problem was that it wouldn’t output its results to a new, separate worksheet.
Turns out the trick is to create the advanced filter in a new, blank worksheet, then give it the range of data to sort, which is in the original worksheet. The filter will only put its results in the worksheet in which it lives, so this works fine.
Thanks, everyone, for your suggestions.