Lookup result needed

[pia]

I need a lookup formula to look in column B that has a 1 and return the name in column A that coresponds in column B But ignore duplicates and to look through multiple worksheets. Can this be done through a formula or do I need a vba solution?

[geekgirlau]

You will need a VBA solution for this.

I would recommend as a starting point that you look at the Advanced Filter - this can be easily recorded then adapted to suit your purpose:

• The Advanced Filter requires a Database range (your data) and a Criteria range (consisting of the field name as it appears in your data, and the value you are looking for).
• Using the Advanced filter, you can opt to copy the results to a new location on the same sheet, and to show unique values only.
• Once the resulting data has been copied, you can then copy that new range to wherever you want it to appear.

Oh, and welcome to the Board!

[pia]

Thanks geekgirlau but how to have dynamically? Also do you recommend anyone on this forum that can do it with formulas?

[Charlize]

Probably not the best coding on earth but this will get you started. Select cell with number in it and run macro.
[vba]Sub get_data()
Dim lookup As Long
Dim result As Variant
Dim a_items As Long
Dim already_result()
Dim name As String
Dim checkname As String
Dim lookuprange As Range
Dim lookuprange2 As Range
Dim notinarray As Boolean
Dim writing As Long
Dim dest As Long
Set lookuprange = Worksheets(2).Range("B2:B12")
Set lookuprange2 = Worksheets(3).Range("B2:B12")
lookup = ActiveCell.Value
dest = ActiveCell.Row
For Each result In lookuprange
If result = lookup Then
If a_items < 1 Then
a_items = 1
ReDim Preserve already_result(a_items)
already_result(a_items) = result.Offset(, -1).Value
Else
checkname = result.Offset(, -1).Value
For writing = 1 To a_items
name = already_result(writing)
If checkname <> name Then
notinarray = True
Else
notinarray = False
GoTo processnext
End If
Next writing
processnext:
If notinarray = True Then
a_items = a_items + 1
ReDim Preserve already_result(a_items)
already_result(a_items) = result.Offset(, -1).Value
End If
End If
End If
Next result
For writing = 1 To a_items
Worksheets(1).Range("D" & dest).Value = already_result(writing)
dest = dest + 1
Next writing
End Sub[/vba]
Charlize

[moa]

Sounds like a big ask to do this in a formula but xld would be the one to ask (No pressure Bob).

[Shazam]

Try...

Type these formulas.
Got to Insert>>>Define>>>Name


Name refer to:
Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))
Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))
N =100
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3
XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1))

And then input this formula in cell D2 and copied down.

=INDEX(Col_A,MATCH(0,IF(Col_B=$C$2,COUNTIF($D$1:D1,Col_A)),0))

Formula is an-array need to hold down:

Ctrl,Shift,Enter

Hope it helps!

[Charlize]

Variation ...[VBA]Sub get_data()
Dim lookup As Long
Dim result As Variant
Dim a_items As Long
Dim already_result()
Dim name As String
Dim checkname As String
Dim lookuprange As Range
Dim notinarray As Boolean
Dim writing As Long
Dim dest As Long
'lookup range loop
Dim lrl As Long
Dim no_loops As Long
no_loops = Application.InputBox("How many sheets for a range ?", , , , , , , 1)
If no_loops <= 0 Then
Exit Sub
Else
lookup = Worksheets(1).Range("C2").Value
dest = Worksheets(1).Range("C2").Row
For lrl = 1 To no_loops
Set lookuprange = Application.InputBox("Select a range", _
"Select range to search in", , , , , , 8)
'Set lookuprange = Worksheets(2).Range("B2:B12")
'Set lookuprange2 = Worksheets(3).Range("B2:B12")
For Each result In lookuprange
If result = lookup Then
If a_items < 1 Then
a_items = 1
ReDim Preserve already_result(a_items)
already_result(a_items) = result.Offset(, -1).Value
Else
checkname = result.Offset(, -1).Value
For writing = 1 To a_items
name = already_result(writing)
If checkname <> name Then
notinarray = True
Else
notinarray = False
GoTo processnext
End If
Next writing
processnext:
If notinarray = True Then
a_items = a_items + 1
ReDim Preserve already_result(a_items)
already_result(a_items) = result.Offset(, -1).Value
End If
End If
End If
Next result
Next lrl
End If
For writing = 1 To a_items
Worksheets(1).Range("D" & dest).Value = already_result(writing)
dest = dest + 1
Next writing
End Sub[/VBA]Charlize