Lookup

**dcw0405** · 11-11-2009, 03:09 PM

Hey Guys,

So I'm attaching a sample of what I'm wanting to do. I'm needing a little bit of help.
What I'm trying to do is find all the accounts that say "PayPal" and put them into another column. Then the corresponding values that go next to it, I want those in the table as well..

If you look at my spreadsheet, it makes more sense.

Thanks for all your help!

**JP Romano** · 11-11-2009, 03:52 PM

Not elegant, but it works for me...

This assumes your data starts in A2 and will put your results in columns F-H.

Code:

Dim lastrow As Integer
lastrow = Range("a65536").End(xlUp).Row

Range("A2").Select
Do Until ActiveCell.Row = lastrow
    If ActiveCell.Value = "PayPal" Then
        Row = ActiveCell.Row
        Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)).Copy
        Range("G65536").End(xlUp).Offset(1, 0).Select
        ActiveCell.Offset(0, -1).Value = "PayPal"
        ActiveCell.PasteSpecial
        Range("A" & Row).Select

    End If
    ActiveCell.Offset(1, 0).Select
    Loop

Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
       .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    Range("F2").Select

Let me know if that's what you're after!

**dcw0405** · 11-11-2009, 04:07 PM

Thanks! Is that a VBA or Macro? I'm not sure where to input that on the spreadsheet..

**JP Romano** · 11-11-2009, 04:12 PM

Sorry...my bad. I'm using Excel 2007 and always have problems executing macros from downloaded websites. Do this...
1) Alt + F11 to open the VBA editor
2) Right click on the name of your file and select INSERT / MODULE
3) At the top of the module, enter (without quotes)
"Sub Summarize"
(you'll notice that you automatically get "End Sub" displayed a few lines down)

4) Paste the code I gave you between SUB SUMMARIZE and END SUB

...
Now, you can move your data so that it starts in cell A2

To run the code, click on the little green arrow button in the VBA editor window. Let's make sure it works before doing anything else...