Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookups | Excel Discussion (Misc queries) | |||
Lookups | Excel Discussion (Misc queries) | |||
Lookups or something | Excel Worksheet Functions | |||
Maybe I need help with Lookups?? | Excel Worksheet Functions | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |