Evaluating 3 Excel Sheets

  • Thread starter Thread starter Ueli Werner
  • Start date Start date
U

Ueli Werner

Hi newsgroup

I have a excel file which has 3 sheets. This 3 sheets are feeded with data
of a database.

In the first Sheet i have data from a table 'questions'. This table has a
ID.
in the second sheet i have data from a table 'candidate'. This table has an
id too.

in the third sheet i have data from a table 'anwers'. this table has an id
and has also the id of questions and candidate.

Now I like to create another sheet in excel, which shows me the third sheet
combined with sheet1 and sheet2. The relationship is given by the ids.

Has anyone an idea how to do this? I think excel should have this
functionality.

Thanks for every help.

best regards

Ueli Werner
 
Do the id's show up in each worksheet a single time?

Are all the id's in each sheet or do some sheets include a partial list (if
they're not used)?

If all the id's show up in each worksheet and each id is unique, then you could
copy the 3rd worksheet to a new worksheet (or just add the other info to the
existing 3rd sheet????).

Then you could use =vlookup() or =index(match()) to retrieve values from the
other two sheets.

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

===========
If the id's show up a most once per worksheet, but there's no single worksheet
that has all the id's, you could create a new worksheet that combines the id's
into a single list--and removes the duplicates.

Then use =vlookup() or =index(match()) to retrieve the data from all 3 original
sheets.

One way to create that new list of unique id's:

Copy the unique key (and a single header row) to column A of this new
worksheet. Copy the data from the second worksheet under that data. Then the
third.

Then use data|filter|advanced filter to get a unique list.

See Debra Dalgleish's site for more details.
http://contextures.com/xladvfilter01.html#FilterUR

Now you can start adding your =vlookup() or =index(match()) formulas.
 
Back
Top