bringing data together from two worksheet for comparison

[jb]

Hi.

I have two datasets (in two different worksheets) with same labels for the
columns, however, with different list of genes in each row. I am interested
in comparing the values when the gene is present in both datasets.

eg.)
dataset 1

qualityA quality B quality C
XP
2000
apple

dataset 2
quality A quality B quality C
fuji
XP
1998

what can i do to 1) find the genes that are present in both list
AND at the same time
2) to bring the relevant columns next to each other for comparison

i.e to make something that looks like

dataset 1 dataset 2
qualityA quality B quality A quality B
XP


thanks in advance

 

[andy62]

It sounds like a simple VLOOKUP function would work for you. If your first
data set is in "Sheet1" and you are interested in adding the data from Sheet2
to it, first set up those columns. Then under the first new column
(Dataset2, Quality A) use a formula like:

=VLOOKUP($A2,Sheet2!A,2,FALSE)

That formula will pull in whatever's in the second column in Sheet2 in the
row that matches the data in the reference cell, A2 (the gene on Sheet1).
Extend that formula to the other Qualities by modifying the column reference
to 3 (=VLOOKUP($A2,Sheet2!A,3,FALSE)), then 4 (for qualities B, then C),
and then copy it down to all the other gene rows.

Now you have the data side by side, but you still don't have a "marker" for
when the qualities match. You can do this two ways. If you want to be able
to filter when there is a match to show only those rows, you might want to
set up a "third dataset" in Sheet1 that simply indicates whether there is a
match. Use a setup and formula like this:

Match?
qualityA quality B
quality C
XP =IF(C3=C6,"Yes","No") =IF(C4=C7,"Yes","No")

The other way to indicate the matches is to use Conditional Formatting to
"light up" the matches with bold font or a shading in the cell. That's a
good visual, but you can't use it to count the matches or filter on those
rows where there is a match. But if that's what you want write back and I'll
explain it.

HTH