Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 20th 07, 11:44 PM posted to microsoft.public.excel.worksheet.functions
JB JB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 115
Default bringing data together from two worksheet for comparison

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

  #2   Report Post  
Old September 21st 07, 02:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 158
Default bringing data together from two worksheet for comparison

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


"jb" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bringing conditional data from another Worksheet Nikki Excel Worksheet Functions 2 March 31st 07 06:44 AM
bringing data in from another worksheet stacy05 Excel Discussion (Misc queries) 1 February 23rd 07 07:39 PM
bringing data in from one worksheet to another stacy05 Excel Worksheet Functions 0 February 23rd 07 01:51 PM
Bringing together data Emma Hope Excel Worksheet Functions 4 May 25th 06 02:58 PM
Bringing Last line of worksheet to a Summary page Debbie Excel Worksheet Functions 8 January 26th 06 11:36 PM


All times are GMT +1. The time now is 08:09 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017