LookUp/Match Question

[carl]

My data table looks like this:

Date AA BAC DELL
20071203 4.83% 19.72% 7.50%
20071204 6.27% 10.49% 10.02%
20071205 10.61% 2.12% 5.32%
20071206 5.31% 20.33% 12.82%
20071207 8.64% 7.70% 2.97%

I am looking for a formula for ColC of this table:

Date Name Percent
20071203 AA 4.83%
20071203 BAC 19.72%
20071203 DELL 7.50%
20071204 AA 6.27%
20071204 BAC 10.49%
20071204 DELL 10.02%


Thank you in advance.

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastItem As Long
Dim NextRow As Long

With ActiveSheet

Application.ScreenUpdating = False
LastItem = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
.Columns(2).Insert
For i = LastItem To 2 Step -1
.Rows(i + 1).Resize(2).Insert
.Cells(i + 2, "A").Value = .Cells(i, "A")
.Cells(i + 2, "C").Value = .Cells(i, "E")
.Cells(i + 2, "B").Value = .Cells(1, "E")
.Cells(i + 1, "A").Value = .Cells(i, "A")
.Cells(i + 1, "C").Value = .Cells(i, "D")
.Cells(i + 1, "B").Value = .Cells(1, "D")
.Cells(i, "B").Value = .Cells(1, "C").Value
.Cells(i, "D").Resize(, 2).ClearContents
Next i
.Range("B1:E1") = Array("Name", "Percent", "", "")
Application.ScreenUpdating = True
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

With this table in the range A26 -

20071203 4.83% 19.72% 7.50%
20071204 6.27% 10.49% 10.02%
20071205 10.61% 2.12% 5.32%
20071206 5.31% 20.33% 12.82%
20071207 8.64% 7.70% 2.97%

With this table in the range A11:B16 -

20071203 AA
20071203 BAC
20071203 DELL
20071204 AA
20071204 BAC
20071204 DELL

Enter this formula in C11 and copy down as needed:

=INDEX(B$2$6,MATCH(A11,A$2:A$6,0),MATCH(B11,B$1$1,0))

Format as %