Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing (or formatting) only columns that have an item in another column

    I am using Excel 2010. I use it all the time, but not the super advanced stuff, and that is what I need. I have a sheet1 with over 11,000 rows. I have another sheet, sheet2, with about 30 rows. I want to do one of two things:
    1. Format (probably with some fill) any row in sheet1 that has a value in column A that matches any item anywhere in column A on sheet2.
    2. Hide any rows in sheet1 for which the value in column A does not match any value in column A on sheet2.

    Thoughts?

    Troy

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Without VBA here is a possible solution:
    First I think you should focus your attention on Sheet2 since it has only 30 rows.
    Make a copy of sheet2 worksheet and give it a name like "original data" as a backup
    1 Next, sort sheet2 for Column A (A to Z and smallest to largest)
    2 Create a range name for sheet2 $A$1:$A$30 by example "INDEX"
    3 Next on sheet1 find an empty column Say Aa1
    4 Put the following formula in Aa1
    5 =iferror(vlookup(A1,Index,1,False),"No Match")
    6 Copy formula down for each row in sheet1
    7 Select all the formulas in Col Aa and select a Data - Filter
    Now you can use the filter to create custom reports
    Example to show only items with match put a filter such as <> "No Match" or deselect it from items list
    which will hide all rows that have no match in sheet2
    After the filter in ON you can select the entire visible range and on the Home Ribbon put a fill color. This will only i i impact the visible items.
    You can also "Bold" the visible items if so desired.

    To see all information just clear the filter

    Lastly when a filter is on if you highlight all the visible data and press F5 - Special - Visible Cells Only
    You can then select copy and find a place where you will paste the data and have a static report for your data

    Hope this helps.

    TD

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TD,

    Nicely Done!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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