Data Extraction from an Array

[brmeeke]

Hi

I need to extract data from a series of columns to build a new array. Please look at the table below:

[TABLE="width: 526"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Error Num[/TD]
[TD]Row Key[/TD]
[TD]Message[/TD]
[TD]Error Num[/TD]
[TD]Row Key[/TD]
[TD]Message[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2.07.1[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]4[/TD]
[TD]2.09.4[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.12.2[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.14.3[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]99[/TD]
[/TR]
</tbody>[/TABLE]

Column A & D have an ascending error number. I need to find the non-zero rows from Column A & D and then pick up the data that is in the next two columns. The objective is to create a new array that looks like this:

[TABLE="width: 260"]
<tbody>[TR]
[TD]1[/TD]
[TD]2.07.1[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.12.2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.14.3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.09.4[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

My first thought was to capture the Address of the row and column that holds the error number, and then use Offsets to extract the information from the other two rows. I hoped that I could use the Match function in an Array formula and present columns A & D as one continuous array but I could not figure out a syntax that Match would accept.

Does anyone have any suggestions?


Thank you
Brian

 

[DanteAmor]

There you have the options with array formula and with macro

 

[brmeeke]

Thank you to all. I have 3 or 4 different approaches that I can take. Ultimately, I will probably use a combination of these elegant ideas.


Brian

 

[DanteAmor]

@DanteAmor

Nice!!

Thanks for the comment

 

[DanteAmor]

There you have the options with array formula and with macro

I'm glad to help you