Finding Sparse values

[wfisch]

I have what I think should be a relatively straightforward problem, but I haven't had any luck solving it myself :

I have data from a DAC card which comes out every half second onto sheet "d". The data has a seconds column (from time of acquisition beginning) and directly next to it a "User Comment" column (C) which allows the user to input comments about the data being collected. So the comments (which come out every few minutes) are quite sparse; some 30 total entries in 16000 rows. What I'd like to do is to use a separate sheet to pull the row number in which each is located, the time it happened (column B), and the comment itself into separate columns. So far, I've gotten it to find the first row with a

=INDEX(d!C$3:C$16000,MATCH(TRUE,LEN(d!C$3:C$16000)<>0,0),1)

and some commands taken from that. But it doesn't seem to want to continue past the first match it gets. Help is most appreciated!
Thanks,
Will Fischer

 

[erik.van.geit]

Hi, wfisch,
WELCOME to the BOARD!!!!!

I'm not sure if there is an easier solution (apart from doing some calculations within an extra column, to make it calculate faster)

I've put everything on the same sheet. Try out and if it works for you, then put it on two sheets.

   A    B     C        D  E   F   G        
 1 auto other comment  0  4                
 2 A2   B2                A6  B6  comment1 
 3 A3   B3                A14 B14 comment2 
 4 A4   B4                A15 B15 comment3 
 5 A5   B5                A18 B18 comment4 
 6 A6   B6    comment1 1                   
 7 A7   B7                                 
 8 A8   B8                                 
 9 A9   B9                                 
10 A10  B10                                
11 A11  B11                                
12 A12  B12                                
13 A13  B13                                
14 A14  B14   comment2 2                   
15 A15  B15   comment3 3                   
16 A16  B16                                
17 A17  B17                                
18 A18  B18   comment4 4                   
19 A19  B19                                
20 A20  B20                                
21 A21  B21                                
22 A22  B22                                
23 A23  B23                                
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
D2:D23  =IF(C2="","",LOOKUP(9.99999999999999E+307,$D$1:D1)+1)
E1      =LOOKUP(9.99999999999999E+307,D:D)
E2:G2   =IF(ROWS($E2:E$2)< =$E$1,LOOKUP(ROWS($D2:D$2),$D$2:$D$65536,A$2:A$65536),"")
E3:G23  =IF(ROWS($E$2:E3)< =$E$1,LOOKUP(ROWS($D$2:D3),$D$2:$D$65536,A$2:A$65536),"")
[Table-It] version 09 by Erik Van Geit

I'm not really font of this solution and would tend to use some code, which would do the following:
filter source sheet for non blank comments
copy paste to other sheet
unfulter source sheet

this code could be ran whenever the other sheet is activated

kind regards,
Erik

 

[wfisch]

Thank you so much. I'll give it a shot and get back if I have trouble.

 

[erik.van.geit]

you're welcome
do not forget to try out the filter method