Compare values and move data to new sheet.

grafiksinc · #1 (**permalink**) 01-25-10, 01:56

Hello all,

This may be simple and I may be over looking something. But here goes.

I have a spreadsheet that has 3 sheets,

I came up a procedure that will display the matches from
AAUIC 68470 Main Report - Sheet and the Comments68470 - Sheet.

So, if the matches are in both sheets the resulting values will be in Column A Sheet 3.

Now what I want to do is the Comments from the Comments68470 - sheet to be listed with the correct number in Sheet three.

For example the below procedure will show this result In column A on Sheet 3:
-------------------

Column A - Sheet 3
33055
11117
11708
23932
26015
26020
26036

Now the the Comments68470 sheet has user commentsin them. And I would like to have them comments listed on sheet 3 but with the correct number.
So, if the value of column A in sheet 3 is = to the value of column B:Range then comments wuold be displayed. This is what i am stuck on. If I need to be more clear please let me know,

Thanks In advance

*****************
Start Code
*****************

Sub Match()
Dim rng_1 As Range
Dim rng_2 As Range
Dim rng_3 As Range

Dim rngRef_1 As Range
Dim rngRef_2 As Range
Dim rngRef_3 As Range
Dim intRowNum As Integer

Worksheets("Sheet3").Range("A1:A1000").ClearConten ts

Set rng_1 = Worksheets("AAUIC 68470 Main Report").Range("B6:B83")
Set rng_2 = Worksheets("Comments68470").Range("B2:B30")

For Each rngRef_1 In rng_1
For Each rngRef_2 In rng_2

If rngRef_1.Value = rngRef_2.Value Then
intRowNum = intRowNum + 1
Worksheets("Sheet3").Cells(intRowNum, 1).Value = rngRef_1.Value

If rngRef_2.Row = rng_3.Row Then
Worksheets("Sheet3").Range("B1:B30").Value = rng_3.Value

End If
End If

Next
Next

'Allow for 30 Matches
Worksheets("Sheet3").Range("A1:B30").Columns.AutoF it

End Sub
***************
End Code
***************

Colin Legg · #2 (**permalink**) 01-25-10, 07:26

Hi there,

Unless you are automating the process, I don't think there's any need to use VBA here.

To transfer all of the matches you could use the advanced filter.
To pull in the comments from the Comments68470 sheet you could use VLOOKUP().

If you are automating the process then you could use VBA to perform the above actions. Generally speaking, automating the built-in utilities such as the advanced filter is much faster than using nested loops and the such.

If you need a demo, if you give me some (dummy) sample data in an attached workbook, I could put something together for you?

Hope that helps...

grafiksinc · #3 (**permalink**) 01-25-10, 19:11

Colin,

Yes, my goal is to automate this process. This spreadsheet is a result of an Access database application I wrote for the user. The user pushes a button in the app. and this spreadsheet is generated. Then I recorded a Auto_Open() macro to format the spreadsheet upon opening it.

The part I posted here is just one small piece I wrote.

So, thats the back ground

I did put together a temp file focused on this post's question, see the attached.

Thanks for the help!,
Tommy

Colin Legg · #4 (**permalink**) 01-26-10, 06:47

Hi,

Sorry, but I am unable to unzip and view the attachment.

grafiksinc · #5 (**permalink**) 01-26-10, 18:38

That's wierd. Ok i am trying this again.

Colin Legg · #6 (**permalink**) 01-27-10, 06:27

Hi,

Okay, I can view it now.

Quote:

Yes, my goal is to automate this process..... The user pushes a button in the app. and this spreadsheet is generated. Then I recorded a Auto_Open() macro to format the spreadsheet upon opening it.

I'll just quickly mention that Auto_Open() has been superceded by using the Workbook_Open() event handler. Whilst Auto_Open() would go in a standard code module, the Workbook_Open() event handler would go in the ThisWorkbook class module. There are some subtle differences between the two, so there are a few occassions when using Auto_Open() is perfectly legitimate but, as a rule of thumb, Workbook_Open() is the way to go. If you want me to list some differences then just ask.

Quote:

This spreadsheet is a result of an Access database application I wrote for the user.

Right, I have a small confession to make: I know hardly anything about databases. That's one of the reasons I joined this forum - to learn about them - but I've lost my way a bit in the Excel section! However, my impression is that databases can do this sort of query quite well - so couldn't you extract the necessary records within the database before exporting, rather than doing it in Excel? Just a thought.

So anyway, here's one way of doing it using Excel VBA.

To get the advanced filter working, we have to add headers to the columns. In the attached example I have used "Numbers" and "Comments".
We also assume that none of the cells in the criteria range are empty.

Code:

    Dim rngCriteria As Range, rngCopyTo As Range, rngToFilter As Range
    
    With Worksheets(1)
        Set rngCriteria = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp)) 'this must not contain blanks
    End With
    
    Set rngToFilter = Worksheets(2).Range("B:L")
    Set rngCopyTo = Worksheets(3).Range("A1:k1")

    rngToFilter.AdvancedFilter _
                Action:=xlFilterCopy, _
                CriteriaRange:=rngCriteria, _
                CopyToRange:=rngCopyTo, _
                Unique:=False

You don't say which column on sheet3 you want the comments in, so I've left them in Column K.

Hope that helps...

grafiksinc · #7 (**permalink**) 01-27-10, 19:52

Colin,

First off, thanks for your help. I will work this and let you know the result.
I had no idea about the Workbook_Open() I wonder if this could correct one of my other issues I was trying.

In my earlier post I mentioned that the user presses a button in my app and the spreadsheet was created right, Well, in order for the Auto_Open()macro to run the user has to open the file or "Double-click" the file.

What I wanted to do was, when the user presses the button the spreadsheet would be created and then open automatically. But every time I did this in Access VBA the macro in Excel would not run.

In other words after the button is pressed the file is created and then opened the Auto_Open() macro never ran. So with that I am curious if by changing from Auto_Open() to Workbook_Open()if that may correct that issue. I will do some testing and let you know.

Now, it may be better if I explain the process and why I have not thought of a way to set things straight in Access yet. Sometimes talking it out may help.

Process:

1. User gets a .xls sent to them via email. It has about 60,000 + records that are just listed. The user needs to go through these and separate them for their own requirements. This happens monthly.

2. The Access application imports the data into TABLE1.
This table is over written on every import because after the data has been separated and distributed there is no need to keep a history of this table. And the next month an entire new set of data will go through the same process.

Besides keep this many records over time the DB will grow substantially.

3. After the data is imported the user has worked with me to specify the criteria they need for the reports and such. Therefore the user can easily view/edit the data separated as the needed.

4. At this point the user does make comments on some records. This is the stuff that I need to keep for historical purposes. After a comment is made the user clicks a "Save" button. These comments are saved in TABLE2. So, if a comment is made I copy the entire row from TABLE1 and place it in TABLE2.

5. The user has requested to have the final report exported in to excel. This is easy enough it uses a SQL statement in VBA to query what you need.

My first problem was the data that is collected has no primary key Only things like Names but that is not a good column for that. In the end I could not think of a query that shows the criteria for the report every month. Which is:

Show all the separated "Criteria given by user" records in the report.
Then on the same row show the comments inputted for the records that were edited that month.

See in my two Tables

TABLE1 has all records right
TABLE2 has ONLY the records with comments

So my dilemma is if there is no unique identifier between the two TABLES how can I create a query to display what I am trying to do in excel.

So, I decided to take it in to excel.

I hope this makes sense to you.

Colin Legg · #8 (**permalink**) 01-28-10, 07:45

Hi,

Quote:

First off, thanks for your help. I will work this and let you know the result.
I had no idea about the Workbook_Open() I wonder if this could correct one of my other issues I was trying.

In my earlier post I mentioned that the user presses a button in my app and the spreadsheet was created right, Well, in order for the Auto_Open()macro to run the user has to open the file or "Double-click" the file.

What I wanted to do was, when the user presses the button the spreadsheet would be created and then open automatically. But every time I did this in Access VBA the macro in Excel would not run.

In other words after the button is pressed the file is created and then opened the Auto_Open() macro never ran. So with that I am curious if by changing from Auto_Open() to Workbook_Open()if that may correct that issue. I will do some testing and let you know.

Yes, absolutely it will. One of the features of Auto_Open() is that it will not run if you open the workbook programmatically. Workbook_Open() will run provided that Excel.Application.EnableEvents is set to True. This is one of the differences I was referring to earlier. Both, of course, require macros to be enabled.

As an aside, if you want to keep the Auto_Open() procedure, you can call it once the workbook has opened by using the Workbook object's RunAutoMacros() method.

Quote:

Now, it may be better if I explain the process and why I have not thought of a way to set things straight in Access yet. Sometimes talking it out may help.

As I confessed earlier, I'm no good at all with Access so I really can't help with this. If you post a question in this forum's Access board I'm sure they will be able to assist you?

Hope that helps...

grafiksinc · #9 (**permalink**) 01-31-10, 20:17

Colin, the macro you wrote for me. Worked like a charm. However I did find a way to get my data sorted out in Access before I export it to excel.
So, I will keep your macro for another day!

Also the WorkbookOpen() fantastic! works great for the users.

Again thanks. Hopefully one day I can repay the favor.