Compiling info from two types of surveys

[dreddster]

Hey guys,

I want to compile the information from a survey (10k surveys).
There are two types of surveys with just 1 sheet.

I have put all the surveys in a folder and this is the macro I will use to run it on all files

Please Login or Register  to view this content.

I tried completing the copy part but couldn't make it work.
What I need the macro to do is:
If the worksheet on the survey is called First -> Copy content from C6 to Column B on the combined workbook, copy content from B6 to column C, and then copy for each row the range of B:C, one next to another on the combined workbook. For example B10:C10 to columns D:E, B11:C11 to columns F:G, B12:C12 to columns H:I. There are like 50 questions. The last row is 257 (there are hidden rows, that I don't need copied).

If the worksheet on the survey is called Second -> Copy content from B3 to Column B on the combined workbook. Then copy content from B to column C on the combined workbook, and range column C:P to DD:DQ on the combined sheet. And do that on all rows because this Survey B will have multiple rows filled.

Any help will be greatly appreciated.

I have attached the files in case you want to see how it will look like.

Thank you very much in advance!

[dreddster]

Hey Guys,

Is it hard what I am trying to achieve? If it is maybe I can do the conversion beforehand with macro on the survey sheets first in some way and then combine it?
Or is there some other solution?

[jaslake]

Hi dreddster

It would be helpful if you would put a small amount of Data in each of your Survey files, then, take the Data from Survey Files and Populate the Combined File with expected outcome from that Data.

[dreddster]

Hello Jaslake,

I have updated the files as per your suggestion.

Small change on the copy part on Survey B.

If the worksheet on the survey is called First (which corresponds to Survey A) -> Copy content from C6 to Column B on the combined workbook, copy content from B6 to column C, and then copy for each row the range of B:C, one next to another on the combined workbook. For example B10:C10 to columns D:E, B11:C11 to columns F:G, B12:C12 to columns H:I. There are like 50 questions. The last row is 257 (there are hidden rows, that I don't need copied).

If the worksheet on the survey is called Second (which corresponds to Survey B) -> Copy content from A to Column B on the combined workbook. Then copy content from B to column C on the combined workbook, and range column C:P to DD:DQ on the combined sheet. And do that on all rows because this Survey B will have multiple rows filled.

Thank you!

[jaslake]

Hi dreddster

Will Book SurveyB EVER have BOTH Worksheets "First" and "Second" or ONLY one or the other? Of course, the same question applies to Book SurveyA.

[dreddster]

Hey Jaslake,

No, I have decided to use the worksheet name as a guidance to the macro to decide whether it is Survey book A or B respectively. Only 1 sheet per workbook, always First for A, Second for B

[jaslake]

Hi dreddster

You indicate there are 50 questions. This Code works for the limited questions you have in the Sample Survey Files. You can modify the Code for additional questions and deposit the Data where you like.

If you need assistance with that let me know.

Please Login or Register  to view this content.

[dreddster]

Hey Jaslake!

It works great! Exactly what I needed! I was almost starting to lose hope that it is too hard!
However I think I overestimated myself. There are actually three surveys, third is exactly like the second, and I thought I can just replicate the second one. However now I get an error Else without IF at the second ElseIF. I just read about these IF-THEN-ELSE and can't figure out the error. Here is how is the macro I tried with:

Please Login or Register  to view this content.

Thanks for all of your help!

[jaslake]

Hi dreddster

Add the Line of Code as indicated

Please Login or Register  to view this content.

[dreddster]

Awesome!! Thank you so much!

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.