Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2011, 12:50 AM
Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need Help with Multiple Lookups

Hello all,
I have a file with the following column headings:

1> Master list of Part Numbers (15000 entries)
2> My Part List (1400 entries)
3> Station Number (15000 entries)

For a basic explanation, a part number can be present at multiple station numbers.

What I need to do is find out all the (STATION NUMBERS) for (MY PART NUMBERS) from the (MASTER LIST OF PART NUMBERS)

Please help.
 
Old December 16th, 2011, 02:04 AM
Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ill be more specific:
There are two excel files:

File 1 has the following columns:
1> Master list of Part Numbers (15000 entries)
and the corresponding
2> Station Number (15000 entries)
for example:
Master list Station Number
1> Part a 1
2> Part b 2
3> Part c 3
4> Part b 5
5> Part b 8
6> Part d 6


File 2 has:
1> My Part List (1400 entries)
for example:
My Part list
1> Part b
2> Part c
3> Part d


Now, a part number in the (Master list of Part Numbers) can be present at multiple (Station Number).
What I need to do is get all the (Station Number) for the entries in (My Part List).
So according to the given example, I need to find out the station numbers for Parts b,c and d.
Part b will have station number as:2, 5 and 8
Part c will have station number as:3
Part d will have station number as:6
 
Old December 16th, 2011, 12:17 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

There are multiple ways to do this

1. Loop through the list and use Find function to check the availability of partnumbers in the master file

2. Use Excel ADO - that is use the master file as the database - Query for presence of record using ADO (http://vbadud.blogspot.com/2008/05/u...-database.html)

3. USe Excel ADO - consider both the Master and Data file as Databases, run a join query and get the result in new sheet

While you try these approach some wizards in this forum might come with new techniques too

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old December 16th, 2011, 12:17 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

There are multiple ways to do this

1. Loop through the list and use Find function to check the availability of partnumbers in the master file

2. Use Excel ADO - that is use the master file as the database - Query for presence of record using ADO (http://vbadud.blogspot.com/2008/05/u...-database.html)

3. USe Excel ADO - consider both the Master and Data file as Databases, run a join query and get the result in new sheet

While you try these approach some wizards in this forum might come with new techniques too

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookups Gajun Access 5 September 20th, 2007 07:26 AM
Counting Lookups elsewhere in the XML ashcarrot XSLT 5 July 17th, 2006 11:09 AM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Multiple lookups in same table kev_79 Access 3 February 15th, 2004 05:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.