Pull data from multiple worksheets

Ok, this is a tricky one:

I have a workbook with multiple worksheets. In each worksheet, the rows are labeled with gene names, and the columns with case #s. The data can be one of five different values: -2, -1, 0, 1, or 2.

	Case 1	Case 2	Case 3
Gene 1	-2	0	1
Gene 2	-1	-1	2
Gene 3	0	1	-1

I need to match the Case and Gene from across the worksheets, and place them into a new sheet labeled with the above rows and column headers. However, there are duplicate Case-Gene matches across the sheets (e.g. Case 1 and Gene 1 may appear in multiple worksheets).

I would like to pull the data with the following priorities: -2 > -1 > 0 > 1 > 2

For example:

If Case 1 and Gene 1 from a worksheet has the value 0, but Case 1 and Gene 1 from a different worksheet has the value -2, I would like for it to pull the value -2 (and so on, according to the priorities just listed).

Here's an example:

Worksheet A

	Case 1	Case 2	Case 3
Gene 1	-2	0	1
Gene 2	-1	-1	2
Gene 3	0	1	-1

Worksheet B

	Case 1	Case 2	Case 3
Gene 1	0	1	2
Gene 2	2	0	0
Gene 3	-1	0	-1

Final worksheet

	Case 1	Case 2	Case 3
Gene 1	-2	-1	-1
Gene 2	0	-1	0
Gene 3	1	0	-1

Based on the data you provided for the three sheets, I don't get the Final worksheet results. You are showing answers that are not in the other two.

If the sheets are all identical in layout - you could use =MIN(Sheet2:Sheet3!B2)

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

How different are they?

Are Column and Row headings in a consistent place?

It might be easier if there was a copy of the workbook

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Reply, then Click on Go Advanced and scroll down to Manage Attachments.

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

1) Sheet name A, B and Final
2) Data should begin from A2 and heading from B1 in row1

Please Login or Register  to view this content.

If this doesn't work, need to see your workbook.

How do I attach an excel file to this thread?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you for your help. I actually need to adapt it for many sheets with large amounts of data.

Here is a sample workbook. I need to match the cases in the columns at the top with the Gene IDs in the rows on the left, and pull them into the sheet labeled Final. I have included a sheet labeled final_before and final_after, to emulate what I would like to achieve. As you can see, there are some duplicate cases across the sheets and if it runs into a duplicate Case-Gene, then use the priority: -2 > -1 > 0 > 1 > 2. also, the gene lists aren't necessarily in the same order across the sheets.

Many thanks!

Change to

Please Login or Register  to view this content.

Thanks for this, but I keep getting a Microsoft Visual Basic out of memory error. Any suggestions? (I am running on 16.0 GB RAM, i7, 3.40GHz)

Error with the workbook you uploaded?