Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Question Unanswered: Compare values and move data to new sheet.

    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
    ***************

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Thumbs up

    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
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

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

  5. #5
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14

    Trying again

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

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Okay, I can view it now.

    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.
    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...
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14
    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.
    Tommy
    Grafik Interfaces
    www.grafiksinc.com

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    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.

    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...
    Last edited by Colin Legg; 01-28-10 at 08:05.

  9. #9
    Join Date
    Nov 2009
    Location
    Japan
    Posts
    14
    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.
    Tommy
    Grafik Interfaces
    www.grafiksinc.com

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Quote Originally Posted by grafiksinc View Post
    However I did find a way to get my data sorted out in Access before I export it to excel.
    Great stuff - that definitely sounds like the better option.

Posting Permissions

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