Extract part of list (vlookup ?)
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Extract part of list (vlookup ?)
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.
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
I'm wondering if it would be easier to use vba to copy and paste the relevant items from the required group?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
I don't understand your description. Could you try to explain more clearly, and/or provide an example?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
That won't work, I need the information in specific referenced cells.HansV wrote:It sound like a job for Advanced Filter...
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
Hans,
Does the attached help?
Does the attached help?
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
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.
The source range doesn't have to be sorted by Ref for this to work.
You can't see the entire list range in the screenshot, it's the source range 'Contacts (2)'!$A$1:$K$101.
The source range doesn't have to be sorted by Ref for this to work.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
If you prefer to use a macro, here is the VBA equivalent of my previous reply:
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.
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
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:
When you edit the value in A2, the Advanced Filter action will be executed automatically.
- 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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
Great, Thanks Hans, that's brilliant!
Nathan
There's no place like home.....
There's no place like home.....
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
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:
Any idea how I can get the bulleted list into the strBody?
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.......
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
What do you want the bulleted list to contain?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
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.
"Ref " & Worksheets("Email Generator").Range("G5") & " - " & Worksheets("Email Generator").Range("H5") & " - " & Worksheets("Email Generator").Range("I5")
The 5 obviously changing for each filtered row.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of list (vlookup ?)
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Extract part of list (vlookup ?)
Thanks Hans, works great.
Nathan
There's no place like home.....
There's no place like home.....