Extract part of list (vlookup ?)

[VegasNath]

Hi,

I have a list (columns A:K) that has unique items in some of the columns, and grouped references in column A. I am attempting to create a new list on another tab that will return all items that meet the grouped reference criteria. I can get to the first item using "=VLOOKUP($A$1,'Contacts (2)'!$A:$K,1)" & "=VLOOKUP($A$1,'Contacts (2)'!$A:$K,2)" etc, but how can I get to the subsequent items to make up the new list. I can sort the original list by column A (the group reference) if I have to, but ideally would prefer not to IF possible.

[VegasNath]

I'm wondering if it would be easier to use vba to copy and paste the relevant items from the required group?

[HansV]

I don't understand your description. Could you try to explain more clearly, and/or provide an example?

[VegasNath]

The more I think about it, I don't think there is a formula way of doing this. I'm thinking that I need some vba to request the group reference from the user, then using the group reference, loop through sheet 1 column A for a match, if found, copy the row to the next blank row in sheet 2, thus creating a new list of items that meets the group criteria.

[HansV]

It sound like a job for Advanced Filter...

[VegasNath]

It sound like a job for Advanced Filter...

That won't work, I need the information in specific referenced cells.

[HansV]

If you would like help, you'll have to explain clearly, precisely and fully what exactly you want. Currently I don't have the slightest idea.

[VegasNath]

Hans,
Does the attached help?

[HansV]

If you create a small criteria range (A1:A2 in the screenshot below), Advanced Filter will copy the records with the specified Ref.

You can't see the entire list range in the screenshot, it's the source range 'Contacts (2)'!$A$1:$K$101.

x247.png

The source range doesn't have to be sorted by Ref for this to work.

[HansV]

If you prefer to use a macro, here is the VBA equivalent of my previous reply:

Code: Select all

Worksheets("Contacts (2)").Range("A1").CurrentRegion.AdvancedFilter _
  Action:=xlFilterCopy, _
  CriteriaRange:=Worksheets("Email Generator").Range("A1:A2"), _
  CopyToRange:=Worksheets("Email Generator").Range("A4:K4"), _
  Unique:=False

I've specified both the source and target sheets explicitly, so that it doesn't matter which sheet is active when you run the code.

[VegasNath]

Thanks Hans, I have never used advanced filter before. This could work, but is there any way to auto change the advanced filter when the ref in A2 is changed? I will have a lot of use for this which will need to be speedy.

[HansV]

Yes, by using the Worksheet_Change event of the Email Generator sheet:
- Right-click the sheet tab of Email Generator.
- Select View Code from the popup menu.
- Enter or copy/paste the following code:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("A2"), Target) Is Nothing Then
    Application.EnableEvents = False
    Worksheets("Contacts (2)").Range("A1").CurrentRegion.AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("A1:A2"), _
      CopyToRange:=Range("A4:K4"), _
      Unique:=False
    Application.EnableEvents = True
  End If
End Sub

When you edit the value in A2, the Advanced Filter action will be executed automatically.

[VegasNath]

Great, Thanks Hans, that's brilliant!

[VegasNath]

This should probably be a new thread, but it does carry on from the advanced filter...

The advanced filter can return 1 or more results. I need to add each result into an email as a bullet point. The following is a snippet of the code that I am using:

Code: Select all

....snip
strbody = "Hi " & Worksheets("Email Generator").Range("F5") & "," & "<br><br>" & _
          "Request for documentation update:" & "<br><br>" & _
          "" & "<br><br>" & _
          "Please could you confirm if there are any changes to the above documentation that you have previously provided?" & "<br><br>" & _
          "Kind Regards," & "<br><br>" & _
          Signature & "<br>"

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = Worksheets("Email Generator").Range("E5")
        .CC = Worksheets("Email Generator").Range("D5")
        .Subject = strTitle
        .HTMLBody = strbody
        '.Attachments.Add strFile
        .Display
        .ReadReceiptRequested = True
    End With

snip.......

Any idea how I can get the bulleted list into the strBody?

[HansV]

What do you want the bulleted list to contain?

[VegasNath]

A concatenate of various cells within each filtered item, something like:

"Ref " & Worksheets("Email Generator").Range("G5") & " - " & Worksheets("Email Generator").Range("H5") & " - " & Worksheets("Email Generator").Range("I5")

The 5 obviously changing for each filtered row.

[HansV]

Here is code that builds a string for the bulleted list; you can concatenate it into strbody at the appropriate place.

Code: Select all

Dim r As Long
Dim m As Long
Dim strList As String
With Worksheets("Email Generator")
  m = .Cells(Rows.Count, 1).End(xlUp).Row
  strList = "<ul>" & vbCrLf
  For r = 5 To m
    strList = strList & "<li>Ref " & .Cells(r, 7) & " - " & .Cells(r, 8) & " - " & .Cells(r, 9) & "</li>" & vbCrLf
  Next r
  strList = strList & "</ul>"
End With

[VegasNath]

Thanks Hans, works great.