Filtering by text

[saltarazan]

Hi,
Take a look please at the attached file (filter.xlsx).
I want to get the rows that have the world "three" in column D to show up, AND if there are rows that are related to column A and B, to fix the number (column A) and the date (column B) that include them.
I hope the explanation is clear.

any help?
thanks

[Ken Puls]

I think the main issue is that your data has holes in it. What I would do is fill column A and B with the values from the above cells instead of blanks. At that point adding a filter will show you the data in all the visible rows.

If it is critical that you have bank cells to show separation from the dates then, after filling the cells as I described above, I'd build a PivotTable off the data. That will allow you to filter on the column, but will not repeat data that is showing above.

[saltarazan]

I put here a little example, because I have another file that has ALOT (thousands) of rows and "holes" between the dates, that makes it so difficult and time wasting to start filling. (I got these data from someone else).
thanks for replying, and I hope you or someone else find a solution, its so important to me because it's part of project.

[Mary]

You could have a column that does the following =+IF(A2="","blank",A2) considering A contains the information you want and B does the "cleaning".

[saltarazan]

Hi mary,, first of all I have to keep the data without adding words in between. Second thing, your formula doesn't work on dates (It returns numbers like 40941 instead of 02/02/2012)! ,, and I didn't understand exactly what did you mean by "... and B does the "cleaning""
Thanks for replying

[Ken Puls]

Okay, so here's what I did with this.

First off, because of the names in your sheets, I couldn't create a PivotTable, so I had to copy your data to a new document. It's actually the first time I've un into a workbook that flips the left-> to a left<-right read. I think the characters in your language combined with the view that is opposite to what I normally see must have confused Excel a bit.

At any rate, hopefully you can open and follow this.

• I inserted a header row above your data so that I had field names for the PivotTable
• I addded the following to row 2:
  
  • F2: =A2
  • F3:F13 =IF(A3="",F2,A3)
  • G2: =B2
  • G3:G13 =IF(B3="",G2,B3)

That gave me a table to work with that I could pull into a PivotTable.

I've attached a workbook with the PivotTable in it. If you need any info about how to create it, let me know.

[saltarazan]

I see that you did filteration based on column E (yes/no). but I actually added that column E just to point on the rows that have the word "three".
You've resolved the problem for that specific case, but I want to be able to filter by any word in column D, regardless of what is written in column E or any other column !
In my project file, In column D there're different sentences (and in column E it's not yes\no !), and I want to be able to filter by key words in these sentences!
(another little problem now, why dates turn to numbers?)
sorry for being complex, but I really belive there is solution to my issue even without pivote tables, I hope.
thank you

[Ken Puls]

Dates and numbers are the same thing. Have a read of this article It should help.

With regards to the PivotTable, the reason I'm suggesting it is that it is going to be BY FAR the easiest way to get your data the way you want. Especially if you can't share all the rules with us.

Try this:
- Pull the "Show?" field off the table
- Click the Filter icon on the "FilterColumn" and choose Label Filters --> Contains

At that point I can filter for one, two, three or four and it will pick up every record with that text in it.

[saltarazan]

I attached your file with adding new Sheet (Sheet2). I put in it a little part that shows the problems of the raw data that I got from my colleague.
So what if for example I want to filter by the word "designing" in column D, and get the results that I asked before?
As you'll see, there're alot of things and preparations that have to be done to finally use a pivote table (as I said there're thousands of rows).
and notice how there are texts in the Dates (column B), that I don't know how to move them to the Notes (column G).
I know that it got really complex and challenging now! but if we could solve this it should work on the entire original project.
By the way, I've learned many things in dealing with this issue, and great part from you, I appreciate that.

[Ken Puls]

Okay, so here's the trick...

In order to use a PivotTable, your data source needs to be one big set of "contiguous" rows. (i.e. no blank rows). So that's part of what we use the formulas to fix. By running those down from the row below the heading all the way to the last row of data (10 or 10,000 doesn't matter), we create that table.

In addition, since we need to create the big table anyway, that's where we can get the formulas to create the consistent data we need. The real trick here is not the PivotTable, it's creating the formulas to deal with all the stuff that your co-workers throw into the data to make it inconsistent.

In the case of your example, here's what I would do:

• I1: Full Period
• I2: =IF(A2="",I1,A2)
• J1: Full Date
• J2: =IF(AND(ISNUMBER(B2),B2<>""),B2,J1)
• Copy I2:J2 down to the last row of data

For reference, J2 checks if B2 is both a date and not equal to a blank cell. This will ensure that you only get dates and ignore any other notes in the file. You don't need to move them out, you just need to craft a formula that ignores them when you're building your "real" columns to work off.

Now you can build a PivotTable off it. I set the Pivot up in this shape:

Now, at that point I get a Pivot that has all the "work" listed in it. To get to just the design, you click the filter arrow on the Pivot beside Work, choose Label Filters --> Contains and set it to Designing.

If the filter gets too complicated, then create a new column in your data table and use IF, AND, OR statements to come up with a True or False value, then filter the table by that.

At the end of the day, the magic is in the formula set with your table. As long as you have the original source data, you can keep building your criteria off the table to "fix" or "clean" the data from your co-workers, and mine it very very quickly with the Pivot. And if you need help creating a formula... well... that's what we're here for.

I've attached a copy of the workbook for you too.