Originally Posted by

**Aladin Akyurek**
In post #2, I assumed A2 should be the result while B2, C2, and D2 as the look up values.

If A2 is in fact the look up value and B2:D2 are the results, try...

Aladin

Looking back at post #1, you may well be right with one of those assumptions. I assumed the whole table needed to be created.

Rick,

Your code highlighted to me that my formula solution did not order the results in exactly the same way the OP has. If the whole table does have to be created and that order is important, then I don't see a feasible formula solution. (Too hard for me anyway. )

Need some OP feedback on whether only part of the results table needs to be created (& if so which part) or, if the whole table, whether the exact order is critical.

I think you have just overlooked this red dot .Sort **.**Range("A1"), Header:=xlYes

I think you may also ave missed that there were a number of columns hidden in the OP's screen shot of the data sheet.

My attempted code solution, if the whole table needs to be created** is below.

** I have assumed that the 'ORX' sheet exists and already holds at least headings but nothing else that needs to be kept.

Code:

Sub Make_Results()
Dim a, b, aRws
Dim i As Long, j As Long, k As Long, rws As Long
Const DataCols As String = "5 10 11 15 16 17 18 19 20 21 22 23 24"
With Sheets("INC")
aRws = Evaluate("row(6:" & .Cells(.Rows.Count, 5).End(xlUp).Row & ")")
a = Application.Index(.Cells, aRws, Split(DataCols))
End With
rws = UBound(aRws)
ReDim b(1 To rws * 10, 1 To 5)
For j = 4 To 13
For i = 1 To rws
If Len(a(i, j)) Then
k = k + 1
b(k, 1) = a(i, j)
b(k, 2) = a(i, 1)
b(k, 3) = a(i, 2)
b(k, 4) = a(i, 3)
b(k, 5) = j
End If
Next i
Next j
With Sheets("ORX")
.UsedRange.Offset(1).ClearContents
.Range("A2").Resize(k, 5).Value = b
.UsedRange.Sort Key1:=.Range("E2"), Order1:=xlAscending, Key2:=.Range("A2"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
.Range("E2").Resize(k).ClearContents
End With
End Sub

## Like this thread? Share it with others