Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Hi,
I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Do you want to find only those genes that appear in all 3 lists? Not just 2
lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Not all the lists. For example, I want to compare list 1 with lists 2 and 3.
I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Sounds complex! Can you post your file here?
http://www.freefilehosting.net/ People on this DG can see what you are referring to and this should make things a lot easier for everyone involved. Someone will, most likely, post the solution back on the same file hosting site. Regards, Ryan-- -- RyGuy "Mark" wrote: Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Mark
I was looking at the thread you have in the Programming newsgroup. It looks like your code, after making the suggested corrections, is pretty much what you want. I would rewrite the code to eliminate all but one of the "For" loop statements and use the "Find" statement to do all the work, but what you have will work. Come back if you're interested in a rewrite of what you have. HTH Otto "Mark" wrote in message ... Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Hi,
Thanks for the advice. This is my file if you want a look. (http://www.freefilehosting.net/download/3ffj5) I've included my macro code. It start to work but then crashed. Any help with a rewrite would be much appreciated. Mark "Otto Moehrbach" wrote: Mark I was looking at the thread you have in the Programming newsgroup. It looks like your code, after making the suggested corrections, is pretty much what you want. I would rewrite the code to eliminate all but one of the "For" loop statements and use the "Find" statement to do all the work, but what you have will work. Come back if you're interested in a rewrite of what you have. HTH Otto "Mark" wrote in message ... Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
This does a nice job of comparing two sheets:
Sub FindDupes() 'assuming both sheets are in same book and book is open Dim sht1 As Worksheet Dim sht2 As Worksheet Dim cell1 As Range Dim cell2 As Range Dim str As String str = InputBox("Type name of first sheet") Set sht1 = Worksheets(str) str = InputBox("Type name of second sheet") Set sht2 = Worksheets(str) sht1.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht1 = ActiveCell.Row sht2.Activate sht2.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht2 = ActiveCell.Row sht1.Activate For rowSht1 = 1 To LastRowSht1 If sht1.Cells(rowSht1, 1) = "" Then Exit Sub For rowSht2 = 1 To LastRowSht2 If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3 sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3 End If Next Next sht1.Cells(1, 1).Select End Sub Regards, Ryan--- PS, same answer in your other post too...not sure which you will look at... -- RyGuy "Mark" wrote: Hi, Thanks for the advice. This is my file if you want a look. (http://www.freefilehosting.net/download/3ffj5) I've included my macro code. It start to work but then crashed. Any help with a rewrite would be much appreciated. Mark "Otto Moehrbach" wrote: Mark I was looking at the thread you have in the Programming newsgroup. It looks like your code, after making the suggested corrections, is pretty much what you want. I would rewrite the code to eliminate all but one of the "For" loop statements and use the "Find" statement to do all the work, but what you have will work. Come back if you're interested in a rewrite of what you have. HTH Otto "Mark" wrote in message ... Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Hi, Thanks for the help. However, I'm getting a syntax error message at the
line: If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then Not sure why it doing that? Any ideas? Thanks. "ryguy7272" wrote: This does a nice job of comparing two sheets: Sub FindDupes() 'assuming both sheets are in same book and book is open Dim sht1 As Worksheet Dim sht2 As Worksheet Dim cell1 As Range Dim cell2 As Range Dim str As String str = InputBox("Type name of first sheet") Set sht1 = Worksheets(str) str = InputBox("Type name of second sheet") Set sht2 = Worksheets(str) sht1.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht1 = ActiveCell.Row sht2.Activate sht2.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht2 = ActiveCell.Row sht1.Activate For rowSht1 = 1 To LastRowSht1 If sht1.Cells(rowSht1, 1) = "" Then Exit Sub For rowSht2 = 1 To LastRowSht2 If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3 sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3 End If Next Next sht1.Cells(1, 1).Select End Sub Regards, Ryan--- PS, same answer in your other post too...not sure which you will look at... -- RyGuy "Mark" wrote: Hi, Thanks for the advice. This is my file if you want a look. (http://www.freefilehosting.net/download/3ffj5) I've included my macro code. It start to work but then crashed. Any help with a rewrite would be much appreciated. Mark "Otto Moehrbach" wrote: Mark I was looking at the thread you have in the Programming newsgroup. It looks like your code, after making the suggested corrections, is pretty much what you want. I would rewrite the code to eliminate all but one of the "For" loop statements and use the "Find" statement to do all the work, but what you have will work. Come back if you're interested in a rewrite of what you have. HTH Otto "Mark" wrote in message ... Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing 3 Lists
Mark
Here is a rewrite of your macro as I described before. This macro is based on there being 3 sheets named One, Two, and Three. The objective is to find all values that appear in all 3 sheets. Each sheet has a list running from A1 down. The end product is a list in Column C of the One sheet, starting with C2, of all the items that appear in all 3 sheets. HTH Otto Sub FindDups() Dim rOne As Range Dim rTwo As Range Dim rThree As Range Dim i As Range Dim Dest As Range Set Dest = Sheets("One").Range("C2") Set rOne = Sheets("One").Range("A1", Sheets("One").Range("A" & Rows.Count).End(xlUp)) Set rTwo = Sheets("Two").Range("A1", Sheets("Two").Range("A" & Rows.Count).End(xlUp)) Set rThree = Sheets("Three").Range("A1", Sheets("Three").Range("A" & Rows.Count).End(xlUp)) For Each i In rOne If Not rTwo.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then If Not rThree.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then Dest.Value = i.Value Set Dest = Dest.Offset(1) End If End If Next i End Sub "Mark" wrote in message ... Hi, Thanks for the help. However, I'm getting a syntax error message at the line: If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then Not sure why it doing that? Any ideas? Thanks. "ryguy7272" wrote: This does a nice job of comparing two sheets: Sub FindDupes() 'assuming both sheets are in same book and book is open Dim sht1 As Worksheet Dim sht2 As Worksheet Dim cell1 As Range Dim cell2 As Range Dim str As String str = InputBox("Type name of first sheet") Set sht1 = Worksheets(str) str = InputBox("Type name of second sheet") Set sht2 = Worksheets(str) sht1.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht1 = ActiveCell.Row sht2.Activate sht2.Range("A65536").End(xlDown).Activate Selection.End(xlUp).Activate LastRowSht2 = ActiveCell.Row sht1.Activate For rowSht1 = 1 To LastRowSht1 If sht1.Cells(rowSht1, 1) = "" Then Exit Sub For rowSht2 = 1 To LastRowSht2 If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value Then sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3 sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3 End If Next Next sht1.Cells(1, 1).Select End Sub Regards, Ryan--- PS, same answer in your other post too...not sure which you will look at... -- RyGuy "Mark" wrote: Hi, Thanks for the advice. This is my file if you want a look. (http://www.freefilehosting.net/download/3ffj5) I've included my macro code. It start to work but then crashed. Any help with a rewrite would be much appreciated. Mark "Otto Moehrbach" wrote: Mark I was looking at the thread you have in the Programming newsgroup. It looks like your code, after making the suggested corrections, is pretty much what you want. I would rewrite the code to eliminate all but one of the "For" loop statements and use the "Find" statement to do all the work, but what you have will work. Come back if you're interested in a rewrite of what you have. HTH Otto "Mark" wrote in message ... Not all the lists. For example, I want to compare list 1 with lists 2 and 3. I want to see which of the genes in list 1 appear in lists 2 and 3, etc. I was looking at colour coding the genes that appear, but it could be easier to put into a seperate sheet? "Otto Moehrbach" wrote: Do you want to find only those genes that appear in all 3 lists? Not just 2 lists? What/where do you want to place the list of genes that appear in all 3 lists? In 2 lists only? HTH Otto "Mark" wrote in message ... Hi, I'm looking for some advice as to how to cross reference 3 lists of genes that I am researching. I have these lists in a single workbook, with each list in its own worksheet. The lists start in Cell A1 and vary in size from 337 genes in the smaller list and upto 1489 genes in the largest. Basically, each list refers to the genes of interest in a particular region. I'm therefore looking for a way to cross reference these lists to show which genes appear in all three regions. Hope someone can help! Thanks Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross referencing | Excel Worksheet Functions | |||
Cross-Referencing Across Worksheets | Excel Worksheet Functions | |||
Cross Referencing | Excel Discussion (Misc queries) | |||
Cross referencing | Excel Worksheet Functions | |||
cross referencing | Excel Worksheet Functions |