(un)solvable problem in excel ?
 

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

(un)solvable problem in excel ?
 

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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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

"Gary''s Student" wrote in message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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

"Gary''s Student" wrote in message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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

"Gary''s Student" wrote in message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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

"Gary''s Student" wrote in
message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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

(un)solvable problem in excel ?
 

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
--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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

"Gary''s Student" wrote in message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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

"Gary''s Student" wrote in
message
...
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.

--
Gary''s Student - gsnu200725


"/-_-b" wrote:

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"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 26, 2007 2:13 PM
Subject: (un)solvable problem in excel ?


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

--
Gary''s Student - gsnu200724


"/-_-b" wrote:

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