• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compile Questionnaires in one workbook

M

melzjm

New Member
Hi,


Can you give me an idea on how to compile questionnaired in one workbook with the format in the consolidated excel file found in belows


link:https://docs.google.com/folder/d/0B8ipP_XUVnFwRXZ1aHhFYV9PY0U/edit


Thanks.
 
Good day melzjm


As you posted this same question in some one else's post I have removed that post.

It is all ways best not to bury your question in other post as members may not revisit and you will not get help, fresh question = fresh answers :)
 
@melzjm

Please describe clearly your problem.

Do you have every separate sheet for separate person?

How many sheet do you have?

All data in all sheets are same format?


Regards,


Muneer
 
Hi melzjm,


I have been trying to open file but for some unknown reasons it was taking too too much time so here is a suggestion for you! :)


You can always use Google Form to collect Feedback in a excel sheet, If the one that you have uploaded i had been able to download i would have endorsed this idea with much more confidence. :)


If it is a similar one and you want to have a form that can be emailed to filled, let me know, and please see whether this file downloads??


Regards,
 
Hi melzjm ,


Can you check out this file ?


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdGJoLU5XeXNoSE5JQlBkQTFqdkNqN1E


I have done the formulae for column A ; I hope you can use this to put in the formulae in the other cells. If not , please let me know.


Narayan
 
Hi Faseeh,


Thanks for acknowledging. I have separate workbooks for each questionnaire with one sheet each, all have the same format. This questionnaires are sent to me in a weekly basis so I need to add them to a folder and I need to have a master file where all the details can be viewed. Please try the link again. I reuploaded it. Thanks.


https://docs.google.com/open?id=0B8ipP_XUVnFwT1dqdlpKa0lYSVE
 
Hi Narayan,


I tried your suggestion but I get #NAME?

Also, I'd like to inform you guys that the questionaires file name might not be the same or sequential since it will be coming from different departments. The only thing in common is the sheet name and the sheet format.


Thanks.
 
The questionaires will come every 2 weeks, then I'll put in a folder. I was hoping to first run a macro (no idea how to do it :( ) to consolidate all workbooks into one workbook .csv file, then il make a master file with fields referring to the csv file.


So, everytime there is a new questionnaire, the csv rows will be added and the master will automaticall update :(


Is this even possible? or is this a wishful thinking?
 
Hi Melzjm ,


The problem is with Google Docs. Can you download from here and see ?


http://speedy.sh/787YP/Collated-1.xlsx


Note that when you open this file , the Questionaire files should also be open , otherwise you will get a #REF! error display.


Of course , looking at your latest post , the formulae will not work , since I have assumed that the questionnaire files all follow the same name format.


Narayan
 
Hi Narayan,

Thanks for your patience. I triend to do it again with all the questionaires 1-3 open but i get this.Please see & advise what I am doing wrong.


https://www.dropbox.com/s/rdjvufby41nirk6/Screenshot.jpg
 
Hi melzjm ,


Sorry I did not explicitly mention this :


The workbook I uploaded has two named ranges defined , which is the reason for the data you see in I1 , J1 , I2 and J2.


1. File_Name : =Sheet1!$J$1


2. Sheet_Name : =Sheet1!$J$2


You have replaced the above two named ranges with the word Questionaire in your formula :


=INDIRECT("'["&Questionaire&" "&ROW(A1)&".xlsx&]"&Questionaire&"'!"&"B2")


Your formula will work provided you define a named range Questionaire , referring it to : ="Questionaire"


Narayan
 
Hi Narayan,


It worked now! Thanks for the explanation. But then, I also understand that this will only work if the files have the same sequential file name (Questionaire 1, *.2, *.3) and the all the files have to be open at the same time. Which means sending the master file as the summary report will not be a smart move.


With my limited capability, I tried to research more in the forum, found this duscussion:

http://chandoo.org/forums/topic/how-to-compile-the-excel-file (Credits to Debraj Roy) , tried to test the CombinedMultipleFile.xlsm and found out it compiles all sheets into one workbook and everytime a new file is added, I can overried the *.csv file and the content of the new file is added at the bottom of the list. Then I realized, the interval of the cells from one content cell to the next file's content cell is exactly the same, so I made a master file and pointed it to the Combined Excel File.csv.


This work around works for me because:

1. I wont have issues with filenames

2. If I add new files, I only need to run the macro file, then overwrite the csv file, then refresh

3. I only need to have the .csv file open together with the master collated file


I can work with this for now. But , I was wondering again, would it be possible to run the macro on the master collated file, instead of having it on a different file which I would be used cause everytime it runs the Macro, whatever is written in sheet 1 will be cleared.


Any input will be appreciated. Below is the link of the files I have used for testing.

https://www.dropbox.com/s/rvri1187nm3gyb3/CollateQuestoinaires.zip


Let me know your 2 cents. Thanks.
 
Hi


Try this code. I guess you have only questionnaire files in the folder, if not remove all other files from the folder.


Copy paste this code in the Master File and run. Adjust the folder path in the code.

[pre]
Code: 
Sub kTest()

Dim FName   As String
Dim Fldr    As String
Dim i       As Long
Dim ka, k(), n As Long
Dim wbkM    As Workbook
Dim wbkS    As Workbook
Dim wksM    As Worksheet
Dim wksS    As Worksheet
Dim dic     As Object
Dim Hdr, c  As Long

Fldr = "C:Qustionnaires"       '<<< adjust the folder path

If Right$(Fldr, 1) <> Application.PathSeparator Then Fldr = Fldr & Application.PathSeparator

Set wbkM = ThisWorkbook
Set wksM = wbkM.Worksheets(1)

Hdr = wksM.Range("a1").CurrentRegion.Rows(1).Value2
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1

If IsArray(Hdr) Then
For i = 1 To UBound(Hdr, 2)
If Len(Hdr(1, i)) Then dic.Item(Hdr(1, i)) = i
Next
Else
dic.Item("Name") = 1
dic.Item("Age") = 2
dic.Item("Gender") = 3
dic.Item("Religion") = 4
End If
c = dic.Count
ReDim k(1 To 1000, 1 To IIf(c, c, 8))

FName = Dir(Fldr & "*.xls*")
Application.ScreenUpdating = 0
Do While FName <> vbNullString
If FName <> wbkM.Name Then
Set wbkS = Workbooks.Open(Fldr & FName)
Set wksS = wbkS.Worksheets(1)
ka = wksS.Range("a1:c" & wksS.Range("a" & wksS.Rows.Count).End(3).Row)
Set wksS = Nothing
wbkS.Close 0
Set wbkS = Nothing
n = n + 1
k(n, 1) = ka(4, 2): k(n, 2) = ka(5, 2)
k(n, 3) = ka(6, 2): k(n, 4) = ka(7, 2)

For i = 10 To UBound(ka, 1)
If Len(ka(i, 2)) Then
If dic.exists(ka(i, 2)) Then
c = dic.Item(ka(i, 2))
k(n, c) = ka(i, 3)
Else
dic.Item(ka(i, 2)) = dic.Count + 1
c = dic.Count
ReDim Preserve k(1 To 1000, 1 To c)
k(n, c) = ka(i, 3)
End If
End If
Next
Erase ka
End If
FName = Dir()
Loop

If n Then
With wksM
.Range("a1").Resize(, dic.Count) = dic.keys
.Range("a" & .Rows.Count).End(3).Offset(1).Resize(n, dic.Count) = k
.UsedRange.EntireColumn.AutoFit
End With
End If
Application.ScreenUpdating = 1

End Sub
[/pre]

Kris
 
Hi Kris,


I tried the code above! It works perfectly! It captures all the common fields in the questionaires even with different filenames.


But there is a problem, when I add a new questionaire in the folder, then I open the master file and run the macro, it will repeat the original content of the master file and will add new rows for the new data.


Please see example on the link provided.
 
https://www.dropbox.com/s/c0ry0nd65ujb8is/Questionaires.zip


- when I press CTRL + M to run the macro, it adds the new details from the files but it also copies the initial data.


Is it possible to only add the new data? Please assist.

Any help will be greatly appriated. Thanks.
 
Hi


Replace this line


.Range("a" & .Rows.Count).End(3).Offset(1).Resize(n, dic.Count) = k


with


.Range("a2").Resize(n, dic.Count) = k


Kris
 
Hi Kris,


I changed the line and it now updates great. Thank you so much for being so helpful.

What if I have a more fields added to Name, Gender, ...


*** If IsArray(Hdr) Then

For i = 1 To UBound(Hdr, 2)

If Len(Hdr(1, i)) Then dic.Item(Hdr(1, i)) = i

Next

Else

dic.Item("Name") = 1

dic.Item("Age") = 2

dic.Item("Gender") = 3

dic.Item("Religion") = 4

End If

c = dic.Count

ReDim k(1 To 1000, 1 To IIf(c, c, 8))****


is this where I need to make the changes? Thanks.
 
You must log in or register to reply here.
Back
Top