Excel help?

[werldhed]

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

[tnf]

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.

[werldhed]

[Shmee]

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

[werldhed]

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

[seremtan]

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?

[Don Carlos]

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...

[werldhed]

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:

Code: Select all

=VLOOKUP(C31,J1:K50000,2)

it will return:

Code: Select all

Ccr5      1424727_at

What I want it to do is return something like this:

Code: Select all

Ccr5       1424727_at
           1422260_x_a
           1422269_a_at

Does that make more sense?

[R00k]

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

[mjrpes]

here, let me google that for you...

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

(see third post down)

[R00k]

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.

[R00k]

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))))

[Fender]

here, let me google that for you...

http://live.lmgtfy.com/

[R00k]

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

[R00k]

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.

[werldhed]

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

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.

[Doombrain]

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

[werldhed]

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

[R00k]

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

[werldhed]

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.

[Doombrain]

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.

[werldhed]

Fuck it. Just did it by hand.

Thanks anyway

[Doombrain]

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

[werldhed]

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]

[R00k]

How long did that take, out of curiosity?