Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote: 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" wrote in message ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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 ? "ker_01" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: macro - enumerating in excel
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. "Elvis" wrote: 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 ? "ker_01" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Q: macro - enumerating in Excel | Excel Programming | |||
Enumerating SpecialCells | Excel Programming | |||
Enumerating the names of *subs* and *functions* | Excel Programming | |||
Enumerating Printers | Excel Programming | |||
Enumerating a multi-dimensional array | Excel Programming |