Consolidating data via formula

[M_B33]

Hello all,

I am working on creating a data reduction sheet for a very large amount of data acquired through a Data Acquisition System. I have a 'Raw Data' sheet set up to quickly copy and paste over the entire DAQ file into specific columns. One thing I am recording is the flow rate through a solenoid. I want to consolidate the data in that column onto another sheet, based on when the value is =0.00 (solenoid is closed), and when there is anything else.

So say I have 2 draws for different spans of time in Column A of Sheet 1, but they are way down on say row 13,000+. I would like to organize that data so that Draw 1 shows up on Sheet 2 in Column A starting at row 2, and Draw 2 starting in B2.

I know I could pull the data over to the sheet just by using a simple =IF(Sheet1!A1=0,"",Sheet1!A1). Albeit, that will not organize the data so that it moves from the bottom of Sheet 1 to the top of Sheet 2.

Any help would be greatly appreciated. Thank you for your time!

 

[FDibbins]

Just so that I understand...

If th 0.00 appears in (say) A13000 on sheet 1, then on sheet 2, you want 0.00 to appear in A1, then below that (A2/A3 etc) you want to show all the data below A13000 from sheet 1 (A13001/A13002 etc)?

 

[FDibbins]

On the assumption that your answer to the above will be yes...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

1.00​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td]

2​

[/td][td]

2.00​

[/td][td][/td][td]d[/td][/tr]

[tr][td]

3​

[/td][td]aa[/td][td][/td][td]

3​

[/td][/tr]

[tr][td]

4​

[/td][td]bb[/td][td][/td][td]

0​

[/td][/tr]

[tr][td]

5​

[/td][td]

0.00​

[/td][td][/td][td]aa[/td][/tr]

[tr][td]

6​

[/td][td]d[/td][td][/td][td]vv[/td][/tr]

[tr][td]

7​

[/td][td]

3.00​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td]

8​

[/td][td]

0.00​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td]

9​

[/td][td]aa[/td][td][/td][td]

0​

[/td][/tr]

[tr][td]

10​

[/td][td]vv[/td][td][/td][td][/td][/tr]
[/table]
C1=INDEX(A:A,MATCH(0,A:A,0)+ROW()-1)
copied down

If that is to be on another sheet, then it becomses...
=INDEX(Sheet7!A:A,MATCH(0,Sheet7!A:A,0)+ROW()-1)

 

[M_B33]

FDibbins,

Thank you for your prompt reply, that is almost what I am trying to do, and I can probably make it work using that technique in a manner anyway. Although, the majority of the time my data will be reading 0.00, however from time to time there will be a draw taking place where I actually have data. I need to search through the column until I find any value greater than 0.00, then I would copy all of the info below that point UNTIL the data read 0.00 once more (showing that the solenoid had been shut and flow was lost).

Thanks!

 

[M_B33]

For Example:

Sheet 1 would show:
[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]Draw Flow[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[/TR]
[TR]
[TD].99[/TD]
[/TR]
[TR]
[TD]1.02[/TD]
[/TR]
[TR]
[TD].89[/TD]
[/TR]
[TR]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3.12[/TD]
[/TR]
[TR]
[TD]3.02[/TD]
[/TR]
[TR]
[TD]3.15[/TD]
[/TR]
[TR]
[TD]2.98[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 would show:[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Draw 1[/TD]
[TD]Draw 2[/TD]
[/TR]
[TR]
[TD].99[/TD]
[TD]3.12[/TD]
[/TR]
[TR]
[TD]1.02[/TD]
[TD]3.02[/TD]
[/TR]
[TR]
[TD].89[/TD]
[TD]3.15[/TD]
[/TR]
[TR]
[TD]1.05[/TD]
[TD]2.98[/TD]
[/TR]
</tbody>[/TABLE]

 

[MarcelBeug]

The following solution uses a helper column on sheet1 and a helper row on sheet2.
Additionally, I formatted the input and associated helper column in a table “Table1” with headers “Draw flow” in A1 and “Draw No” in B1.
So the solution will still work when entries are added.

Enter in Sheet1!B2 and copy down (will be done automatically if the range is already formatted as a table):

=(A2<>0)*(MAX(B$1:B1)+(SUM(A1)=0))

This will add sequence numbers to the input.

On Sheet2, row 1 will be a helper row with the number of values for each draw.
Enter in Sheet2!A1 and copy to the right for as many draws you would ever expect to have:

=COUNTIF(Table1[Draw No],(COLUMN(A1)-COLUMN($A1)+1))

Enter in Sheet2!A2 and copy to the right, “Draw 1”, “Draw 2”, or with a formula

=IF(A1=0,"","Draw "&( COLUMN(A1)-COLUMN($A1)+1))

Enter in Sheet2!A3, copy to the right and down for as many values you would ever expect to have in a single draw, the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):

=IF((ROW(A3)-ROW(A$3)+1)>A$1,"",INDEX(Table1[Draw flow],SMALL(IF(Table1[Draw No]=COLUMN(A3)-COLUMN($A3)+1,ROW(INDIRECT("1:"&COUNTA(Table1[Draw No])))),ROW(A3)-ROW(A$3)+1)))

 

[M_B33]

MarcelBeug,

Thank you for your prompt reply, I apologize for my delay in response. I just started back to working on this, and tested out your code. It works well, except for the last step. When I copy to the right it starts a count for every draw, and when I copy down it just repeats this number. So my Sheet 2 looks like:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]112[/TD]
[TD]21[/TD]
[TD]11[/TD]
[TD]64[/TD]
[TD]60[/TD]
[TD]36[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]Draw 1[/TD]
[TD]Draw 2[/TD]
[TD]Draw 3[/TD]
[TD]Draw 4[/TD]
[TD]Draw 5[/TD]
[TD]Draw 6[/TD]
[TD]Draw 7[/TD]
[TD]Draw 8[/TD]
[TD]Draw 9[/TD]
[TD]Draw 10[/TD]
[TD]Draw 11[/TD]
[TD]Draw 12[/TD]
[TD]Draw 13[/TD]
[TD]Draw 14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

So instead of it correlating with the real data from Sheet 1, it is only telling me what draw # it is. However, the top row is correct in the number of data points I have for each specific draw. So it is only this last step that I need to fix. Here is the code I had to modify it to:

=IF((ROW(A3)-ROW(A$3)+1)>A$1,"",INDEX('Raw Data'!$AN$12:$AN$13700,SMALL(IF('Raw Data'!$AN$12:$AN$13700=COLUMN(A3)-COLUMN($A3)+1,ROW(INDIRECT("1:"&COUNTA('Raw Data'!$AN$12:$AN$13700)))),ROW(A3)-ROW(A$3)+1)))

The only change is that instead of using the table format, the helper column on Sheet 1 ('Raw Data') is column AN with values starting on Row 12 going through 13700. Thanks for your help with this, I really appreciate it!!

 

[M_B33]

Ah I actually just noticed a mistake I made when I was looking closer at the code. It makes sense now why I was not seeing any flows, because I was referencing my helper column on Sheet 1 twice by mistake in the last line of code. I will modify that and let you know if it fixes everything.

 

[M_B33]

Victory! It worked like a charm after that small change. Thank you again for your assistance!

 

[MarcelBeug]

You're welcome.