I have microarray data arranged in two excel sheets. These two sheets have a common column in them. Can anyone tell me how can I combine that data in a single sheet. How can I do this? Using Excel or other tool?
If you prefer to do this in excel rather than reading into any type of data management tool, there is a method to link two excel tables by a common column. I believe it has something to do with pivot tables.
you may do this with R, Matlab, and some other softwares which are able to work with excel files or databases.
For a detailed response, please provide me the dimension and structure of your data; i.e., the number of variables and number of individuals and etc.
@ Heyder after doing in Matlab or R is there possible to import the result file if yes can you tell me please
Thank you...
In R, when you have two matrices or data-frames, you may use cbind or rbind to bind them into single matrix or database vertically or horizontally. Please use rbind to add rows and cbind to add columns; see ?cbind or ?rbind for more details
In Matlab, when you have two matrices A and B with the same number of columns you can write
C=[A;B]; to add rows of B after rows of A and save the results in C
and write
C=[A B]; to add columns of B after columns of A and save the results in C.
Finally, in Matlab use the following command to write the results onto an EXCEL file:
xlswrite(F,X,S,R)
where
F is the output filename and should be in the form 'filename.xls' with cottations
X is the matrix which you want to write it
S is the name or number of sheet which you want to write X into it and should be in the form 'sheetname' or a number like 3
R is the range of the region which you want to write X into it and should be in this format:
'A2:B5' or 'B2', etc.
Note: S and R is optional and when you do not specify them, X is written in the first cell of first sheet. (sheet1 , 'A1').
Please let me know if you need more help.
@ Heydar Thank you very much for telling me in detail. I will do and let you if I need more help. .. Thanks again
Within the office packet, you can import both excel sheets in access as two tables, and then create a query joining them by the common column
Oooh... Sorry - I've only heard of pivot tables but never used them. I would merge data in SAS... but you will probably find a youtube video on how to do this. It should be pretty simple.
Hi,
If data are in Excel already, no need to use other program. A macro would just do it fine, it's very flexible. Can you describe briefly your data structure (Name of columns, number of columns & rows, type of data of the common column, etc)?
Hello Rajashri N Bhairamadgi, I have done the excel to access and then a query, but it was so long ago I would struggle now to remember how to do it, but as per the comment above, yes, this can occur. Thanks Debbie
If I understood you correctly,
you can use consolidate function in Excel.
Go to the page where you want to insert your new table, Data tab-> consolidate-> in pop-up window-> chose the function you want to perform on your columns-> choose the tables you want to combine->place V on "use labels in (according to your common column)
- Badges
- Science topic