Compare ID and value of each sheet then compile all ID and add value into the final sheet

0
votes

I am asking help to compare the gene expression value in different time point experiments, with either Excel or VBA to compare then generate report sheet. Each sheet (time points) contains two columns: Gene ID and Value and the gene ID may not be the same in each sheet (time point: 1H, 4H and 8H.. etc.). In the report sheet (sheet4), I would like to have all Gene ID (column 1) and values of each time points (column 2, column 3, column 4). Thus the final sheet (sheet 4) will include all Gene ID from each of the sheets into the column 1, the expression value of sheet1(value1), sheet2(value2) and sheet3(value3) will be in the column2, column3 and column4 of the sheet4. I use “vlookup” of excel can add the expression value into the sheet4 after comparing/lookup the ID, but I do not know how to add the GeneID together for all sheets.

It will look like:

Sheet1 (1H):

(title) GeneID, Value1

Eco, 2;

Xmo, 4;

Sheet2 (4H):

(title) GeneID, Value2

Eco, 6;

Hmm, 8;

Sheet3 (24H):

(title) GeneID, Value3

Xmo, 10;

Ama,12;

The final sheet4 (summary):

(title) GeneID, Value1, Value2, Value3

Eco, 2, 6, (blank);

Xmo, 4, (blank), 6;

Hmm, (blank), 8, (blank);

Ana, (blank), (blank),12;

2
vbaexcel
LOGIC: [1] Loop though all sheets and get all the Gene IDs in Sheet 4. [2] Remove the duplicates from Col A in sheet 4 [3] Using .Find() now look for all the values for gene ID in all sheets and output the data in Sheet 4. [4] Here is how you use .FIND() siddharthrout.wordpress.com/2011/07/14/… Give it a try and if you get stuck, post what have you tried and where are you stuck and we will take it from there. - Siddharth Rout
For a non-vba approach you can try something similar to this: stackoverflow.com/questions/10610354/… - lori_m
Hi @SiddharthRout, the URL for this wet site is not accessible. (siddharthrout.wordpress.com/2011/07/14/…). Can you check? Thanks. - user1398016
I just clicked it. It is working just fine :) - Siddharth Rout

2 Answers

1
votes

I think the quickest way might be to use the Data|Consolidate command with settings shown:

enter image description here

enter image description here

0
votes

You can do a lot with ADO and Excel. Let us say your sheet names are 1Hour, 4Hour and 24Hour.

''http://support.microsoft.com/kb/257819
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''HDR=Yes, the names in the first row of the range
''are used as field (column) names.
''
''This is the ACE / Excel 2007/10 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "TRANSFORM Sum(ValX) As SumVal " _
       & "SELECT GeneID FROM ( " _
       & "SELECT GeneID, '01Hour' As TimeX, Value1 As ValX  " _
       & "FROM [1Hour$] " _
       & "UNION ALL " _
       & "SELECT GeneID, '04Hour' As TimeX, Value2 As ValX  " _
       & "FROM [4Hour$] " _
       & "UNION ALL " _
       & "SELECT GeneID, '24Hour' As TimeX, Value3 As ValX  " _
       & "FROM [24Hour$] ) t " _
       & "GROUP BY GeneID " _
       & "PIVOT TimeX"

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results
With Worksheets("Sheet4")
    For i = 0 To rs.Fields.Count - 1
        .Cells(1, i + 1) = rs.Fields(i).Name
    Next

    .Cells(2, 1).CopyFromRecordset rs
End With

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Jet/ACE SQL can be used.

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000