Consolidating data from multiple worksheets

[Derek F]

Note: I'm a Mac user but could use a PC if needed.

I am trying to analyze patient satisfaction surveys and have been given an excel file. Unfortunately, the way the program exports results is not user friendly (at least to me). I have a workbook with over 1400 worksheets (tabs) and am looking for a formula, VBA code, or any other way that will allow me to collect the data from ALL worksheets in one table. 



A couple things to know:
1. Each worksheet is one person's survey

2. Each worksheet has a numeric name (example: 2321123) and is not just "sheet 1"

3. Each worksheet name is not sequential but do seem to be in chronological order

4. Each worksheet is set up the exact same layout (cells) for all information EXCEPT Questions. Patient Name, City, Date of Service, Provider, etc are all in the same cell.

5. Each worksheet may not have the same survey questions. Because of this each question may not be in the same cell.

6. Some cells may contain a word and a number. I will eventually separate these. For example a cell may have "Visit # 555555". When it's all done I will only have "555555" in the cell.

7. Each worksheet is not set up as a table

8. I would like to have all data in one Worksheet and in one table.

9. I've tried to use "='555555'!B5" to reference each cell. This worked great and I could easily set up formulas to create a table. The downfall with this is I could not auto fill when I went to reference the worksheet. I'm assuming it is because each worksheet has a name and are not sequential (although chronological). 

10. I've included an example

Can anyone help??

[#2]

I think it is difficult as the cell contains the title and the datum as well. 

I did not have time to write a detailed VBA for you. Attached is a simplified case. Hope that you understand it and make the code for yourself. The key is to list the field in <Info> and specify the range of it. As some field contains "title", you have calculate the len of "title" and listed in the column M. 

There is also another module to list all patients' sheetname. 

[#3]

Thank you for your reply. Unfortunately, I've never been exposed to VBA scripting so this doesn't make much sense. I've been looking at your example and trying to work it out, but I'm not comprehending it as of now.

I'm sure you're busy, but if you have any time to do the scripting on the excel file I attached, I would really appreciate it.

[#4]

Mr. Derek

As Mr. Chan noted - the  fields and data are in one cell - if this is the case for all your worksheets (and merged cells) - it might be difficult to manage. I came up with a similar situation but with separated fields and data. Maybe you can come up with something out of it (or someone) ..

somewhat crude but I think it worked.

Hope that this may help you - somehow..

thanks

[#5]

press ALT F8 then run the macro

[#6]

if we are to use the original worksheet of field and data in one cell -- we have to use lots of trimming of the string:

ex B7= Visit #: 55555

instead of just using Range("B7") - we have to use Right("B7",len("B7")-9) and so on...

also I noticed in your "Ideal Table" that there are data not present in the survey results and vice versa...

thanks..

[#7]

attached is another version of the previous file - this time with the survey comment.

at the top RIBBON -- click DEVELOPER then Macro then Run 

or press ALT + F8 then run the macro

hoping this could help you.

thanks

[#8]

I need to consolidate from several workbooks into one.  data is in same place with same headings in each workbook.  Can someone send me a formula?

[#9]

see below sites, hope it helps..
https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/
https://support.office.com/en-us/article/create-an-external-reference-link-to-a-cell-range-in-anothe...