(un)solvable problem in excel ?

[/-_-b]

Hey guys,

I have 2 xls files from one of my web quizzes (both exported from mysql in
excel format), and i'm trying to merge them in one file.

1st file example:
--------------------------------------------------
| A | B |
--------------------------------------------------
| 1 | Who was the first man on the Moon ? |
--------------------------------------------------
| 2 | The fastest animal on the planet is.. |
---
and so on...
(A column - question no., B col. - question)

2nd file example:
-------------------------------------
| A | B | C |
-------------------------------------
| 1 | John Glenn | 0 |
-------------------------------------
| 1 | Neil Armstrong | 1 |
-------------------------------------
| 1 | Yuri Gagarin | 0 |
-------------------------------------
| 2 | Turtle | 0 |

-------------------------------------
| 2 | Cheetah | 1 |
----
and so on...
(A column- question no. answers refer to, B- answers, C- 0 is false answer,
1 is true)

So i would like to merge those 2 tables in one following this sequence :
| A | B | C |
----------------------------------------------------
question no. | question | |
----------------------------------------------------
| | answer 1 | true/false|
----------------------------------------------------
| | answer 2 | true/false|
----------------------------------------------------
| | answer 3 | true/false|

----------------------------------------------------
question no. | question | |
----------------------------------------------------
| | answer 1 | true/false|
----------------------------------------------------
| | answer 2 | true/false|
----------------------------------------------------
| | answer 3 | true/false|
.....

I hope someone can help me with this one ?

Thanks in advance !

Marko

 

[Guest]

First put the questions and answers in two different sheets in the same
workbook and call the question sheet "q" and the answer sheet "a"

Include a blank sheet called "qa"


Then install & run:

Sub marko()
'
' gsnuxx
'
k = 1
Set q = Sheets("q")
Set a = Sheets("a")
Set qa = Sheets("qa")
j = 1
For i = 1 To 1000
n = q.Cells(i, 1).Value
qa.Cells(k, 1).Value = n
qa.Cells(k, 2).Value = q.Cells(i, 2).Value
k = k + 1
m = n
Do
m = a.Cells(j, 1).Value
If m <> n Then
Exit Do
End If

If m = "" Then
Exit Sub
End If
qa.Cells(k, 2).Value = a.Cells(j, 2).Value
If a.Cells(j, 3).Value = 0 Then
qa.Cells(k, 3).Value = "False"
Else
qa.Cells(k, 3).Value = "True"
End If
k = k + 1
j = j + 1
Loop

Next
End Sub

 

[/-_-b]

Thank you very much, it works, almost... actually the script stops when it
cannot find next question no... I got 3000 questions, but it stops on number
22, because the next question number in the table is 24.
Is that repairable ?

Thanks in advance !

Marko

----- Original Message -----
From: "Gary''s Student" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: RE: (un)solvable problem in excel ?

 

[Guest]

Hi Marko:

First I am glad we beginning to make progress. It is "fixable"

I am assuming that there are at least 2 answers for each question and that
the questions and answers are in the same order? Also the sub will stop when
it encounters the first blank cell in column A of the a-sheet?

Check back later.

 

[/-_-b]

Some questions are missing, but the answers for particular non-existing
questions are there ... i think the script couldn't figure the sequence.
After I removed redundant answers, all worked great... until it stopped on
the next missing question. Any way to bypass those missing q-s automatically
?

Thanks,

Marko

 

[Guest]

Here is the revised code:

Sub marko()
'
' gsnuxx
' rev 1
'
k = 1
Set q = Sheets("q")
Set a = Sheets("a")
Set qa = Sheets("qa")
nq = q.Cells(Rows.Count, "A").End(xlUp).Row
na = a.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To nq
n = q.Cells(i, 1).Value
qa.Cells(k, 1).Value = n
qa.Cells(k, 2).Value = q.Cells(i, 2).Value
k = k + 1
For j = 1 To na
m = a.Cells(j, 1).Value
If m = n Then
qa.Cells(k, 2).Value = a.Cells(j, 2).Value
If a.Cells(j, 3).Value = 0 Then
qa.Cells(k, 3).Value = "False"
Else
qa.Cells(k, 3).Value = "True"
End If
k = k + 1
End If
Next
Next
End Sub



Here are sample questions:

1 first man on the moon
2 fastest animal on earth
3 prettiest flower
5 best dog
9 Dumbest Stooge


Here are sample answers:

1 John Glenn 0
1 Neil Armstrong 1
1 Yuri Gagarin 0
2 Turtle 0
2 Cheetah 1
3 Lily 0
3 Rose 1
5 Pug 1
5 Beagle 0
5 Collie 0
8 Jordan 0
8 Melinda 1
8 Blake 0
9 Larry 0
9 Moe 0
9 Curley 1
9 Shep 0


As you can see, there are gaps in the numbering and question #8 (who was the
best American Idol) is missing. The output was:

1 first man on the moon
John Glenn FALSE
Neil Armstrong TRUE
Yuri Gagarin FALSE
2 fastest animal on earth
Turtle FALSE
Cheetah TRUE
3 prettiest flower
Lily FALSE
Rose TRUE
5 best dog
Pug TRUE
Beagle FALSE
Collie FALSE
9 Dumbest Stooge
Larry FALSE
Moe FALSE
Curley TRUE
Shep FALSE

Once again, if problems occur, update the post.

 

[/-_-b]

This works perfectly, thank you very much !

There are few other things i wanted to do,cosmetics really, but I didn't
figure out how...
1) questions in bold
2) a), b), c), d)... before the answers
3) blank row indent between 2 questions

So if and when you find some time to add few more snippets, i'd be really
thankful.

Cya around,

Marko

 

[Guest]

Question in bold is only a single additional line of code:

Sub marko()
'
' gsnuxx
' rev 2
'
k = 1
Set q = Sheets("q")
Set a = Sheets("a")
Set qa = Sheets("qa")
nq = q.Cells(Rows.Count, "A").End(xlUp).Row
na = a.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To nq
n = q.Cells(i, 1).Value
qa.Cells(k, 1).Value = n
qa.Cells(k, 2).Value = q.Cells(i, 2).Value
qa.Cells(k, 2).Font.FontStyle = "Bold"
k = k + 1
For j = 1 To na
m = a.Cells(j, 1).Value
If m = n Then
qa.Cells(k, 2).Value = a.Cells(j, 2).Value
If a.Cells(j, 3).Value = 0 Then
qa.Cells(k, 3).Value = "False"
Else
qa.Cells(k, 3).Value = "True"
End If
k = k + 1
End If
Next
Next
End Sub