Search multiple cells that contain multiple values and return specific column

[sl0140]

Hi guys,

Have a tough question for you. I have a listing in excel that contains the details of many sql queries. in each cell.

In another sheet, i have a listing of tables. I want to go through the listing of tables, and search each cell in the querie sheet and return the name of the querys where that table has been used. I have been able to do this for the first occurance of a table, but unable to find something that will continue to search and return all occurances.

Say i have a table called A in the table sheet.

In the querie sheet i may have 5 queries in rows B1 to B5. Of this, tbale A is used in 3 of those querys. I want to return the name of the queries that use this table which are held in tha adjacant cell in column A. this can be seperated by commas so we can return all querys names into one cell.

Does anyone have any of how we can do this???

Thanks
Scotty

 

[cgcamal]

Hi sl0140 and welcome!

May you show some input lines and desired output?

Regards

 

[sl0140]

Thanks for the reply.

ok, some test values would be:

Sheet1:

Column A

TABLE1
TABLE2
TABLE3

Sheet2:

Column A Column B

QUER1 select * from table1
QUER2 select * from table2
QUER3 Select * from table1 right outer join table2 on pn
QUER4 select * from table1 right outer join table3 on date
QUER5 select * from table3

expected results....

Sheet1:

ColumnA ColumnB
TABLE1 QUER1,QUER3,QUER4
TABLE2 QUER2,QUER3
TABLE3 QUER4, QUER5


Hope that makes it more clear..

Thanks for the help...

SCotty

 

[ZVI]

Hi Scotty,
Try:

' ZVI:2011-04-29 http://www.mrexcel.com/forum/showthread.php?t=546643
Sub GetStats()
  
  Dim Rng As Range, a1, a2, i&, r&, v, qr$, tb$
  
  ' Put list of tables into a1()
  With Sheets(1)
    If .FilterMode Then .ShowAllData
    Set Rng = .Range("B1", .Cells(.Rows.Count, "A").End(xlUp))
    a1 = Rng.Value
  End With
  
  ' Put list of queries & commands into a2()
  With Sheets(2)
    If .FilterMode Then .ShowAllData
    a2 = .Range("B1", .Cells(.Rows.Count, "A").End(xlUp)).Value
  End With

  ' Main
  With CreateObject("Scripting.Dictionary")
    
    .CompareMode = 1
    
    ' Build dictionary of the tables
    For r = 1 To UBound(a1)
      tb = Trim(a1(r, 1))
      If Len(tb) Then .Item(tb) = r
      a1(r, 2) = Empty
    Next
    
    ' Concatenate tables found in words of commands in 2nd column of a1()
    For r = 1 To UBound(a2)
      qr = Trim(a2(r, 1))
      For Each v In Split(a2(r, 2))
        If Len(v) Then
          If .Exists(v) Then
            i = .Item(v)
            a1(i, 2) = a1(i, 2) & ";" & qr
          End If
        End If
      Next
    Next
    
    ' Copy a1() to Rng
    If .Count Then
      Rng.Value = a1
    End If
    
  End With
  
End Sub

Sheet2 layout:

Excel Workbook
	A	B
1	QUER1	select * from table1
2	QUER2	select * from table2
3	QUER3	Select * from table1 right outer join table2 on pn
4	QUER4	select * from table1 right outer join table3 on date
5	QUER5	select * from table3
Sheet

Sheet1 before macro:

Excel Workbook
	A	B
1	TABLE1
2	TABLE2
3	TABLE3
Sheet

Sheet1 after macro:

Excel Workbook
	A	B
1	TABLE1	;QUER1;QUER3;QUER4
2	TABLE2	;QUER2;QUER3
3	TABLE3	;QUER4;QUER5
Sheet

Regards

 

[ZVI]

Improved code:

Sub GetStats()
  
  Dim Rng As Range, a1, a2, i&, r&, v, qr$, tb$
  
  ' Put list of tables into a1()
  With Sheets(1)
    If .FilterMode Then .ShowAllData
    Set Rng = .Range("B1", .Cells(.Rows.Count, "A").End(xlUp))
    a1 = Rng.Value
  End With
  
  ' Put list of queries & commands into a2()
  With Sheets(2)
    a2 = .Range("B1", .Cells(.Rows.Count, "A").End(xlUp)).Value
  End With

  ' Main
  With CreateObject("Scripting.Dictionary")
    
    .CompareMode = 1
    
    ' Build dictionary of the tables
    For r = 1 To UBound(a1)
      tb = Trim(a1(r, 1))
      If Len(tb) Then .Item(tb) = r
      a1(r, 2) = Empty
    Next
    
    ' Concatenate tables found in words of commands in 2nd column of a1()
    For r = 1 To UBound(a2)
      qr = Trim(a2(r, 1))
      For Each v In Split(a2(r, 2))
        If Len(v) Then
          If .Exists(v) Then
            i = .Item(v)
            a1(i, 2) = a1(i, 2) & IIf(Len(a1(i, 2)), ",", "") & qr
          End If
        End If
      Next
    Next
    
    ' Copy a1() to Rng
    If .Count Then
      Rng.Value = a1
    End If
    
  End With
  
End Sub

Sheet2 layout:

Excel Workbook
	A	B
1	QUER1	select * from table1
2	QUER2	select * from table2
3	QUER3	Select * from table1 right outer join table2 on pn
4	QUER4	select * from table1 right outer join table3 on date
5	QUER5	select * from table3
Sheet

Sheet1 before macro:

Excel Workbook
	A	B
1	TABLE1
2	TABLE2
3	TABLE3
Sheet

Sheet1 after macro:

Excel Workbook
	A	B
1	TABLE1	QUER1,QUER3,QUER4
2	TABLE2	QUER2,QUER3
3	TABLE3	QUER4,QUER5
Sheet

 

[cgcamal]

Hi sl0140,

Another option as follow.

Sub Queries_for_Table()
    
 Sheets("Sheet1").Select
 
    lq = Sheets("Sheet2").Range("A1").End(xlDown).Row
    lt = Range("A1").End(xlDown).Row
    Range("B1:B" & lt).ClearContents
  
    For i = 1 To lt
        TQ = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Range("B1:B" & lq), "*" & Range("A" & i) & "*")
        CAdd = "A1"

        For j = 1 To TQ
            QTl = Sheets("Sheet2").Cells.Find(What:=Range("A" & i), After:=Range(CAdd), LookIn:=xlFormulas, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False).Row
            CAdd = "B" & QTl
 
            Cells(i, 2) = Cells(i, 2) & "," & Sheets("Sheet2").Range("A" & QTl)
            
            If j = TQ Then
                Cells(i, 2) = Application.WorksheetFunction.Substitute(Cells(i, 2), ",", "", 1)
            End If

        Next
    Next

End Sub

Regards

 

[sl0140]

Thanks guys, both solutions worked in the end, this has made my analysis a lot easier..

Many thanks once again

Scotty