Making a list

[jimayers]

Hi, am having trouble looking up info, but particularly making a list that looks different from the list I extract infofrom. I have tried VLOOKUP with no success, but am working on using .find andoffset, but am having trouble making a list without leaving blank rows.<o></o>
<o> </o>
What I have so far:<o></o>
Private Sub Worksheet_SelectionChange(ByValTarget As Range)<o></o>
<o> </o>
Dim cRNG As Range<o></o>
Set cRNG =Worksheets(2).Range("C:C")<o></o>
With cRNG<o></o>
Call Lookup(Target)<o></o>
End With<o></o>
End Sub<o></o>
<o> </o>
THEN<o></o>

Sub Lookup(Target As Range)<o></o>
Dim pFind As Range<o></o>
<o> </o>
Set pFind =Sheets(1).Range("A:Z").Find(What:=Target.Value, LookIn:=xlValues,LookAt:=xlPart).Offset(, -1).Resize(1, 3)<o></o>
Target.Offset(, -1).Resize(1, 3).Value =pFind.Value<o></o>
End Sub<o></o>
<o> </o>

What I am trying to do is lookup up a value (such as "Q1010") on aworksheet(1) and when found, put the values from its left and right cells on alist in another sheet(2) in a different order.

So (sorry about formatting) it looks something like this:
worksheet(1)

……………..A……………..B………………….C……………………D…………………………E<o></o>
ROW<o></o>
1………..name1……….add1………….Q1010…………..phone1……………otherinfo1
2………..name2……….add2………….Q1011…………..phone2…………… otherinfo2
3………..name3……….add3………….Q1012…………..phone3…………… otherinfo3
4………..name4……….add4………….Q1013…………..phone4…………… otherinfo4


Then, the user can search for Q1010...then Q1013...so on, and have the searchedfor values make a list on sheet(2) but copy over other values from the same row:

worksheet(2)
……………..A……………..B………………….C……………………D…………………………E<o></o>
ROW<o></o>
1…………add1……….Q1010……..otherinfo1………..name1
2…………add4……….Q1013……..otherinfo4………..name4<o></o>
---------------------------------------------
Another feature I have not gotten to yet, but would appreciate help with, is howthe user enters the search value "Q1010." I was thinking of 3 different ways:
1. Use check boxes next to every row, and when the value is found by scrolling throughdata, the user puts a check and that triggers the macro to put "Q1010"cell and the cells to its left and right on the list.
2. click on the cell with containing the desired value, "Q1010," and thattriggers the macro
3. enter the desired value, "Q1010" into a box, userform, cell, whatever.
All three options for user choice of how to search would be ideal.<o></o>

 

[michaelsmith559]

Not sure if this helps, but here is a macro I wrote awhile back to return multiple values for one lookup. It all must be done on one sheet, maybe someone else could modify the code. Also, if using excel 2003, the rows in the code need to be changed to 65536. You can lookup and return values from the left or right of your lookup value. It only returns one column of results at a time, so you cannot select multiple columns. Below I will show how I setup sheet1, show the results after the macro runs, followed by the macro. Maybe it will help, some.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Add	Value	Phone	Info		Add	Value	Info	Name	Info
2	name1	add1	Q1010	phone1	otherinfo1
3	name2	add2	Q1011	phone2	otherinfo2
4	name3	add3	Q1012	phone3	otherinfo3
5	name4	add4	Q1013	phone4	otherinfo4
6	name1	add1	Q1010	phone1	otherinfo1
7	name2	add2	Q1011	phone2	otherinfo2
8	name3	add3	Q1012	phone3	otherinfo3
9	name4	add4	Q1013	phone4	otherinfo4
10	name1	add1	Q1010	phone1	otherinfo1
11	name2	add2	Q1011	phone2	otherinfo2
12	name3	add3	Q1012	phone3	otherinfo3
13	name4	add4	Q1013	phone4	otherinfo4
Sheet1

After macro:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Add	Value	Phone	Info		Add	Value	Info	Name	Info
2	name1	add1	Q1010	phone1	otherinfo1			Q1010		name1
3	name2	add2	Q1011	phone2	otherinfo2					name1
4	name3	add3	Q1012	phone3	otherinfo3					name1
5	name4	add4	Q1013	phone4	otherinfo4
6	name1	add1	Q1010	phone1	otherinfo1
7	name2	add2	Q1011	phone2	otherinfo2
8	name3	add3	Q1012	phone3	otherinfo3
9	name4	add4	Q1013	phone4	otherinfo4
10	name1	add1	Q1010	phone1	otherinfo1
11	name2	add2	Q1011	phone2	otherinfo2
12	name3	add3	Q1012	phone3	otherinfo3
13	name4	add4	Q1013	phone4	otherinfo4
Sheet1

macro:

Sub Multiple_Values_One_LookUp()
Dim myLookUp As Variant, myRange As Range, myReturn As Range, lastRow As Long
Dim lookupRow As Long, r As Long, putLookUpHere As Range, putReturnValuesHere As Range
Dim answer As Integer

answer = MsgBox("Does your lookup column contain a text heading?", vbYesNo + vbQuestion, "Text Heading")

If answer = vbYes Then

myLookUp = InputBox("What value do you want to lookup?")
Set myRange = Application.InputBox(prompt:="Select LookUp Column", Type:=8)
Set myReturn = Application.InputBox(prompt:="Select Column to Return Values From", Type:=8)
Set putLookUpHere = Application.InputBox(prompt:="Where do you want the lookup value placed?", Type:=8)
Set putReturnValuesHere = Application.InputBox(prompt:="Where do you want the return values placed?", Type:=8)

If Application.IsText(Cells(myRange.Rows.Count, myRange.Column).End(xlUp).End(xlUp).Value) = True Then
    lookupRow = myRange.Offset(1, 0).Row
        Else
    lookupRow = myRange.Row
End If

lastRow = myRange.End(xlDown).Row

If IsEmpty(Cells(1048576, putLookUpHere.Column).End(xlUp).Value) = True Then
    Cells(1048576, putLookUpHere.Column).End(xlUp).Value = myLookUp
        Else
    Cells(1048576, putLookUpHere.Column).End(xlUp).Offset(1, 0).Value = myLookUp
End If

For r = lookupRow To lastRow
    If Cells(r, myRange.Column).Value = myLookUp Then
        If IsEmpty(Cells(1048576, putReturnValuesHere.Column).End(xlUp).Value) = True Then
            Cells(1048576, putReturnValuesHere.Column).End(xlUp).Value = Cells(r, myReturn.Column).Value
        Else
            Cells(1048576, putReturnValuesHere.Column).End(xlUp).Offset(1, 0).Value = Cells(r, myReturn.Column).Value
        End If
    End If
Next r

Else
End If

End Sub

To use macro.
Your data must have text headers. It will ask you if your data has text headers, if so, click yes.
1. Type value you want to lookup into box, ex: Q1010
2. Select the column that contains the lookup value, ex: C1
3. Select the column that you want to return values from, ex: B1
4. Select where you want to place the lookup value, ex: H1
5. Select where you want the return values placed, ex: G1

 

[alansidman]

Crossposted: trouble making a list

 

[jimayers]

Thank you for your help. JA