Help with Lookups
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... |
Help with Lookups
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 "silentpro" wrote in message ... 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... |
All times are GMT +1. The time now is 08:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com