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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

TD,

Nicely Done! :thewave:
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote