Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query between worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query between worksheets
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query between worksheets
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query between worksheets
Thanks a lot. I'll give this a shot today.
Andy On Fri, 18 Jun 2004 04:10:01 -0700, "jngi" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Add worksheets with Web Query | Excel Discussion (Misc queries) | |||
Query worksheets in same workbook | Excel Worksheet Functions | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) |