Help with Lookups

[silentpro]

My apologies if this is a duplicate post.....



Maybe I should've done this to begin with, but here's my example

From worksheet MAIN

ALLEN, FRED ZAOP046145 COURSE Red COURSER12047
ALLEN, FRED ZAOP046145 COURSE Blue COURSEBL451815
ALLEN, FRED ZAOP046145 COURSE Yellow COURSEYEL014784754
ALLEN, FRED ZAOP046145 COURSE Green COURSEG654
ALLEN, FRED ZAOP046145 GREY Course GREY4578416

The above would be in one worksheet


In another worksheet, let's say fred is beginning on row A2
I would like to display the results as such..
Results I would like to display

NAME ID COURSE RED
COURSE BLUE COURSE YELLOW COURSE GREEN GREY
COURSE
ALLEN, FRED ZAOP046145 COURSER12047 COURSEBL451815
COURSEYEL014784754 COURSEG654 GREY4578416


thanks...

 

[Bernie Deitrick]

I would use a macro.

For a four column database, with headers in row 1, select a cell in the database and run the macro
below.

HTH,
Bernie
MS Excel MVP

Sub DBtoCrossTab3()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long
Dim myRow2 As Long
Dim myCol As Integer

Set myTable = ActiveCell.CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab"

Set myTable = myTable.Offset(1, 1).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

mySht.Range("A1").Value = myTable.Columns(1).Cells(0, 0).Value
mySht.Range("B1").Value = myTable.Columns(1).Cells(0, 1).Value
For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("B:B"), False)) Then
myRow2 = mySht.Range("B65536").End(xlUp)(2).Row
mySht.Range("B" & myRow2).Value = myCell.Value
mySht.Range("A" & myRow2).Value = myCell(1, 0).Value
End If
If IsError(Application.Match(myCell(1, 2).Value, _
mySht.Range("1:1"), False)) Then
mySht.Range("IV1").End(xlToLeft)(1, 2).Value = myCell(1, 2).Value
End If

myRow = Application.Match(myCell.Value, _
mySht.Range("B:B"), False)
myCol = Application.Match(myCell(1, 2).Value, _
mySht.Range("1:1"), False)

mySht.Cells(myRow, myCol).Value = myCell(1, 3).Value

Next myCell

End Sub