how to find what values sum a particular value

[anilahirwal]

hi all,

I have a column A with more than 500 values in it, with various values there in it.

how can i find what are the values, say 3-4 or even more which sum to a value say $5000.
can the Respective cell no. be also displayed alongwith so that i can locate them.

Cheers
Anil

[shg]

There are 2.6 billion combinations of 2, 3, or 4 numbers out of 5000. Chances are, there are many, many, many of them total $5000. See http://www.excelforum.com/tips-and-t...tion-sums.html

[anilahirwal]

There are 2.6 billion combinations of 2, 3, or 4 numbers out of 5000. Chances are, there are many, many, many of them total $5000. See http://www.excelforum.com/tips-and-t...tion-sums.html

tx shg, for ur kind n quick response, i tried tushar mehta's code and the macro ran into application time error 1004 with following line highlighted for debugging "InArr = Application.WorksheetFunction.Transpose( _
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)",
tx

[anilahirwal]

tx shg for ur help, i tried tushar mehta's code, but application time error 1004 is coming, with following line highlighted there. "InArr = Application.WorksheetFunction.Transpose( _
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)"

kinldy help on this

[shg]

Post a workbook with your values?

[anilahirwal]

Post a workbook with your values?

Tx for your kind response shg,

Please find attached the workbook "test.xlsx"

Regards

Anil

[shg]

With your 1857 numbers in col B, I found 6800+ solutions to 4 or fewer number totalling $5000.00. It was 38 seconds into the process, evaluating 1% of the possibilities when I shut it down. Do you see the problem?

[anilahirwal]

With your 1857 numbers in col B, I found 6800+ solutions to 4 or fewer number totalling $5000.00. It was 38 seconds into the process, evaluating 1% of the possibilities when I shut it down. Do you see the problem?

Tx shg, for all ur kind efforts, i now understand the problem..............

but, to still reduce the manual search & match process, can a code in VBA be written 1. which searches a value , say $5000, in cell A5, matches the exact amount i.e. $5000 in column B somewhere, say B56. Then it deletes both the rows.i.e. row 5 & row 56
2. or if we go a bit further, 2 or 3 combinations in column b somewhere, for example, say for $5000, in cell A5, and $1000, $2500 & $1500 in say, cell no.s B10,B25&B65 respectively, it matches & all rows i.e. 5,10,25&65 are deleted.


This can kind of reduce few minutes of mine.

tx again for your kind help

[anilahirwal]

Tx a lot shg, im so thankful to you.........

it helped me to cut my search time................

really appreciate your efforts, tx once more

[shg]

You're welcome, good luck.

[sandy789]

Required help, not able to work on this