Q: macro - enumerating in excel

[Elvis]

Hi all,

this problem to all of you may look trival, but I need some help with this.

I got the sheet in excel looking like this :

A B
Question
a) answer 1
b) answer 2
c) answer 3
d) answer 4

Question
a) answer 1
b) answer 2
c) answer 3
d) answer 4

and so on..

I need to put the numbers in the row A so they are in front of each
"Question". They should start with 1. and finish with the last "Question" in
the table.
The number of answers differ, there are 4 or 5 answers for the each
question.

Is this doable ?

Thanks in advance !

Elvis

 

[ker_01]

Although this could be done with a macro (programming), I think you can
accomplish your goal much more easily with a formula.

put a 1 in column A next to your first question, then put this formula in
the next row in column A and copy it all the way down. This assumes your
first question starts in B1 (so this formula would be in A2); adjust the row
numbers accordingly.

=IF(B2="Question",MAX(B$1:B2)+1,"")

HTH,
Keith

 

[Elvis]

Thanks for your answer, but the problem is - the contents of the "Question"
line is not a "Question" but it varies, just like all the answers too

ex.

A B
Which is the deepest loch in Scotland:
a) Loch Lomand
b) Loch Ness
c) Loch Morar
d) Loch Smith

What was the first of Earth's supercontinents:
a) Pangaea
b) Gondwanaland
c) Rodinia
...

I hope I explained the problem better now

 

[Elvis]

Thanks for your answer, but the problem is - the contents of the "Question"
line is not a "Question" but it varies, just like all the answers too

ex.

A B
Which is the deepest loch in Scotland:
a) Loch Lomand
b) Loch Ness
c) Loch Morar
d) Loch Smith

What was the first of Earth's supercontinents:
a) Pangaea
b) Gondwanaland
c) Rodinia
...

I hope I explained the problem better now

 

[ker_01]

My apologies for not fully understanding.

Ok, that complicates it, but not terribly. It sounds like you want to insert
a question # everywhere that the cell next to it is (a) not blank, and (b)
doesn't have a second character of ")"

=IF(AND(B2<>"",mid(B2,2,1)<>")"),MAX(A$1:A2)+1,"")

(also corrected the Max part of the expression, which needs to reference col
A not col B)

HTH,
Keith

 

[Elvis]

I just tried to do as you suggested. I get the "The formula you typed
contains an error" when i try to paste the formula into A2. Did all the
steps u mentioned - put the number 1 in the A1, and the question text starts
from B1.

Any suggestions ?

 

[ker_01]

Sorry about the aircode error. This should work in cell A2.


=IF(AND(B2<>"",mid(B2,2,1)<>")"),MAX(A$1:A1)+1,"")

This assumes that your actual "options" all have the close parenthesis
symbol as the second character in each of those cells; if you have something
different, like:

[space] a) <- where it is the third character, or
(a) <- again it is the third character

then you will have to change the MID parameters to match the location where
it is located.

 

[Elvis]

I must be doing something wrong, everything you mentioned is just the way it
should be. The second character in "options" is always ")".

Thought of the easy way to program the enumerating, but not sure how to make
it work.

The script could look for any "a)" in B row, after it finds it, should go
one cell up and enumerate in adjacent A cell.

Could this be done ?

Sorry about the aircode error. This should work in cell A2.


=IF(AND(B2<>"",mid(B2,2,1)<>")"),MAX(A$1:A1)+1,"")

This assumes that your actual "options" all have the close parenthesis
symbol as the second character in each of those cells; if you have
something
different, like:

[space] a) <- where it is the third character, or
(a) <- again it is the third character

then you will have to change the MID parameters to match the location
where
it is located.

I just tried to do as you suggested. I get the "The formula you typed
contains an error" when i try to paste the formula into A2. Did all the
steps u mentioned - put the number 1 in the A1, and the question text
starts
from B1.

Any suggestions ?

 

[ker_01]

Yes; it just changes one piece of the equation;

put the number 1 in cell A1

put this formula in A2
autocopy the formula down

Anywhere you have a cell in column B that starts with "a)" the formula in
column A, one row above should show the next incremental number.

If this isn't working for you:
(a) describe how you are filling the formula into all subsequent cells- for
example, fill the formula down to row 15 and post back what the auto-filled
formula looks like in cell A15
(b) verify that column A is formatted as general (or number) and not text.
If the 1 is actually text instead of a number, that could cause problems
(c) verify that there aren't any extra spaces in your a) and that it is
actually a) and not A) (Excel is case-sensitive with text strings). To test,
go to a random empty cell and type in =left(B1,2)="a)" . Replace the B1 with
a cell reference of a cell that has an a) in it. If the formula evaluates to
false, you either have an extra space, or something else going on.

I must be doing something wrong, everything you mentioned is just the way it
should be. The second character in "options" is always ")".

Thought of the easy way to program the enumerating, but not sure how to make
it work.

The script could look for any "a)" in B row, after it finds it, should go
one cell up and enumerate in adjacent A cell.

Could this be done ?

Sorry about the aircode error. This should work in cell A2.


=IF(AND(B2<>"",mid(B2,2,1)<>")"),MAX(A$1:A1)+1,"")

This assumes that your actual "options" all have the close parenthesis
symbol as the second character in each of those cells; if you have
something
different, like:

[space] a) <- where it is the third character, or
(a) <- again it is the third character

then you will have to change the MID parameters to match the location
where
it is located.

I just tried to do as you suggested. I get the "The formula you typed
contains an error" when i try to paste the formula into A2. Did all the
steps u mentioned - put the number 1 in the A1, and the question text
starts
from B1.

Any suggestions ?


My apologies for not fully understanding.

Ok, that complicates it, but not terribly. It sounds like you want to
insert
a question # everywhere that the cell next to it is (a) not blank, and
(b)
doesn't have a second character of ")"

=IF(AND(B2<>"",mid(B2,2,1)<>")"),MAX(A$1:A2)+1,"")

(also corrected the Max part of the expression, which needs to
reference
col
A not col B)

HTH,
Keith

:

Thanks for your answer, but the problem is - the contents of the
"Question"
line is not a "Question" but it varies, just like all the answers too


ex.

A B
Which is the deepest loch in Scotland:
a) Loch Lomand
b) Loch Ness
c) Loch Morar
d) Loch Smith

What was the first of Earth's supercontinents:
a) Pangaea
b) Gondwanaland
c) Rodinia
...

I hope I explained the problem better now



Although this could be done with a macro (programming), I think you
can
accomplish your goal much more easily with a formula.

put a 1 in column A next to your first question, then put this
formula
in
the next row in column A and copy it all the way down. This assumes
your
first question starts in B1 (so this formula would be in A2); adjust
the
row
numbers accordingly.

=IF(B2="Question",MAX(B$1:B2)+1,"")

HTH,
Keith

:

Hi all,

this problem to all of you may look trival, but I need some help
with
this.

I got the sheet in excel looking like this :

A B
Question
a) answer 1
b) answer 2
c) answer 3
d) answer 4

Question
a) answer 1
b) answer 2
c) answer 3
d) answer 4

and so on..

I need to put the numbers in the row A so they are in front of each
"Question". They should start with 1. and finish with the last
"Question"
in
the table.
The number of answers differ, there are 4 or 5 answers for the each
question.

Is this doable ?

Thanks in advance !

Elvis