Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / September 2007

Tip: Looking for answers? Try searching our database.

bringing data together from two worksheet for comparison

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jb - 20 Sep 2007 23:44 GMT
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 - 21 Sep 2007 14:28 GMT
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:D,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:D,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

> Hi.
>
[quoted text clipped - 27 lines]
>
> thanks in advance
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.