IF formula true THEN move to next row

**zepelin** · 12-27-2012, 12:00 PM

I have a table with 131 columns and 4000 rows. I need in a different sheet to make a tabel with 3 raws:

original tabel has row A as an name(internal number composed of leters and/or digits) and the rest of rows are warehouse names; under warehouse names are numbers .

internal number warehouse1 warehouse2 warehouse3
abc 1 0 1
def 0 2 0

Using the example above I need to make a new table(in a new sheet) that will collect the data like this:

Internal number Location Sales
abc ware1 1
abc ware3 1
def ware2 2

I tried a lot of formulas but none did help me prepare what i want. Anyone got an ideea? I attached an bigger example so anyone who tries to help me can do that in that table.

**mike7952** · 12-27-2012, 12:18 PM

here' one way

Sub abc()
 Dim a, b, i As Long, ii As Long, n As Long
 
 With Worksheets("sheet1")
    a = .Range("I1").CurrentRegion
 
    ReDim b(1 To Rows.Count, 1 To 3)
    
    For i = 2 To UBound(a, 1)
       For ii = 2 To UBound(a, 2)
           If Not IsEmpty(a(i, ii)) And a(i, ii) > 0 Then
               n = n + 1
               b(n, 1) = a(i, 1)
               b(n, 2) = a(1, ii)
               b(n, 3) = a(i, ii)
           End If
       Next
    Next
    .Cells(6, "c").Resize(n, 3) = b
 End With
End Sub

**zepelin** · 12-28-2012, 03:57 AM

your formula is what i need.
how can i bring the new data to a new sheet? so that my first table remains untouched

**Kevin UK** · 12-27-2012, 12:53 PM

Hi zepelin

Will the INDEX & MATCH achieve those results! In E6 & copy down, adjust the ranges to suit your needs.

Formula:

=INDEX($J$2:$M$15,MATCH(C26,$I$2:$I$15,0),MATCH(D26,$J$1:$M$1,0))

Kevin

**zepelin** · 12-28-2012, 04:16 AM

this is the table

**mike7952** · 12-28-2012, 06:24 AM

This should do it

Sub abc()
 Dim a, b, i As Long, ii As Long, n As Long
 
 With Worksheets("sheet1")
    a = .Range("a1").CurrentRegion
 End With
 ReDim b(1 To Rows.Count, 1 To 3)

 For i = 2 To UBound(a, 1)
    For ii = 2 To UBound(a, 2)
       If Not IsEmpty(a(i, ii)) And a(i, ii) > 0 Then
           n = n + 1
           b(n, 1) = a(i, 1)
           b(n, 2) = a(1, ii)
           b(n, 3) = a(i, ii)
       End If
    Next
 Next
 With Worksheets("sheet2")
    .Cells(1, "a").Resize(n, 3) = b
 End With
End Sub