Query between worksheets

[Andy]

I am attempting to find a way to lookup data from multiple rows of one
spreadsheet to populate cells on another. This is similar to the
vlookup function in Excel, but I need to look for every instance of a
string and append them together in a cell.

Here's the layout of the spreadsheets

Sheet1 (Source)

Column A Column B
Dog Ralph
Dog Spike
Cat Whiskers
Cat Fluffy
Cat Muffy

Sheet2 (Destination)

Column A Column B
Dog
Cat
Hamster


Thus what I'm looking to do is lookup the value in Sheet2 Column A in
the rows of Sheet 1 and populate Sheet2 ColumnB. My desired end
result for Sheet 2 would look like this:

Column A Column B
Dog Ralph, Spike
Cat Whiskers, Fluffy, Muffy

The actual worksheets are a couple of thousand rows long.

I've written a bunch of Windows system scripts in VB scripting, so I'm
familiar with how to script, but I've never done anything with VBA or
VB and multiple worksheets.

Can someone give me some pointers on how to proceed?

Thanks,

Andy

 

[mangesh_yadav]

use the following piece of code in a module
Function ModLookUp(Lookup_value, Table_array, Col_index_num)

Dim rng As Range
Dim X, Y
Dim j


X = Lookup_value
j = Col_index_num

Set rng = Table_array

For i = 1 To rng.Rows.Count Step 1
If (X = rng.Cells(i, 1)) Then
If (result = "") Then
result = rng.Cells(i, j)
Else
result = result & ", " & rng.Cells(i, j)
End If
End If
Next

ModLookUp = result

End Function

And then use this function in your column two as follows
=ModLookUp(A1,Sheet1!$A$1:$B$5,2)

- Manges

 

[jngi]

Andy
Try this:

Sheet(1) has the list
Dog Ralph
Dog Spike
Cat Whiskers
Cat Fluffy
Cat Muffy
Hamster Jim
Dog Qaz
Cat Kat
Hamster Bill
Dog Qwerty

Sheet(2) ends up with

Cat Whiskers, Fluffy, Muffy, Kat
Dog Ralph, Spike, Qaz, Qwerty
Hamster Jim, Bill


The code is

Sub populate()

Sheets(2).Activate
For i = 1 To 3 'This is the number of types of animal - dog, cat etc

a = Cells(i, 1).Text 'Select them in order

For j = 1 To 10 'This the number of items in your main list

If Worksheets(1).Cells(j, 1).Text = a Then 'If match

If Cells(i, 2) = "" Then 'first one no comma

Cells(i, 2) = Cells(i, 2) + Worksheets(1).Cells(j, 2).Text

Else 'rest have commas

Cells(i, 2) = Cells(i, 2) + ", " + Worksheets(1).Cells(j, 2).Text

End If
End If
Next
Next

End Sub


OK?

Jon

 

[Andy]

Thanks a lot. I'll give this a shot today.

Andy