comparing data column in other worksheets and returning certain data fields

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I have 3 spreadsheet. Sheets 1 & 2 have a common field.
If this matches then I need to return the data in other
column associated with the common field, but not the common
fields.

After the data is filled in from the 2nd sheet, then there
will be a common field from sheet 1 & 3. Then I need to
match up those fields and pull over the assocaited data and
fill in the columns. Need help ASAP!!!
 
Assume the common field on Sheet2 and Sheet3 are to the left of the data you
need to retrieve

Assume you need it returned to Column F of Sheet1
in F1:
=vlookup(Sheet1!A1,Sheet2!$A$1:$C$30,2,False)

returns the value from column B on sheet2 for a match in column A on each
sheet

in G1:
=vlookup(Sheet1!F1,Sheet3!$M$1:$R$50,4,False)

Returns the value from column P of sheet3 for a the value in F1 matching
Column M of sheet3


Then drag fill the formulas down the column.
 
WEll lets see. I need to return the values to diffenet
columns. So I need to return the data that are in
different columns to Column F thru I of Sheet 1.

Thanks for all the help. I was looking at the Index and
Match and teh VLookup and trying to determine the formula
format. So if you can expand on your answer that would be
great. I assume I can tell it what field to put in what
column. Hope this is not to complicated.

Mary
 
Guess I didn't make myself clear - sorry. I have 3
different work book and need to extract the data from
them. The sheets are not in the same workbook. And I do
not need to bring over the common field. I am using that
to match and if that does bring the data to the row and
fields that match in workbook 1 - sheet 1 to workbook 2-
sheet. When that happens the that will create a
different common field from Workbook 1 - sheet 1 to
Workbook 3 sheet one. I need to get all the data in
workbook 1 sheet 1.

If you could expand on this - I would like to thank you
very much.

Mary
 
All you need to do is read the help on VLookup so you understand the
arguments and what they do.

It will do what you want if you create the correct references. It doesn't
make any difference if the source data is in other workbooks.

Open all three workbooks and use the mouse to select the look up ranges and
you will see how to make an external reference.
 
I have 3 spreadsheet. Sheets 1 & 2 have a common field.
If this matches then I need to return the data in other
column associated with the common field, but not the common
fields.

After the data is filled in from the 2nd sheet, then there
will be a common field from sheet 1 & 3. Then I need to
match up those fields and pull over the assocaited data and
fill in the columns. Need help ASAP!!!
Click to expand...

What you seem to be looking for is a database inner join. This'd be much easier
to do using Data > Get External Data *if* you know SQL. It could be as simple as

SELECT TBL1.Val_1, TBL2.Val_2
FROM `D:\tmp\tst1`.TBL1 TBL1, `D:\tmp\tst2`.TBL2 TBL2
WHERE TBL1.ID = TBL2.ID

If you don't know SQL, then if you have all three sheets open, and the common ID
field is the first column in sheets 1 and 2, you could try the following
approach. I'll assume the result should begin in A1 in sheet 3. Also, I'll
assume the entire tables in sheets 1 and 2 are named, respectively, TBL1 and
TBL2.

First get the field names from the other tables.

A1:
=INDEX(TBL1,1,2)

B1:
=IF(COLUMNS(TBL1)>COLUMN(),INDEX(TBL1,1,COLUMN()+1),
INDEX(TBL2,1,COLUMN()-COLUMNS(TBL1)+2))

Fill B1 right until it generates #REF! errors.

Next find the field values for the first record.

A2: [array formula]
=IF(COUNTIF(INDEX(TBL1,1,0),A$1),
HLOOKUP(A$1,TBL1,SMALL(IF(COUNTIF(INDEX(TBL2,0,1),INDEX(TBL1,0,1))
*(ROW(TBL1)-CELL("Row",TBL1)+1)>1,COUNTIF(INDEX(TBL2,0,1),INDEX(TBL1,0,1))
*(ROW(TBL1)-CELL("Row",TBL1)+1)),ROW()-1)),
HLOOKUP(A$1,TBL2,SMALL(IF(COUNTIF(INDEX(TBL1,0,1),INDEX(TBL2,0,1))
*(ROW(TBL2)-CELL("Row",TBL2)+1)>1,COUNTIF(INDEX(TBL1,0,1),INDEX(TBL2,0,1))
*(ROW(TBL2)-CELL("Row",TBL2)+1)),ROW()-1)))

Fill A2 right as far as needed to correspond to field names in row 1. Let's say
there were 6 fields in totoal, so fill A2 into B2:F2. Then select all cells in
row2, so A2:F2, and fill down as far as needed until you get #REF! errors.

While it is possible to construct fairly general inner joins in Excel, it makes
a lot more sense to learn how to use SQL quesries to do this.
 
Back
Top