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

[dpaton05]

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

 

[Michael M]

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

 

[dpaton05]

Thank you Michael

 

[dpaton05]

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

 

[dpaton05]

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

 

[Michael M]

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...tract-data-multiple-excel-files-1-folder.html

 

[Worf]

Hi Dave and Michael

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

https://www.mrexcel.com/forum/excel...-query-modify-update-instead.html#post5341984

 

[sandy666]

or use Power Query aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference