Selective extraction of data

[eddy56]

Hi there.

I have this problem and I hope somebody can help me:

A B C
- - - - - - - - - - - - -
1 ! 7320 John Car
2 ! 7320 Marcy Bus
3 ! 7320 Peter Bike
4 ! 1119 John Car
5 ! 1119 Marcy Bus
6 ! 1119 Peter Bike
7 ! 4886 John Car
8 ! 4886 Marcy Bus
9 ! 4886 Peter Bike

I need a formula that returns me the values in column C that matche
the criteria search founded in column A. Example:

=EXTRACTIF(A1:C9,7320,1,3) = {"Car","Bus","Bike"}
or
=EXTRACTIF(A1:C9,7320,1,3) = "Car, Bus, Bike"

Thanks for your help.

Eddy
(e-mail address removed)

 

[Biff]

That'd be a nice function to have!

Since that function doesn't exsist I don't know the syntax. It'd be easier
to TELL us what you want to extract.

Biff

 

[Guest]

Perhaps this. Paste to a standard code module:

Function ExtractIf(SearchRng As Range, ReturnRng As Range, _
SearchVal As String) As String
Dim i As Long
Dim txt As String
For i = 1 To SearchRng.Count
If SearchRng(i).Value = SearchVal Then
txt = txt & ReturnRng(i) & ", "
End If
Next
txt = Left(txt, Len(txt) - 2)
ExtractIf = txt
End Function

Assumed is that the cell range containing the search value (i.e. 444) is
A1:A25 and the range to extract the text is C1:C25. Enter this in the
appropriate worksheet cell:

=ExtractIf(A1:A25, C1:C25, 444)

Note that the two ranges (SearchRng and ReturnRng) need not be aligned -
e.g. could be A1:A25 and E27:E51.

Regards,
Greg

 

[Guest]

Suggested improvement is to substitute:

If Len(ReturnRng(i)) > 0 Then txt = txt & ReturnRng(i) & ", "

for:

txt = txt & ReturnRng(i) & ", "

Greg

 

[eddy56]

Hi Greg. Thanks for your help

It helps me to continuing develop my function. Now I have some mor
elaborated (obviously can't it be made without your help). I don't kne
how to retrieve the range by cols and rows until I see your code an
studied it and the help of Excel.

Let me show you and tell me what do you think about it.

Option Explicit
Option Base 1
Function ExtractIf(SearchRng As Range, cSearchVal As String, nSearchCo
As Integer, nReturnCol As Integer) As String

' Rango de búsqueda, Dato a buscar, Columna donde se buscará, Column
que se regresará

' Propiedades importantes de los rangos:
' SearchRng.Columns.Count = Columnas del rango
' SearchRng.Rows.Count = Filas del rango
' SearchRng.Count = Celdas del rango (Columns * Rows)

' Declaracion de variables
Dim nItem As Integer ' Ciclos
Dim nRows As Integer ' Filas del rango
Dim nCols As Integer ' Columnas del rango
Dim cTxt As String ' Texto a devolver separado po
comas

' Inicialización de variables
nRows = SearchRng.Rows.Count ' Número de filas
nCols = SearchRng.Columns.Count ' Número de columnas
cTxt = "" ' Resultado obtenido

' Depuración de parámetros
' ToDo: What happens with date values in cSearchVal? If No
IsNumeric(nSearchCol) Then nSearchCol = 1
If Not IsNumeric(nReturnCol) Then nSearchCol = nCols
If nSearchCol > nCols Then nSearchCol = 1
If nReturnCol > nCols Then nReturnCol = nCols

' Busca en el rango SearchRng el valor cSearchVal en la column
nSearchCol
' Si encuentra una coincidencia, devuelve el valor de la misma fil
pero en
' la columna nReturnCol
For nItem = 1 To nRows
If cSearchVal = SearchRng(nItem, nSearchCol).Value Then
cTxt = cTxt & SearchRng(nItem, nReturnCol).Value & ", "
End If
Next

' Elimina la última coma cuando sea necesario
If Len(cTxt) > 0 Then
cTxt = Left(cTxt, Len(cTxt) - 2)
End If

' Regresa la lista de los valores obtenidos
' ToDo: How to return an array instead of a String?
' ToDo: How to return the data so it can be used as a
' so it can be displayed in the dropdown listbox of a
' Cellata:Validation? ExtractIf = cTxt
End Functio