Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Macro
I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I
need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#2
|
|||
|
|||
Hi
some questions: - do you need all possible combinations - do you have negative numbers - how many values do you have -- Regards Frank Kabel Frankfurt, Germany "Danny" schrieb im Newsbeitrag ... I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#3
|
|||
|
|||
The spreadsheet does contain negative numbers. The numbers are not intergers
and i have up to 50,000 pieces of data in my spreadsheet. Any given combination would do, however I need to know if there is more than 1 combination, and if so what and how many there are. "Danny" wrote: I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#4
|
|||
|
|||
Hi
one kludgy way: 1. Copy your data to a separate sheet :-) 2. Sort your data ascending 3. Start with the first positive value and enter the following formula in a column next to it (lets say the first positive number is in A10) =SUM($A$10:A10) and copy this down until you have reached your target value (+/-50) -- Regards Frank Kabel Frankfurt, Germany "Danny" schrieb im Newsbeitrag ... The spreadsheet does contain negative numbers. The numbers are not intergers and i have up to 50,000 pieces of data in my spreadsheet. Any given combination would do, however I need to know if there is more than 1 combination, and if so what and how many there are. "Danny" wrote: I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#5
|
|||
|
|||
The macro needs only to do this on certain selected columns if possible, and
then filter out the rows containing the relevant data. "Danny" wrote: The spreadsheet does contain negative numbers. The numbers are not intergers and i have up to 50,000 pieces of data in my spreadsheet. Any given combination would do, however I need to know if there is more than 1 combination, and if so what and how many there are. "Danny" wrote: I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#6
|
|||
|
|||
That would be possible, but i have to correlate this data to another value on
a separate spreadsheet, and doing this for each one would not be viable. Also the nature of the data means it has to stay in the order that it has been entered in, which leads me to the conclusion that a macro may be the only possible solution. However i do not have the knowledge to create a macro so complex, (well at least it is to me)! Can you help??? "Danny" wrote: The spreadsheet does contain negative numbers. The numbers are not intergers and i have up to 50,000 pieces of data in my spreadsheet. Any given combination would do, however I need to know if there is more than 1 combination, and if so what and how many there are. "Danny" wrote: I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
#7
|
|||
|
|||
You could add a helper column and number it consecutively, use Frank's
method, and after the rows have been identified, you could re-sort by the helper column to restore the entry order. Carlos "Danny" wrote in message ... That would be possible, but i have to correlate this data to another value on a separate spreadsheet, and doing this for each one would not be viable. Also the nature of the data means it has to stay in the order that it has been entered in, which leads me to the conclusion that a macro may be the only possible solution. However i do not have the knowledge to create a macro so complex, (well at least it is to me)! Can you help??? "Danny" wrote: The spreadsheet does contain negative numbers. The numbers are not intergers and i have up to 50,000 pieces of data in my spreadsheet. Any given combination would do, however I need to know if there is more than 1 combination, and if so what and how many there are. "Danny" wrote: I need help to develop a macro for my spreadsheet in Microsoft Excel 2002. I need it to be able to search through a set of numbers, and filter out the rows that total the sum (within a tolerance of 50) of any given number. Any help or solutions would be most welcome. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) | |||
pivotcell object to excel 2000 | Excel Discussion (Misc queries) |