Quake3World

Hey folks... I have an excel question. This may be a simple solution or even an obvious impossibility, but I've never had this problem, so I don't know the answer.

Here's the spreadsheet:
[lvlshot]http://img35.imageshack.us/img35/9497/array1.tif[/lvlshot]

-I have a set of 50000 genes and corresponding data for each entry (Column J & Column K, respectively).
-From this list of genes, I have a selection of 230 genes I specifically want to retrieve data results from (Column C).
-Normally, I could just use VLOOKUP to pull all those genes out of Column J and return the corresponding Column K results. Unfortunately, many of the genes in Column C have multiple data entries in Column J. For example, the arrow points to "CCR5" (C31) which has 3 results:

[lvlshot]http://img160.imageshack.us/img160/7784/array2.tif[/lvlshot]

So the question:
Can I pull out all data entries from Column K that correspond to a SINGLE value in Column C?

Does that make sense?
TIA

You see, excel uses these things called "Cells" and in them you can put "formulas" or even numbers and letters.

For example: A1+B3 will add up the values in those two cells.

Hope this helps.

I think what you want is a Pivot Table, but your K values would have to each inhabit their own column. I've seen this kind of thing done before but long ago. Hopefully I've pointed you in the right direction at least

I'm not familiar with using a pivot table, but i'll look into it. Maybe it will lead to a solution. Thanks

pivot tables are the generic solution to about 50% of all excel problems. for the other 50% i hand the problem off to a temp

also, i'm not sure if i quite got your explanation, but is something to do with trying to do something relational in a non-relational database-y type setup?

Tried the =LEFT command?
If what I think you are trying to do is filter down the results in K by using the =LEFT command and then VLookuping the results. You should need =Left(K1, 7) in your case

Should work nice and easy...

Sorry, I should have been clearer in my explanation. And it would've helped if my pic didn't have all those other columns.

Unfortunately I'm not at work any more, so I can't change it.

I'll try to explain it again:
J = 50000 Gene names.
K = 50000 values corresponding with genes in J
C = 230 gene names (taken from J) that I want to know the K values for.

J and K are the raw data from an experiment. C is the list of genes from that experiment that I want to isolate and analyze by themselves. Normally, I could use VLOOKUP to find each C entry and return its corresponding K value.

The problem is that some of the C entries have multiple K values, and using VLOOKUP will only return the FIRST value. So for the highlighted gene, "CCR5", if I say:
it will return: What I want it to do is return something like this: Does that make more sense?

Maybe if my wife feels like a challenge tonight I can help you out.

here, let me google that for you...

http://www.ozgrid.com/forum/showthread.php?t=28928

(see third post down)

She took a look at it, and thinks she may have found a way to do it (without a pivot table). But she isn't 100% sure yet, and it looks ridiculously complicated to me.

If I had the spreadsheet myself, I could do this in 5 minutes just by dumping it in a single database table and querying it. Trying it in Excel seems like an exercise in frustration.

mjrpes wrote:here, let me google that for you...

http://www.ozgrid.com/forum/showthread.php?t=28928

(see third post down)

lol, that is way more complex than anything i want to do in excel.

=IF(ROW($A2)-ROW($A$1)>COUNTIF($A$3:$A$261,$I$3),"",INDEX($D$1:$D$261,SMALL(IF($A$3:$A$261=$I$3,ROW($D$3:$D$261)),ROW($A2)-ROW($A$1))))

mjrpes wrote:here, let me google that for you...

http://live.lmgtfy.com/

mjrpes wrote:here, let me google that for you...

http://www.ozgrid.com/forum/showthread.php?t=28928

(see third post down)

Also:

http://office.microsoft.com/en-us/excel ... 81033.aspx

Fender wrote:

mjrpes wrote:here, let me google that for you...

http://live.lmgtfy.com/

"windows 7 instalar"

lol

This is like a tremendously nerdy version of people-watching.

mjrpes wrote:here, let me google that for you...

http://www.ozgrid.com/forum/showthread.php?t=28928

(see third post down)

Already went searching for the solution, but none worked. I hadn't come across this one, yet. Will give it a try

R00k wrote:

mjrpes wrote:

Also:

http://office.microsoft.com/en-us/excel ... 81033.aspx

Yeah, I already found that one. It seems to require individually entering the formula for each entry. i.e. if the first entry returns five values, I have to copy/paste the next formula 5 cells below the first and go back and select the correct reference cell. Then I have to do the same thing for the entire list.
Eeehhh... it may be what I end up doing anyway.

wait, can't you just make a list then use custom filter to include *230*?

Is there a way to make a filter for the 230 without individually typing them all in?

Just give me the damn spreadsheet and I can give the data to you in a few minutes.

I'm still editing the list. I'm not to the point yet where I need to filter out the data.

I was just asking ahead of time in the hopes I could have a solution ready when I need it.

Give me a half hour or so, and I'll send it your way.

werldhed wrote:Is there a way to make a filter for the 230 without individually typing them all in?

select all cells > data tab > list > create list > OK.

look at top of cell and click drop down box > custom > change 'equals' to 'contains' > *value of data to filter* > OK.

Fuck it. Just did it by hand.

Thanks anyway

the above does what you needed and it's very simple

I tried it out, but I still had to individually enter each gene I wanted to filter.

What I ended up doing is using a custom filter, and plugging in each gene 2 at a time (could only filter based on 2 parameters). Then I just copy/pasted the results.

EDIT: This might illustrate my problem better:

[lvlshot]http://img187.imageshack.us/img187/9813/array3.jpg[/lvlshot]

How long did that take, out of curiosity?