matching problem...I think

[Steve]

Hi,

I have a worksheet with 3 columns (A, B & C) of numbers. Somehow, I
need column "D" to get the first zero in column "A" to match up with
the first number that appears in column "C", the second zero in column
"A" to match up with the second number that appears in column "C", the
third zero in column "A" to match up with the third number that
appears in column "C", etc....


Please advise,

Steve


199 169
276 243
413 390
209 179
210 180
288 255
414 391 167
33 30 245
400 167
0 245 167
0 392 245
283 250
204 174
275 242
415 393
235 205 217
282 249
236 206
0 248 217
201 171 167
289 256 211
207 177 288
286 253
0 394 167
0 395 211
0 405 288
26 412
38 424
18 404
17 403

 

[Bob Umlas]

In E2(I know you said D, but hang in there), enter
=MATCH(0,OFFSET($A$1,E1,0,100,1)+E1
in D2 enter =INDEX(C:C,E2)
Select D2:E2 and fill down. When you get #N/A errors, you've run out of
zeros in col A.
The reason you need to start in row 2 is that you need the empty cell in E1
for the first formula (in E2).
You can suppress the #N/A values by selecting D:E, use Format/Conditional
Formatting, change "Cell Value Is" to "Formula Is", then enter =ISERROR(D1)
and click the Format button, the Font tab, select a white font. (D1 because
D1 is the active cell).
You can also hide col E if you don't want that to show.
HTH
Bob Umlas
Excel MVP