Find and tell cell coordinates that sum to a result

[jberwind]

I have a coloumn (i.e. A) with hundreds of numbers. I'd like to be able to write a number in a cell (i.e. B4), and have Excel find and tell the coordinates of those cells in coloumn A that sum up to the result I wrote in B4 (i.e., 32). I also would like to be able to "hit a key" and see the next possible set of result-cells.


I first found usable code to sum in sequence - Ill call VBA1 which I used successfully to produce the running total in the adjacent column. I must now revise the code to identify the groups of cells in the running total column that do not exceed a value (i.e., value set is cell c170 which happens to be 32). So I want to identify


Columns A= Row#; Column B=Starting values; Column C= Running total & cell C170 =32,,, I want to find c15, then I want to find the next set which is c27, and so on,,, Find the coordinate of the cell where the sum of the preceding cells is equal to or less than 32 (not >32), then start over from the next cell, and so on until the end of the list.

A B C

1 1 1

2 2 3

3 1 4

4 4 8

5 1 9

6 3 12

7 1 13

8 3 16

9 1 17

10 4 21

11 1 22

12 4 26

13 1 27

14 3 30

15 1 31*

16 4 35

17 1 36

18 3 39

19 1 40

20 5 45

21 1 46

22 4 50

23 1 51

24 6 57

25 1 58

26 3 61

27 1 62*

28 3 65

29 1 66

30 3 69

31 1 70

32 4 74

33 1 75

34 5 80

35 1 81

36 4 85

37 1 86

38 4 90

39 1 91

40 4 95

41 1 96

42 2 98

43 1 99

44 4 103

45 1 104

46 4 108

47 1 109

48 5 114

49 1 115

50 5 120

51 1 121

52 2 123

53 1 124

54 2 126

55 1 127

56 5 132

57 1 133

58 4 137

59 1 138

60 3 141

61 1 142

62 4 146

63 1 147

64 3 150

65 1 151

66 3 154

67 1 155

68 4 159

69 1 160

70 3 163

71 1 164

72 3 167

73 1 168

74 3 171

75 1 172

76 3 175

77 1 176

78 2 178

79 1 179

80 3 182

81 1 183

82 3 186

83 1 187

84 7 194

85 1 195

86 3 198

87 1 199

88 4 203

89 1 204

90 3 207

91 1 208

92 3 211

93 1 212

94 3 215

95 1 216

96 3 219

97 1 220

98 4 224

99 1 225

100 4 229

101 1 230

102 4 234

103 1 235

104 2 237

105 1 238

106 5 243

107 1 244

108 4 248

109 1 249

110 4 253

111 1 254

112 2 256

113 1 257

114 4 261

115 1 262

116 2 264

117 1 265

118 4 269

119 1 270

120 3 273

121 1 274

122 4 278

123 1 279

124 4 283

125 1 284

126 4 288

127 1 289

128 4 293>
129 1 294

130 3 297

131 1 298

132 3 301

133 1 302

134 2 304

135 1 305

136 2 307

137 1 308

138 2 310

139 1 311

140 2 313

141 1 314

142 2 316

143 1 317

144 3 320

145 1 321

146 2 323

147 1 324

148 5 329

149 1 330

150 3 333

151 1 334

152 2 336

153 1 337

154 2 339

155 1 340

156 3 343

157 1 344

158 3 347

159 1 348

160 5 353

161 1 354

162 3 357

163 1 358

164 4 362

165 1 363

166 6 369

VBA1 =

Sub runbalance()

Dim wksht As Worksheet

Dim counter As Long


Set wksht = ThisWorkbook.ActiveSheet


For counter = 1 To 200


wksht.Cells(counter, 1).Offset(0, 1).Value = Application.WorksheetFunction.Sum(wksht.Range(Cells(1, 1), Cells(counter, 1)).Value)


Next counter


End Sub


I now need to do something with the code to achieve the goal.


Much appreciate any help!

 

[NARAYANK991]

Hi ,


If you don't mind a formula solution , here goes :


1. I assume that your data is in columns A and B , starting from row 3 ; the data in column A is just the row number , starting from 1 , and the data in column B is the numbers which need to be summed up.


2. I assume your threshold value ( 32 in your example ) is in cell D1.


3. In column D , starting from cell D3 , enter the following formula :


=D2+LOOKUP($D$1,SUMIF(OFFSET(INDEX($B$3:$B$168,D2+1),,,ROW(INDEX($B$3:$B$168,D2+1):$B$168)-ROW(INDEX($B$3:$B$168,D2+1))+1),"<"&$D$1),ROW(INDEX($A$3:$A$168,D2+1):$A$168)-ROW(INDEX($B$3:$B$168,D2+1))+1)


4. In column E , starting from cell E3 , enter the following formula :


=ADDRESS(MATCH(D3,A:A,0),1)


Copy both the above formulae downwards.


D2 should be blank.


Narayan

 

[jberwind]

Not sure why, but after d12 throwing #ref. And, formula for e3 is immediately throwing off a #ref.

 

[NARAYANK991]

Hi ,


With the above formulae in columns D and E , the results I got are :

[pre]

15	$A$17
27	$A$29
39	$A$41
52	$A$54
67	$A$69
83	$A$85
96	$A$98
109	$A$111
123	$A$125
139	$A$141
156	$A$158
166	$A$168

[/pre]
Row 168 is the end of data.


D3 is :


=D2+LOOKUP($D$1,SUMIF(OFFSET(INDEX($B$3:$B$168,D2+1),,,ROW(INDEX($B$3:$B$168,D2+1):$B$168)-ROW(INDEX($B$3:$B$168,D2+1))+1),"<"&$D$1),ROW(INDEX($A$3:$A$168,D2+1):$A$168)-ROW(INDEX($B$3:$B$168,D2+1))+1)


E3 is :


=ADDRESS(MATCH(D3,A:A,0),1)


Narayan

 

[jberwind]

Narayan: I'm not getting those, but I checked the formula you provided and its as you want it. My row a, beginning with a3 is the row number starting at 1. My row b, also beginning at b3 is the running totals. My row c, is the word count which is =IF(LEN(TRIM(F159))=0,0,LEN(TRIM(F159))-LEN(SUBSTITUTE(F159," ",""))+1). My row d is defined as you wanted and so too is my row e. These are how they look.


1 1 1 5 #N/A 002380:KS

2 3 2 7 #N/A KCC Corp

3 4 1 8 #N/A 005930:KS

4 8 4 9 #N/A Samsung Electronics Co Ltd

5 9 1 10 #N/A 010060:KS

6 12 3 11 #N/A OCI Co Ltd

7 13 1 12 #N/A 021240:KS

8 16 3 13 #N/A Coway Co Ltd

9 17 1 14 #N/A 036490:KS

10 21 4 166 #N/A OCI Materials Co Ltd

11 22 1 #REF! #REF! 050760:KS

12 26 4 #REF! #REF! S Polytech Co Ltd

13 27 1 #REF! #REF! 066980:KS


Seems I have something wrong. I really like your results more!

 

[NARAYANK991]

Hi ,


Since the format of your data is somewhat different from what was posted earlier , please use the following formulae :


1. Use the following in any unused column ; I have used column M.


=M2+LOOKUP($D$1,SUMIF(OFFSET(INDEX($C$3:$C$168,M2+1),,,ROW(INDEX($C$3:$C$168,M2+1):$C$168)-ROW(INDEX($C$3:$C$168,M2+1))+1),"<"&$D$1),ROW(INDEX($A$3:$A$168,M2+1):$A$168)-ROW(INDEX($A$3:$A$168,M2+1))+1)


The formula is entered in M3 , and copied down.


2. Use the following in column D , starting from D3 , and copy down :


=ADDRESS(MATCH(M3,A:A,0),1)


I have assumed that your data is as follows :

[pre]

Column A : Row number
B : Running Total
C : Word Count
D : Final formula for giving the address of the cell which matches the count entered in D1.
E : Text

Column M : Helper column , used by the formula in column D

[/pre]
Narayan

 

[jberwind]

Hi Narayan:


Tried with no success which is frustrating because we are "seemingly" in-synch. I am ready to share the file. joe[at]aeiresearch[dot]net


Best,

Joe

 

[NARAYANK991]

Hi Joe ,


Sorry to hear that ; please send your file to my email address :


narayank1026[at]gmail[dot]com


Narayan