Results 1 to 8 of 8

Thread: How do you query in excel? What would be the best program to complete this in?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default How do you query in excel? What would be the best program to complete this in?

    I have spreadsheets called allocation sheets. These are yearly documents and each row is a service instance.

    The row headings are:

    • Date
    • Purchase order #
    • Req #
    • Child Name
    • Service
    • Requesting Organisation
    • Caseworker Name
    • Price ex. GST
    • GST
    • Price inc. GST
    • Allocated to
    • Date report received
    • Date report sent
    • Allocated by
    • Report sent by
    • Report sent by


    There will be x number of allocation sheets within the same folder, all relating to different years. They will have the same name except the year will be different. The allocation sheet is broken up into sheets for the months of the year.


    My supervisor wants to be able to search for things such as:

    • All children that have had a certain service
    • Dollar figure in regards to a certain caseworker and a certain child
    • Dollar figure that has been applied to a set Purchase order # over a certain time frame, (may be in the year of the allocation sheet of might span over several allocation sheets or years).
    • Any combination of the list above.


    What would be the best program to achieve this in. Access was my first choice but the allocation sheets are going to be constantly updated. Would I create queries in access and have them reference the external excel files as the files will always be up to date or do I have to import the data from excel into access before I ran the query?

    Thanks for your help guys,
    Dave

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    With the amount of data that may be involved, I would think Access would be the choice
    Have a read here on Exporting data to Access....step by step !!

    https://www.wikihow.com/Import-Excel-Into-Access
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    Thank you Michael

  4. #4
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    I have a more accurate description of the requirements of this project now. This will help me better describe it:


    1. Entries will be copied to the allocation sheets and may need to be edited during the during the month.
    2. However, on the 25th of each month, an invoice will need to be sent for the current month, made by incorporating several columns from the allocation sheet. Once that invoice has been sent, the allocation sheet for that month will become static and will not be changed.
    3. I want to have a search field that will search through every row in past allocation sheets and return the entire row if a match is found somewhere in that row.
    4. I only want to search through allocation sheets that have already become static.


    With my new understanding, does this look like it could be accomplished in excel?

    Thanks
    Dave

  5. #5
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    I have a further understanding of what is needed.


    1. I want to search through the workbooks and find rows that have a match somewhere in the row. If it does, I want that entire row copied to a new workbook.
    2. The workbooks have not got a consistent format. It has been the same up until this year, where I have changed it, but the columns have mostly similar information. It doesn't matter that the same information might be in different columns for different rows, I just need that row copied to a new document if there is a match in that row.
    3. The filenames are not consistent, however, they all will have the words "Work Allocation" somewhere in the file. They are stored in the same folder. The sheet names up until this year were month year, for instance, "March 2018" and this year have started using just the month name.
    4. The first sheet in the workbook is not consistent over the workbooks but I need to search through every sheet in the document.
    5. I need all the rows that have a match from every document put into one sheet in the new document. This will allow me to see a summary of information regarding to the search query.


    Thanks
    Last edited by dpaton05; Sep 19th, 2019 at 07:45 PM.

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    All I can suggest is looping through the directory, extracting the relevent worksheet and putting them all into 1 workbook.
    You could then extract to a "Summary" sheet from there.
    there is a looping code that you may be able to modify here...

    https://www.mrexcel.com/forum/excel-...-1-folder.html
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,726
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do you query in excel? What would be the best program to complete this in?

    Hi Dave and Michael

    A fast tool to query Excel sheets is ADO & SQL. See below thread:

    https://www.mrexcel.com/forum/excel-...ml#post5341984
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How do you query in excel? What would be the best program to complete this in?

    Last edited by sandy666; Sep 21st, 2019 at 06:59 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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