How to Only Show Rows That Match Specific Data

[jacom]

Hi-- I'm a newbie and a first time poster. Thank you for considering helping me.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Here's the problem:<o></o>
<o></o>
Workbook #1 has a single column of account numbers- about 16,000 unique records.<o></o>
<o></o>
In Workbook #2, each row/record has multiple columns of data (account number, name, address, etc.). This workbook has about 25,000 rows- of which some or all of 16,000 account numbers from Workbook #1 appear.

I'm looking for a way to ONLY show the rows/records in Workbook #2 (based on account number) that appear in Workbook #1 and hide the rest.<o></o>

Thanks so much!
Mark...

 

[Akashwani]

Hi Mark,

Welcome to MrExcel.

When you say Workbook 1 & Workbook 2 do you really mean 2 separate Workbooks or 2 sheets within the same Workbook?

If you have 2 separate Workbooks then you will need add a helper column and then count if the value in Workbook2 is in Workbook1, something like this.....

=SUMPRODUCT(--(('C:\Documents and Settings\Ak\Desktop\[Workbook1.xls]Sheet1'!$A$2:$A$20)=A2))

Change this Sheet1'!$A$2:$A$20 to suit your sheet name and range in Workbook1
Change this =A2 to suit the first cell in Workbook2 with the Account Number in.
You will need to put the full file path, this will work when Workbook1 is closed.

Next you will need some VBA code in Workbook2 to hide the rows that = 0..

Sub HideRows()
On Error Resume Next
With Range("E2:E800") 'Your Helper Column, change the range to suit yours
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Take a look here.....
http://www.mrexcel.com/archive/VBA/8558d.html
http://www.mrexcel.com/forum/showthread.php?t=544052

Good luck, I hope this helps to point you in the right direction.
If nothing here is of use to you, it has at least bumped you back up!

Ak

 

[jacom]

Thank you very much. I'm not very experienced with VB, but I'll give this a shot and post my results.

I appreciate you taking the time to help!
Mark...