Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Hi,
I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Well, you made the task even more challenging by not giving us a clue as to
how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
First, thank you for your response.
I have posted the three examples to show how the numbers will change. If you input in number 9 in number 5 cell, yes 5 will be replaced by the number 9, then after number 9, there will be 5,6,7,8,10. In regard to 30, I will have validation code to not accept any number above number 30. Thank you, Rad "Rick Rothstein" wrote: Well, you made the task even more challenging by not giving us a clue as to how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
I'm sorry, but I didn't scroll your original message down to see the
examples you provided (the gap you left between your "Thank you, Rad" and the examples was such that the examples were out of view and I didn't think to try and scroll down because of the large amount of white space under your signature). Anyway, I have a question about your "Changed Again" example... why didn't the 9 move down when the 10 was typed above it? Was that because the user typed it, so it needed to stay in the slot the user picked for it? If that is the case, for how long does the code need to remember that.. just until the worksheet is closed, or forever? Or did you make a mistake and the 9 should have slid down too? -- Rick (MVP - Excel) "Rad" wrote in message ... First, thank you for your response. I have posted the three examples to show how the numbers will change. If you input in number 9 in number 5 cell, yes 5 will be replaced by the number 9, then after number 9, there will be 5,6,7,8,10. In regard to 30, I will have validation code to not accept any number above number 30. Thank you, Rad "Rick Rothstein" wrote: Well, you made the task even more challenging by not giving us a clue as to how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Hi,
I'm not exactly clear about what you want. But sounds like you want something like Soduko but just for the columns. In Excel, do you want the whole swapping thing happening in 1 column? A simple algorithm would be as follow: Sample Numbers: 1 2 3 4 5...30 User Input: Enters 5 where it was 3 Response: Accepts the new value of the position as 5 Search for the number 5 If Match found, replace that with 3 Rest of the algorithm would depend on how restrictive is the input and number distribution system. Please provide more details regarding what you want. On Dec 23, 10:49*am, Rad wrote: Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 – 30, and I need to use vba code to allow the user to be able to change a number at a time. *Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. *In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original * * * *Changed Changed Again 1 * * * 1 * * * 1 2 * * * 2 * * * 2 3 * * * 3 * * * 10 4 * * * 4 * * * 3 5 * * * 9 * * * 9 6 * * * 5 * * * 4 7 * * * 6 * * * 5 8 * * * 7 * * * 6 9 * * * 8 * * * 7 10 * * *10 * * *8 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Hi Rick,
No problems, my bad too. Using a dynamic VBA array will scan the column at the start of the run, this way it will pick up the latest changes to the numbers in the column. The number 9 is sat from the first change in the second column in the example; the third column is another change to the column. When the array starts its run at the very beginning of the run, it will read and create an array with the new numbers. Rad "Rick Rothstein" wrote: I'm sorry, but I didn't scroll your original message down to see the examples you provided (the gap you left between your "Thank you, Rad" and the examples was such that the examples were out of view and I didn't think to try and scroll down because of the large amount of white space under your signature). Anyway, I have a question about your "Changed Again" example... why didn't the 9 move down when the 10 was typed above it? Was that because the user typed it, so it needed to stay in the slot the user picked for it? If that is the case, for how long does the code need to remember that.. just until the worksheet is closed, or forever? Or did you make a mistake and the 9 should have slid down too? -- Rick (MVP - Excel) "Rad" wrote in message ... First, thank you for your response. I have posted the three examples to show how the numbers will change. If you input in number 9 in number 5 cell, yes 5 will be replaced by the number 9, then after number 9, there will be 5,6,7,8,10. In regard to 30, I will have validation code to not accept any number above number 30. Thank you, Rad "Rick Rothstein" wrote: Well, you made the task even more challenging by not giving us a clue as to how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Hello,
I am not looking into the swap, I am looking for an algorithm that when the user inputs a number in a column that starts from 1 to 30, the code will input the new number and then will reduce the greater of bigger numbers by -1 until reaches the number that was inputted. Rad "margoob" wrote: Hi, I'm not exactly clear about what you want. But sounds like you want something like Soduko but just for the columns. In Excel, do you want the whole swapping thing happening in 1 column? A simple algorithm would be as follow: Sample Numbers: 1 2 3 4 5...30 User Input: Enters 5 where it was 3 Response: Accepts the new value of the position as 5 Search for the number 5 If Match found, replace that with 3 Rest of the algorithm would depend on how restrictive is the input and number distribution system. Please provide more details regarding what you want. On Dec 23, 10:49 am, Rad wrote: Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
Sorry, I am having trouble determining the full extent of the algorithm you
are looking for, so I have a couple of more questions... What should happen if the number the user types is located BEFORE the position he types it in at? For example, what should the rest of the column look like if the user types in 3 at the location I show below? AND NOTE that the 9 in the 5th row was previously typed in by the user! 1 1 2 2 3 3 4 4 5 9 6 5 7 6 3 8 7 9 8 10 8 In the "Changed Again" column from your first posting, the 9 did not move (it got skipped over) because the user entered it previously... what should happen if the user types that number again, but in a different location? What would the rest of the column look like for this example (where the 9 was previously entered by the user in the 5th row)... 1 1 1 2 2 2 9 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 Now, same scenario, but what if the 9 came AFTER the position it was previously entered in (again, the 9 was previously entered in the 5th row by the user) - what should the rest of the column look like then... 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 9 8 7 10 10 8 -- Rick (MVP - Excel) "Rad" wrote in message ... Hi Rick, No problems, my bad too. Using a dynamic VBA array will scan the column at the start of the run, this way it will pick up the latest changes to the numbers in the column. The number 9 is sat from the first change in the second column in the example; the third column is another change to the column. When the array starts its run at the very beginning of the run, it will read and create an array with the new numbers. Rad "Rick Rothstein" wrote: I'm sorry, but I didn't scroll your original message down to see the examples you provided (the gap you left between your "Thank you, Rad" and the examples was such that the examples were out of view and I didn't think to try and scroll down because of the large amount of white space under your signature). Anyway, I have a question about your "Changed Again" example... why didn't the 9 move down when the 10 was typed above it? Was that because the user typed it, so it needed to stay in the slot the user picked for it? If that is the case, for how long does the code need to remember that.. just until the worksheet is closed, or forever? Or did you make a mistake and the 9 should have slid down too? -- Rick (MVP - Excel) "Rad" wrote in message ... First, thank you for your response. I have posted the three examples to show how the numbers will change. If you input in number 9 in number 5 cell, yes 5 will be replaced by the number 9, then after number 9, there will be 5,6,7,8,10. In regard to 30, I will have validation code to not accept any number above number 30. Thank you, Rad "Rick Rothstein" wrote: Well, you made the task even more challenging by not giving us a clue as to how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Algorithm
No problems Rick,
What should happen if the number the user types is located BEFORE the position he types it in at? For example, what should the rest of the column look like if the user types in 3 at the location I show below? AND NOTE that the 9 in the 5th row was previously typed in by the user! 1 1 2 2 3 3 4 4 5 9 6 5 7 6 3 // I am looking for advice on this part. 8 7 9 8 10 8 In the "Changed Again" column from your first posting, the 9 did not move (it got skipped over) because the user entered it previously... what should happen if the user types that number again, but in a different location? What would the rest of the column look like for this example (where the 9 was previously entered by the user in the 5th row)... 1 1 1 2 2 2 9 // Here it should replace the 2 and the previous 9 need to change to a number that does not exists, so no doublicats exists between 1- 30. 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 Now, same scenario, but what if the 9 came AFTER the position it was previously entered in (again, the 9 was previously entered in the 5th row by the user) - what should the rest of the column look like then... 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 //Same as the previous question. Here it should replace the 3 and the previous 9 need to change to a number that does not exists, 9 8 7 so no doublicats exists between 1- 30. 10 10 8 ------------------ Rad "Rick Rothstein" wrote: Sorry, I am having trouble determining the full extent of the algorithm you are looking for, so I have a couple of more questions... What should happen if the number the user types is located BEFORE the position he types it in at? For example, what should the rest of the column look like if the user types in 3 at the location I show below? AND NOTE that the 9 in the 5th row was previously typed in by the user! 1 1 2 2 3 3 4 4 5 9 6 5 7 6 3 8 7 9 8 10 8 In the "Changed Again" column from your first posting, the 9 did not move (it got skipped over) because the user entered it previously... what should happen if the user types that number again, but in a different location? What would the rest of the column look like for this example (where the 9 was previously entered by the user in the 5th row)... 1 1 1 2 2 2 9 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 Now, same scenario, but what if the 9 came AFTER the position it was previously entered in (again, the 9 was previously entered in the 5th row by the user) - what should the rest of the column look like then... 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 9 8 7 10 10 8 -- Rick (MVP - Excel) "Rad" wrote in message ... Hi Rick, No problems, my bad too. Using a dynamic VBA array will scan the column at the start of the run, this way it will pick up the latest changes to the numbers in the column. The number 9 is sat from the first change in the second column in the example; the third column is another change to the column. When the array starts its run at the very beginning of the run, it will read and create an array with the new numbers. Rad "Rick Rothstein" wrote: I'm sorry, but I didn't scroll your original message down to see the examples you provided (the gap you left between your "Thank you, Rad" and the examples was such that the examples were out of view and I didn't think to try and scroll down because of the large amount of white space under your signature). Anyway, I have a question about your "Changed Again" example... why didn't the 9 move down when the 10 was typed above it? Was that because the user typed it, so it needed to stay in the slot the user picked for it? If that is the case, for how long does the code need to remember that.. just until the worksheet is closed, or forever? Or did you make a mistake and the 9 should have slid down too? -- Rick (MVP - Excel) "Rad" wrote in message ... First, thank you for your response. I have posted the three examples to show how the numbers will change. If you input in number 9 in number 5 cell, yes 5 will be replaced by the number 9, then after number 9, there will be 5,6,7,8,10. In regard to 30, I will have validation code to not accept any number above number 30. Thank you, Rad "Rick Rothstein" wrote: Well, you made the task even more challenging by not giving us a clue as to how you want any duplicates removed... the simplest would be to trade the number being overwritten for the number the user is typing in... would that be acceptable or did you have some other way in mind? Also... what cells are your numbers in (or what cells are they allowed to be in? Do you have any controls to prevent the user from typing in more than 30 numbers in your column (that would create an impossible situation to handle other than removing the entry immediately)? Do you have any control to stop the user from typing in a number bigger than 30? -- Rick (MVP - Excel) "Rad" wrote in message ... Hi, I am looking for an advice on this challenging algorithm. I have a column in ms excel sheet and has the numbers from 1 €“ 30, and I need to use vba code to allow the user to be able to change a number at a time. Then as soon as it is changed, the column numbers need the change so there is no duplicate numbers in the column. In other words, the column numbers 1- 30 must have only a number of each. Any advice will be appreciated. Thank you, Rad I thought about swapping, but the number next to the changed number must be reduced by a number until the number that has been changed from is reached. For example: Original Changed Changed Again 1 1 1 2 2 2 3 3 10 4 4 3 5 9 9 6 5 4 7 6 5 8 7 6 9 8 7 10 10 8 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FFT function/algorithm used by Excel | Excel Worksheet Functions | |||
Dijkstra Algorithm In Excel | Excel Programming | |||
How can I monitor CPU time of algorithm I developed in Excel vba? | Excel Programming | |||
How can I monitor CPU time of algorithm I developed in Excel vba? | Excel Discussion (Misc queries) | |||
Algorithm for computing Excel PercentRank | Excel Worksheet Functions |