How to filter data in spreadsheets?

**Zepher** · 11-01-2009, 02:53 PM

I have some data that looks like this:
5 差し上げる
5 机の上
5 申し上げる
5 上手な
5 上着
6 テーブルの下
6 下げる
6 下宿する
6 下手な
6 下着

and in another sheet, corresponding data like this.
上 5
下 6
中 7
...
後 53
手 54
新 55

I want to be able to filter out the rows from the first sheet when a symbol (kanji) from the right hand side is included at a number higher than that of the second sheet.

For example, 手 appears as a number 6 in the first sheet but doesn't appear until 54 in the second sheet. Therefore I would like to delete the row with 手 in the first sheet.

Is there a simple way to do this? If not, can anyone suggest a way to program this? I was thinking of writing a little VBA code that reads the the characters and then compares them to every number above and if it doesn't find it, deleting that row but I have 2 problems.
1. How can I tell VBA to actually delete a row (not just the contents preferably)
2. These are asian characters which I'm not sure are well supported in strings which I would need for comparison?

Thanks.

**shg** · 11-01-2009, 02:56 PM

Welcome to the forum.

Post an example workbook that shows inputs and expected results.

**Zepher** · 11-01-2009, 03:14 PM

Thanks.

I have attached an example with the first two sheets as mentioned above and the third sheet with the filtered data. It might be worth it to note that as you get higher in number, you filter less data out because there are more symbols (kanji) included in the first sheet. I hope this makes sense, if there is anything else I can do to clarify, please let me know.

Thanks again.

**shg** · 11-01-2009, 07:37 PM

Explain once more ...

For each character in the sheet1 col A, find it in sheet2 col A. If the number in sheet1 col C is less than the number in sheet2 col B, hide the row on sheet1?

**Zepher** · 11-01-2009, 07:46 PM

For each row in sheet 2, find if each symbol in column 3 is located at or above the number given in column 2 on sheet 1.

IE, if Sheet 2, row 4 column 3 = XYZ and column 2 = 10 then search numbers 1-10 (located in column 3) in sheet 1. If X,Y, and Z are within 1-10 then leave the row in sheet 2. else, delete the row in sheet 2.

Please Login or Register  to view this content.

Thanks for the help

**shg** · 11-01-2009, 07:52 PM

In the example, why doesn't FBI survive?

**Zepher** · 11-01-2009, 08:06 PM

FBI doesn't survive because 'I' is not contained within rows 1-6 from sheet 1.

**shg** · 11-01-2009, 08:20 PM

Missed that.

See attached; you can tell me why the results are incorrect. The formulas show where the characters appear in sheet1.

**Zepher** · 11-01-2009, 09:53 PM

So... It took me about an hour but I've realized a couple things.

1. You have a lot more knowledge programming than I do
2. The way I thought I wanted to sort it isn't quite the way I really want to sort it (Looking at the data closer, I want to sort by that first character, not the number)

With that, I set off with the code you provided and altered it for my new search. I figured out that one of the reasons your search was having so much trouble is the hiragana (non-kanji) characters were not in the list on sheet 1. So I added those to my list (though I didn't give them numbers because I don't need them for the new filter) and then slightly altered the code that you had given me.

I have attached the final version with my subroutine. The results that should be filtered have been given a color and, as you can see, it seems to work. I will need to try this for a larger data set before I am 100% confident but so far its great.

Thank you very much for the help

Edit: I did just notice something strange. If I change cell.EntireRow.Hidden = true to cell.EntireRow.Delete then it doesn't delete all the rows in a single execution. If I execute the subroutine multiple times, it does so and does correctly but that just seemed strange.