Results 1 to 8 of 8

Thread: Extract Data from two excel files on member ID

  1. #1

    Extract Data from two excel files on member ID



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please give me the code to prepare a consolidated report with the Titles of the books of all members as well as a report on individual Member with the Titles of the books.
    The Member ID & Member's Name are in one excel file.
    Book ID and Titles of books are in another Excel file.
    Member ID, Date, Book ID of books either Given by the Friends Circulation Library or returned to the Library and in the last column G for Given and R for Returned are stored in another Excel file.
    Thanks in advance.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    If these are really in two different workbooks and not two different worksheets in the same workbook then will need to know the name of each workbook and the full path to each workbook.

    Also, where is it you want the report?

  3. #3
    D:\Member.xls
    Col A= MemberID ColB= Member'sName
    1234 Mr. D.
    1235 Mr. A.
    1236 Mr. C.
    D:\Books.xls
    Col A= BookID ColB= Titles of books
    101 Adventures
    102 Health Tips
    103 Short Stories

    D:\Transactions.xls
    Col A= MemberID ColB= date ColC= BookID ColD= GR
    1235 10 Jan 2013 103 G
    1234 12 Jan 2013 101 G
    1236 14 Jan 2013 102 G
    1234 16 Jan 2013 102 G
    1235 18 Jan 2013 103 R
    1236 20 Jan 2013 102 R
    1236 20 Jan 2013 103 G
    If the command button to generate one member summary is clicked, I want to display it in a ListView control in an UserForm through VBA.
    If the command button to generate summary of All Members is clicked, I want to display it in a ListView control in an UserForm through VBA.
    In both the displays, the Book Code should not be displayed, but the Title of the Book should be displayed by pulling the corresponding Title from the Books.xls based onn the Book ID in the Transactions.xls.
    I can generate summary just by displaying the Book ID. But I don’t know how to translate the Book ID by getting the corresponding Book Title simultaneously while drawing data from Member.xls as well as Transactions.xls. If I know how to do this, then I can even display the name of the Member in the Summary.
    If the summary is pasted on a Sheet with the name “Report” in the Transactions.xls, then I can populate listview control by pulling data using Selection.Offset. I don’t know much about VBA. So, this method of mine may be right or wrong. Sorry. I need a better suggestion.
    Please help.

  4. #4
    PLEASE NOTE: This is revised query. Kindly ignore the query part given in the previuos post.
    If the command button to generate one member summary is clicked, I want to display it in a ListView control in an UserForm through VBA.
    If the command button to generate summary of All Members is clicked, I want to display it in a ListView control in an UserForm through VBA.
    In both the displays, in addition to the Member ID, the Name of the Member and instead of the Book ID, the Title of the Book should be displayed.
    If the summary is pasted on a Sheet with the name “Report” in the Transactions.xls, then I can populate Listview control by pulling data using Selection.Offset. (A better suggestion is appreciated.)
    Please help. Thanks in advance.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    pmyk,

    Somebody here should be able to suggest something if you state what it is you are trying to do rather than how you think it should be done.

    Is it not possible that Members, Books and Transactions be sheets in the same workbook?
    Where are the command buttons that generate the listviews?
    With nothing common in Members and Books, is the Transaction sheet being filled in manually?

    Uploading a sample workbook with what you have now would go a long way in promoting assistance.

  6. #6
    Thanks for the response.
    I searched the web and got some idea to use vlookup.
    It is working.
    Thanks once again.
    I am still learning.

  7. #7
    Cross posted here
    Thanks for the suggestions. This is SOLVED.

  8. #8
    For various reasons the Members, Books and Transactions are not sheets in the same workbook.
    The Transactions file gets data through a VB programme. But the Link where solution is mentioned gives just an idea to extract data from different files based on ID. Thanks for your help.

Tags for this Thread

Posting Permissions

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