EXCEL - transpose data in column 3

[cutestuff]

hi,

I have a question I'm hoping someone can help me out.
I have the following table (in access/excel):

Xref Drawing # Tag #
VP300-G-1-1 300D-24A
VP300-G-1-1 300D-24B
VP300-G-1-1 300G-24A
VP300-G-1-1 300G-24B
VP300-G-103-1 300G-28B
VP300-G-103-1 300G-28C
....

I would like to somehow manipulate this data so the table will look like this:
Xref Drawing # Tag # Tag # Tag # Tag #
VP300-G-1-1 300D-24A 300D-24B 300G-24A 300G-24B
VP300-G-103-1 300G-28B 300G-28C

I have over 7000 rows in this file. If anyone has any ideas, (in Excel or Access) I'd really, really appreciate it.

Thanks so much!
jen

 

[PeterMoran]

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:

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

 

[yogia]

Hi Jen:

Let me see if I have understood you correctly in my following illustration of a formula based aproach ...

In cells D1 through D3, I first created a list of unique Drawing numbers using AdvancedFilter, and then I used an array formula in cell E2 and then copied that to cells E2 through J3.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[cutestuff]

hi,

You guys are GENIUSES!
It's working now.
(I ended up using Peter's approach since I couldn't figure out how to modify Yogi's formula to show values for the other columns. But I did try both of them).

Thanks so much! Appreciate it!

 

[PeterMoran]

Hi,

Thanks for the plaudits Jen.

I must admit I was most impressed with Yogi's solution and have given it another star, but I had difficulty getting it to work because I had to transcribe the formula and that took quite a while to get right. I think you would have used Yogi's approach if you had been able to copy and paste the formula.

By the way for the purposes of consistency my VLOOKUPNTH formula in 7 will replace the VLOOKUP formula in 4 as coded.

Good Luck!

Peter Moran

 

[yogia]

Thanks Jen ... I am sure Peter joins me in saying we are glad you found a solution and you have successfully implemented Peter's VBA solution to your satisfaction.

And Peter ... Thanks for your kind remarks buddy ... now let us keep EXCELing!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com