Reconciling Multiple Tables of Data Efficiently

Quick Update

I currently have all documents I need saved as separate sheets in a single document. What would be ideal at this point is a formula (maybe some kind of vlookup?) That searches for all instances of a particular value (in this case, an exact-text match of an email address) across multiple sheets. Does this exist?

Thanks!

Hello there,

What are you going to use to search for between the workbooks? By your post it seems like you maybe using e-mail addresses. If this is the case what column will the e-mail addresses be in in each workbook?

Below is the code that will hide the rows that do not contains data for you assuming that data appears in column A.
You'll need to change the text in red to your closed workbook's filename.

Sub test()
Dim c As Range
Dim LR As String
Dim B1 As Workbook, B2 As Workbook

Set B1 = ThisWorkbook   'set variable B1 equal to the active workbook

With B1.Sheets(1)   'with the active workbook's first worksheet
    HideRows    'run the HideRows macro
End With    'end with this workbook

'change the file path to your workbooks file path
'to get the file path enter into a blank cell =CELL("filename")

'opens other workbook
Application.Workbooks.Open Filename:="C:\Documents and Settings\rvasquez\My Documents\Example Spreadsheets\TestFolder3\Book2.xls"

Set B2 = ActiveWorkbook 'sets B2 to newly opened workbook

With B2.Sheets(1)   'with the first worksheet in B2
    HideRows    'run the hiderows macro
End With    'end with the opened workbook

End Sub

Sub HideRows()
Dim LR As String, c As Range
 LR = Range("A6555").End(xlUp).Row  'LR is equal to the last row that contains a value in column A

    For Each c In Range("A1:A" & LR).Cells  'loop through cells in column A from row 1 to LR
        If c.Value = vbNullString Then  'if the current cell in the loop is blank then
            c.EntireRow.RowHeight = 0   'hide the row
        End If  'end if
    Next c  'move to next cell in the loop
End Sub

To insert this code into your workbook

1. Open the workbook that you want to insert the code into and close the other.
2. Press Alt+F11
3. Press Alt, then press i and finally press m (individually not all at the same time)
4. This should add a Module to your project
5. copy and paste the above code into the blank space provided, anything that appears in green is a comment meant to help you understand the code.
6. Close out of Visual Basic
7. Press Alt+F8
8. Select the test macro and then select Run

Originally Posted by rvasquez

What are you going to use to search for between the workbooks? By your post it seems like you maybe using e-mail addresses. If this is the case what column will the e-mail addresses be in in each workbook?

At the moment, all documents have been consolidated into separate sheets in one document. Does that change anything?

To answer your question, emails are in column G in the master list of contacts from my company, column L for sheets representing leases from March through June (4 sheets total) at the company that hired us, and column O for the month of July. The sheet for July was formatted differently than the other months, which explains the location discrepancy. Just to reiterate, what I need to do is search through Sheet 1 and highlight entries that match emails/names/numbers found in sheets 2-6.

Originally Posted by rvasquez

Below is the code that will hide the rows that do not contains data for you assuming that data appears in column A.
You'll need to change the text in red to your closed workbook's filename.

To insert this code into your workbook

1. Open the workbook that you want to insert the code into and close the other.
2. Press Alt+F11
3. Press Alt, then press i and finally press m (individually not all at the same time)
4. This should add a Module to your project
5. copy and paste the above code into the blank space provided, anything that appears in green is a comment meant to help you understand the code.
6. Close out of Visual Basic
7. Press Alt+F8
8. Select the test macro and then select Run

I knew I forgot something into the OP! I'm running a Mac... how does that change things?

Originally Posted by rvasquez

Yes it does, try this code:

To insert this code (at least on my computer, Windows)
1. Press Alt+F8
2. Clear the macro name field
3. Type the word LpWorksheets
4. Select the Create option
5. In between the Sub LpWorksheets and End Sub copy and paste the above code
6. Exit out of Visual Basic
7. Press Alt+F8
8. Select the LpWorksheets Macro and select run

I'm not quite sure how this changes things I've never really worked with a Mac I'm sorry.

I tried to find a solution on google, but I'm not sure what exactly I'm looking for. What is this coding process called? I searched for "excel mac visual basic editor tutorial" and came up with nothing helpful.

Originally Posted by rvasquez

It would be easier to help you if you could please provide an example workbook that mimics what you are trying to achieve. It's hard to guess what columns you will be searching in and where to look for information.

If you could please provide a sample workbook that shows the current format of your data with dummy data that would be great.

Please find a very rough mock-up of my current file attached here: Workbook Solutions 8.14.12.xlsx

As you can tell, at the moment I've written a quick vlookup, which is better than nothing. It's still pretty lackluster to think about all the thousands of copy/paste actions I'll need to do to get this project finished like this. Any thoughts?

Thanks again!

Here's my latest idea.

Excel Sorting Question 8.15.12.xlsx

Basically, I'd like to know if it's possible to take two separate tables of data and sort them in such a way that matches appear on the same row, while non-matches are placed into their own row. It seems like something that would be a simple feat with the right code, I just don't know it!

Any help is greatly appreciated.

Latest Update:

I'm confident that I can get by with a VLOOKUP formula, but I'm not sure how it needs to be written.

The latest issue is that (for whatever absurd reason) all cells containing an email address in Table 2 begin with three spaces (e.g. " 123@email.com").

If I can figure out some formula that will automatically delete all of these spaces, I think I can do what I need to do. I've tried a LEFT formula to pull everything after those initial three spaces, but the formula isn't working.

Question: Is there a formula to remove the three blank spaces from the beginning of these cells?

@Richard:
I've tried the TRIM function already with no results. I've also tried CLEAN. I've even tried LEFT and RIGHT to try and extract the emails into another cell, but none of it is working.

Here is a copy/paste from the workbook. I don't know if this will help you, but it's all I've got to offer.
\/
***4/12/2012
/\
I even tried finding something online that would allow me to figure out which ASCII code the spaces are, then targeting that specifically with one of the above formulas. Dead ends at every turn.