Hi jen,
There are a number of steps in achieving what you want.
1. With the "Xref Drawing" col selected use Data, Filter, Advanced Filter, to copy the Unique records to another location maybe on a separate sheet in Col A.
2. Select all your original data and use Insert, Name, Define, and call the data "Table".
3. You will need to know the max no of tags related to any one drawing, or at least have an idea as to whether it is 3 or 30.
4. If you have your unique list in col A of another sheet then in col B of row 2 enter the following formula:
=VLOOKUP($A2,Table,2,0)
this can be copied vertically down col B to give the FIRST entry for each Drawing. Check that this is the case.
5. That is the easy part, next we need to load a special Function which I call VLOOKUPNTH to enable the subsequent entries for each drawing to be found.
6. Press Alt+F11(Func Key) to display the Visual Basic window. In the Project Window (usually top left) select the file (project) you are working with (it will be in brackets) and select Insert, Module, and you should see a clean screen on the main work area of the screen. Now copy the following code and paste it at the cursor:
Code:
Function VLOOKUPNTH(lookup_value, _
table_array As Range, _
col_index_num As Integer, _
Optional nth_value, _
Optional Srch_Col_Offset)
' Extension to VLOOKUP function. Allows for finding
' the "nth" item that matches the lookup value.
' 2/1/05 - Modified to handle Wildcards - "=" changed to "like"
' 6/2/07 - Modified to assign Value rather than Text
' - nth_value optional, default is 1 (identical to VLOOKUP)
' - Srch_Col_Offset optional - Offset to specify Col to be searched
' (Enables searching on any column in table or array)
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
Dim nth As Integer
Dim s_col_offset As Integer
If IsMissing(nth_value) Then nth = 1 Else nth = nth_value
If IsMissing(Srch_Col_Offset) Then s_col_offset = 1 Else s_col_offset = Srch_Col_Offset
VLOOKUPNTH = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, s_col_offset).Value Like lookup_value Then
'If .Cells(nRow, 1).Value = lookup_value Then
nVal = nVal + 1
End If
If nVal = nth Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Value
'VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
Next nRow
End With
End Function
7. Now return to the sheet with the output table we are creating. Copy the following formula into cell C2:
=VLOOKUPNTH($A2,Table,2,COLUMN()-1)
This looks at the Table and finds the second entry (COLUMN() -1 = 3-1 = 2) for the first drawing. If this is the case, then great! This formula can be copied to all other cells in which you expect to see data. You will see "Not Found" in cells for which there is no data. This can be modified if required.
You should now have your data transposed as required!
Good Luck!
Peter Moran
