Data Extraction from an Array

B

brmeeke

New Member
Joined
Jan 30, 2008
Messages
21
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Sure, why not loop thru columns A and D and where only those whose value is greater than 0 (zero) , use the Offset(, 2).value to get row.key values...
 
Last edited:
Upvote 0
Currently, this is what I have; still need to get it all in 1 column in order though

Code: 
Option Explicit

Sub x1085137()
'https://www.mrexcel.com/forum/excel-questions/1085137-data-extraction-array.html
Dim LRow As Long, r As Integer, c As Integer


Cells.Copy
Sheets.Add after:=ActiveSheet
ActiveSheet.Paste


LRow = Cells(Rows.Count, "A").End(xlUp).Row


For c = 1 To 4 Step 3
    For r = LRow To 2 Step -1
        If Cells(r, c).Value = 0 Then
            Range(Cells(r, c), Cells(r, c + 2)).Delete (xlUp)
        End If
    Next r
Next c


End Sub
 
Upvote 0
Now with sorting

Code: 
Option Explicit

Sub x1085137()
'https://www.mrexcel.com/forum/excel-questions/1085137-data-extraction-array.html
Dim LRow As Long, LRow2 As Long, r As Integer, c As Integer


Application.ScreenUpdating = False


Cells.Copy
Sheets.Add after:=ActiveSheet
ActiveSheet.Paste


LRow = Cells(Rows.Count, "A").End(xlUp).Row


For c = 1 To 4 Step 3
    For r = LRow To 2 Step -1
        If Cells(r, c).Value = 0 Then
            Range(Cells(r, c), Cells(r, c + 2)).Delete (xlUp)
        End If
    Next r
Next c


LRow = Cells(Rows.Count, "D").End(xlUp).Row
LRow2 = Cells(Rows.Count, "A").End(xlUp).Row + 1


Range(Cells(2, 4), Cells(LRow, 6)).Cut Cells(LRow2, 1)


Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo


Range("D1:F1").ClearContents
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,

Does this get you any closer to where you need to be...

Code: 
Sub test()


    Dim arr1, arr2, cmp
    Dim lRow As Long, a As Long, b As Long, ct As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    ct = 1
    arr1 = Range("A2:C" & lRow)
    arr2 = Range("D2:F" & lRow)
    ReDim cmp(1 To lRow, 1 To 3)
    
    For a = LBound(arr1) To UBound(arr1)
        If Not (arr1(a, 1)) = 0 Then
            cmp(ct, 1) = arr1(a, 1)
            cmp(ct, 2) = arr1(a, 2)
            cmp(ct, 3) = arr1(a, 3)
            ct = ct + 1
        End If
    Next
    For b = LBound(arr2) To UBound(arr2)
        If Not (arr2(b, 1)) = 0 Then
            cmp(ct, 1) = arr2(b, 1)
            cmp(ct, 2) = arr2(b, 2)
            cmp(ct, 3) = arr2(b, 3)
            ct = ct + 1
        End If
    Next
    Range("I2").Resize(ct - 1, 3) = cmp
    
End Sub
 
Upvote 0
Thank you for your replies. I agree that with a bit of VBA code, I could loop through the cells and perform the necessary steps. However, my spreadsheet does not have any VBA code today and I was hoping that I could generate the desired result without having to go that route. (Sorry if I am being too simplistic)

As far as the sorting issue is concerned, I should have noted that I do not care. I am going to take the extracted array and re-sort it a couple of different ways before I show the results to the end-user. I just need to figure out a way to extract the non-zero error rows first.

Again - appreciate your help.


Brian
 
Upvote 0
With formulas it could be like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Error num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Row key[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Message[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Error num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Row key[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Message[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Refer[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Array Formula[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Error num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Row key[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Message[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]$A$4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.07.1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]$A$9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.12.2[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.07.1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD]$A$4[/TD]
[TD]$A$11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.14.3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]$D$6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.09.4[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.09.4[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.12.2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD]$A$9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.14.3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD]$A$11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$D$6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formulas[/TD]
[/TR]
[TR]
[TD]
CellFormula
H2=IF(A2=0,0,ADDRESS(ROW(A2),COLUMN(A2)))
...Copy to H11
H12=IF(D2=0,0,ADDRESS(ROW(D2),COLUMN(D2)))
Copy to H21
Array Formula
I2{=IFERROR(INDEX($H$1:$H$21,SMALL(IF($H$2:$H$21>0,ROW()),ROW()-1)),"")}
Formula
J2=IFERROR(INDIRECT(I2),"")
K2=IFERROR(OFFSET(INDIRECT(I2),0,1),"")
L2=IFERROR(OFFSET(INDIRECT(I2),0,2),"")
Copy to row 21

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you are going to use a single column then


[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Error num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Row key[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Message[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Error num[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Row key[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Message[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.07.1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.12.2[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.07.1[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.14.3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.09.4[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.12.2[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.14.3[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.09.4[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formulas[/TD]
[/TR]
[TR]
[TD]
CellArray Formula
E2{=IFERROR(INDEX(A$1:A$21,SMALL(IF($A$2:$A$21>0,ROW()),ROW()-1)),"")}
F2{=IFERROR(INDEX(B$1:B$21,SMALL(IF($A$2:$A$21>0,ROW()),ROW()-1)),"")}
G2{=IFERROR(INDEX(C$1:C$21,SMALL(IF($A$2:$A$21>0,ROW()),ROW()-1)),"")}

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD]Copy to row 21, To accept press Shift + Control + Enter[/TD]
[/TR]
</tbody>[/TABLE]


---
Just in case, I put the macro

Code: 
Sub arreglo()
    i = 2
    For Each d In Range("A2:A11, D2:D11")
        If d.Value > 0 Then Cells(i, "H").Resize(1, 3).Value = d.Resize(1, 3).Value: i = i + 1
    Next
End Sub
 
Upvote 0
thanks for the suggestion of the single column. I already thought about that and it is an option however the array is quite large so the number of rows required to work with this data as a single column would be quite significant.
 
Upvote 0
@DanteAmor

Nice!!

DanteAmor said:
Just in case, I put the macro

Code: 
Sub arreglo()
    i = 2
    For Each d In Range("A2:A11, D2:D11")
        If d.Value > 0 Then Cells(i, "H").Resize(1, 3).Value = d.Resize(1, 3).Value: i = i + 1
    Next
End Sub
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
381
Peter_SSs
Peter_SSs
B
  • Question Question
Groups/Arrays/Patterns Help
Replies
5
Views
568
JamesCanale
JamesCanale
B
  • Question Question
Search partial text in a lookup
Replies
1
Views
303
felixstraube
F
T
  • Solved
This seems like a numeric error to me, but does anybody have an explanation.
Replies
2
Views
261
TomCon
T
L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
278
L8NightKebab
L

Forum statistics

Threads
1,223,099
Messages
6,170,111
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top