Binding Data sheets

[Guest]

I am working on a project that requires that data from two sheets be linked on a third. The linking fields would a number field. The first sheet is called “General Data†and the second sheet is called “Data Issuesâ€

General Data’s second column contains a number per record: 10023233 which is the main record for the product line. The “Data Issues†sheet contains related issues to every record which can be multiple records (kind of like a main form / sub-form relationship in Access, or parent/child relationship) in nature.

I need to bring these two data sources together on a third sheet where the resulting data can be calculated for time line values

Are there functions that can be used to accomplish the above.

 

[Frank Kabel]

I am working on a project that requires that data from two sheets be
linked on a third. The linking fields would a number field. The first
sheet is called “General Data†and the second sheet is called “Data
Issuesâ€

General Data’s second column contains a number per record: 10023233
which is the main record for the product line. The “Data Issuesâ€
sheet contains related issues to every record which can be multiple
records (kind of like a main form / sub-form relationship in Access,
or parent/child relationship) in nature.

I need to bring these two data sources together on a third sheet
where the resulting data can be calculated for time line values.

Are there functions that can be used to accomplish the above.

Hi Terry
you may have a look at the function VLOOKUP:
- e.g. to add information from the General Data to your data issue:
=VLOOKUP(A1,'General Data'!$A$1$E$1000,2,0)

this will add data from the 2nd column out of General data where the
number equals the number in A1 (assumption: in both sheets column A
stores your number)

Frank

 

[onedaywhen]

kind of like a main form / sub-form relationship in Access, or parent/child

relationship

Surely the correct MS Access analogy is a JOIN?!

Because you are familiar with MS Access then a 'database' solution is
most appropriate. You can query your workbook as an ADO data source
using the OLEDB provider for Microsoft Jet i.e. ADO in VBA code (MS
Access or Excel). Or you could use linked tables in MS Access, queries
alone, etc - the most appropriate for your circumstances.

Your query would use Jet's SELECT..INTO syntax to join the two sheets
using the key column and create the third e.g.

SELECT T1.*, T2.*
INTO MyNewSheet
FROM [General Data] T1
INNER JOIN
[Data Issues] T2
ON T1.MyKeyCol = T2.MyKeyCol

Note if using ADO, the workbook should be closed when queried to avoid
the memory leak bug.

--

 

[Guest]

Thanks for the VLookUp suggestion. However, the numeric values in column "B" in the General Data are not the same or in ascending order. The product numbers change based on how they come into the office, thus the values in the Data Issues sheet will be pretty much in the same order as entered on the primary sheet.

I simply want a method of grabbing the values from both sheets and plug/place them on a third ("DataResults") sheet as they are entered on the "General Data" and the corresponding "Data Issues" sheets. The third sheet ("DataResults") will be a hidden sheet. Resulting values from here will be pulled into a fourth sheet for calculating.

The Product numbers will be on the "General Data" sheet starting at (B11:B61), the same/corresponding number will be on the "Data Issues" sheet in column (A11:A350). I'd like to marry the two on a third sheet "DataResults" starting in Column (A11:A350)

 

[Frank Kabel]

Thanks for the VLookUp suggestion. However, the numeric values in
column "B" in the General Data are not the same or in ascending
order. The product numbers change based on how they come into the
office, thus the values in the Data Issues sheet will be pretty much
in the same order as entered on the primary sheet.

I simply want a method of grabbing the values from both sheets and
plug/place them on a third ("DataResults") sheet as they are entered
on the "General Data" and the corresponding "Data Issues" sheets. The
third sheet ("DataResults") will be a hidden sheet. Resulting values
from here will be pulled into a fourth sheet for calculating.

The Product numbers will be on the "General Data" sheet starting at
(B11:B61), the same/corresponding number will be on the "Data Issues"
sheet in column (A11:A350). I'd like to marry the two on a third
sheet "DataResults" starting in Column (A11:A350)

Hi Terry
maybe you can post some example rows (in plain text) for all three
sheets. Some questions up-front:
do you have only unique entries in both sheets?. What are the
differences between both lists, etc.

In addition: for VLOOKUP to function a sorting is not required if you
are looking for exact matches. On way to achieve your goal could be:
1. Reference your Data_Results rows on the third sheet with a simple
='Data_Results'!A1 placed in A1 on the third sheet and copied down/ to
the left.
2. To combine these with the data from the General sheet you can either
use VLOOKUP or a combination of MATCH and INDEX

Frank