+ Reply to Thread
Results 1 to 10 of 10

How to filter data in spreadsheets?

Hybrid View

  1. #1
    Registered User
    Join Date
    11-01-2009
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question How to filter data in spreadsheets?

    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.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to filter data in spreadsheets?

    Welcome to the forum.

    Post an example workbook that shows inputs and expected results.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-01-2009
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to filter data in spreadsheets?

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to filter data in spreadsheets?

    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?

  5. #5
    Registered User
    Join Date
    11-01-2009
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to filter data in spreadsheets?

    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.

    Sheet1:
    Col1 Col3
    A     1
    B     2
    C     3
    D     4
    E     5
    F     6
    G     7
    
    
    Sheet2:
    Col2   Col3
    4       ABC
    4       ABG
    5       E
    6       FBI
    6       ABC
    6       B
    
    Sheet3 (result of filter)
    Col2   Col3
    4       ABC
    5       E
    6       ABC
    6       B
    Thanks for the help
    Last edited by shg; 11-01-2009 at 07:49 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to filter data in spreadsheets?

    In the example, why doesn't FBI survive?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1