Combining Excel Tables using Excel or Access 2003

Question

I am trying to create a merged dataset using Excel or Access and am not having much luck. I have two Excel tables, both contain ID's that I can link them with. However, TABLE1 has only one row for each ID, TABLE2 can contain multiple rows for each ID. I want to keep all entries in TABLE1 and add in the values from TABLE2, separating multiples by a comma. Example:

 **TABLE1**
    CNid    start   stop
    0001    1        50
    0002    60      100
    0003    1        20

 **TABLE2**
    CNid    gene
    0001    abc
    0001    ijk
    0001    qrs
    0003    abc

 **TABLE3(Created)**
    CNid    start   stop    gene
    0001    1        50      abc,ijk,qrs
    0002    60      100 
    0003    1        20      abc

I am familiar with merge query in Access, but cannot figure out how to make the resulting table only have one row for each CNid.

If a crosstab does not suit, you can use VBA -- http://stackoverflow.com/questions/92698/combine-rows-in-access-2007/93863#93863 — Fionnuala, Nov 13 '12 at 14:46

score 0 · Answer 1 · answered Aug 21 '13 at 00:01

Although I use Excel 2007 I think this will work in Excel 2003 but I'll separate out the steps just in case:

Create a PivotTable from Table2 with CNid for Row Labels, gene for Column Labels and Count of gene for Σ Values.
With layout as example, in L8 put:

=IF(F8=1,F$7,"")

and copy across to suit, then copy down L8 to wherever.

SO13362680 example

In I8 put:

=L8&","&M8&","&N8&","&O8&","&P8

Extend as necessary and copy down to suit.

Copy ColumnI and Paste Special Values into ColumnJ.
Select ColumnJ and replace ,, with nothing.
In K8 put:

=IF(RIGHT(J8,1)=",",LEFT(J8)-1,J8)

and copy down to suit.

Add gene label to Table1, say this is in D12.
Name ColumnsE:K geneArray with Workbook scope.
In D13 put:

=IF(ISERROR(VLOOKUP(A13,geneArray,7,0)),"",VLOOKUP(A13,geneArray,7,0))

and copy down to suit.

Copy ColumnD and Paste Special Values over the top.
Delete what is no longer required.

Combining Excel Tables using Excel or Access 2003

1 Answers1