• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data organisation challenge: multi condition lookup

J

jsto7380

New Member
Hi all,


I have a table with data that was collected in the following way:

Columns: Individual ID (400), visit number (up to 30 per ID), name of an item (in my case a drug, up to 10 different items) and a value (in my case dose). >Sheet1 in file attached


The best way for me to work with this data is to convert it in a separate table to make a separate sheet per drug, and to have the individual ID in column one and the visit number as a header for the following columns. >Sheets 2, 3, 4 in file attached


I'm sure that it must be possible to retrieve the values from the original table. This post seems to do it for a single given value: http://chandoo.org/wp/2010/11/02/multi-condition-lookup/ . I can't seem to create a formula based on the ones shown with my own data - I don't really understand the naming of the categories.


What I'd ultimately like to be able to do is to retrieve a value and be able to drag down across the IDs for a given ID, visit and drug to retrieve the dose.


Here's an eg. file: http://sdrv.ms/S77dcC


Any ideas?


Best,

Excel novice
 
jsto7380

Please have a look at the upload, work done in Excel 2010


https://dl.dropbox.com/u/75495784/Pivot%20table%2012-11-07%20Excel%20trial.xlsx
 
Hi, jsto7380!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Well, my dear friend b(ut)ob(ut)hc (bobhc for most people) has just come out with an excellent solution. He left only the welcome for me. :)


Regards!


@b(ut)ob(ut)hc

Hi, old dog new tricked!

Good job. And have a nice and healthy day!

Regards!
 
Thank you very much bobhc - it is indeed an interesting solution, and I can see that I can make use of it for other challenges I face.

I do need to work with the data after reorganising it, so would still be hoping that it is possible to achieve the separate tables.


Thank you for the welcome SirJB. I did a big web search prior to posting and the closet thing I found to the solution I am after is the post above from Chandoo. But I was stoked to come across this amazing site: what an awesome community!


Best regards
 
Hi, jsto7380!


Go to sheet 'Drug X' and enter this formula in cell B2:

=SUMAPRODUCTO((Sheet1!$C$2:$C$20="X")*(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$2:$B$20=COLUMNA()-1)*(Sheet1!$D$2:$D$20)) -----> in english: =SUMPRODUCT((Sheet1!$C$2:$C$20="X")*(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$2:$B$20=COLUMNA()-1)*(Sheet1!$D$2:$D$20))


Then copy across and down as needed. Change "X" by "Y" or "Z" for the other two drug sheets.


Just advise if any issue.


Regards!
 
Thank you!!! That did it!


I did have to make a little change:


Removed: COLUMNA()-1 and put: 1 for visit 1

=SUMPRODUCT((Sheet1!$C$2:$C$20="X")*(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$2:$B$20=1)*(Sheet1!$D$2:$D$20))


Just one final question to make this even more awesome and automated: in the same position where I put 1, I'd like to be able to select the visit header cell, naming it 1 instead of V1 (sheet 2, cell B1).


Is there a way to fix that value in the formula so that when I drag it down it won't descend with the formula?


I would like to avoid manually entering the visit number and making an error ... this just might be what you tried to do with COLUMNA()-1 ?? I couldn't work out what your aim was there


A *bow* to the ninja in gratitude
 
Hi, jsto7380!


My mistake, a typo error in the translation: in the English formula, where it says "COLUMNA()-1" it should say "COLUMN()-1". With that is solved your issue of visit number, without having to be involved with headers in row 1.


If you want the header version, you might replace "COLUMNA()-1" or your "1" by "VAL(RIGHT(B$1,LEN(B$1)-1))" for column B.


I suggest the first method. If you find any trouble, just advise.


Regards!
 
Hi again,

I had to leave the work I was doing on this for a while, but have recently come back to it. I was not able to apply the tips above (which worked perfectly in the example table I gave: http://sdrv.ms/S77dcC) to my data.


I thought it may have to do with the data themselves e.g. complicated patient codes and complicated names of the visits, but I did not achieve success by simplifying these or formatting.


Here is the data table I am working on: http://sdrv.ms/OiRby2. To speed things up I filtered out and deleted all other drugs and am working with drug 'CCC'.


Although "COLUMNA()-1" worked, I don't really understand why and if I would need to change it for subsequent columns??


With thanks in advance,
 
Hi jsto7380,


Please see this file:


http://dl.dropbox.com/u/60644346/jsto7380_solution.xls


PS: It is always better for a sample file to be of minimum size to facilitate downloading.


Regards,

Faseeh
 
Thank you, Faseeh!


This worked but for patient 11, for example, the doses are not correct - I´m not sure why that would be.


In the original data file there are other drugs besides ´CCC´. I noticed in your formula the drug (column C) was not included, and I wondered if there is a way to select by drug.

Also, the original file is maybe 10 or 20 times bigger: will this cause calculation or other problems?


I am curious why the previous solution did´t work, if it was an error on my part with the syntax or something else?


My curiosity led me to the dashboard you created: great work!!


Best regards,
 
Hi, jsto7380!


In column B, rows 276 and 1271 have a #N/A value, which I changed to 10 for testing purposes. Changing that and applying the original formula adjusted to new range, it works fine for me. Would you please check it?

=SUMAPRODUCTO((Sheet1!$C$2:$C$2010="CCC")*(Sheet1!$A$2:$A$2010=$A2)*(Sheet1!$B$2:$B$2010=COLUMNA()-1)*(Sheet1!$D$2:$D$2010)) -----> in english: =SUMPRODUCT((Sheet1!$C$2:$C$2010="CCC")*(Sheet1!$A$2:$A$2010=$A2)*(Sheet1!$B$2:$B$2010=COLUMN()-1)*(Sheet1!$D$2:$D$2010))


Here is the link to the related files:

Original:

https://dl.dropbox.com/u/60558749/Data%20organisation%20challenge_%20multi%20condition%20lookup%20-%2012-11-07%20Excel%20trial%20%28for%20jsto7380%20at%20chandoo.org%29.xlsx

New with CCC:

https://dl.dropbox.com/u/60558749/Data%20organisation%20challenge_%20multi%20condition%20lookup%20-%2012-08-12%20Excel%20Trial%202%20%28for%20jsto7380%20at%20chandoo.org%29.xlsx


Just advise if any issue.


Regards!
 
Thank you SirJB7!


The #N/A values were in deed the problem


Many many thanks!


Faseeh's solution was also interesting and useful to learn the iferror and index/match functions (I am very much a beginner). I was stumped that for patient 11 the values retrieved were not the correct ones. Is there a reason why this happened?? It makes me uneasy to trust a function 100%...


Best regards,
 
Hi jsto7380,


Thanks for your remakrs! I have figured out why it was creating problem:


Lets look your data set, the first entry in your data set was:

[pre]
Code: 
Patient	        Visit	Drug	Dose	Visit	        Patient
1	        11	CCC	150	A2.5	        SEMBC11X

If you came down to the row 125 you will see another similar entry that was this:


[code]Patient	        Visit	Drug	Dose	Visit	        Patient
11	        1	CCC	420	J15	        LMMEC08X[/pre]
The Match() fucntion that i used in the formula hath this syntex:

MATCH(Sheet3!$A1&Sheet3!B$1,Sheet1!$A$2:$A$2010&Sheet1!$B$2:$B$2010,0)


When excel evaluates this formula, it combines the value of A2 & B2 (i.e 1 & 11) that gives the result 111. Similarly when it evaluates the values at Row 125, it again joins the values of A125 & B125 that this time are 11 & 1. Hence in both case the result is 111!! That was the error from my side.


When match() starts looking up for 111 it stuck up at the first row that was giving wrong answer.


Now try this formula that will add Values of A2 & B2 & A125 & B125 but with a "." in between that, this will give 1.11 for the first case and 11.1 for the second case and the formula works fine!!! Here is the one:


=IFERROR(INDEX(Sheet1!$D$2:$D$2010,MATCH(Sheet3!$A2&"."&Sheet3!B$1,Sheet1!$A$2:$A$2010&"."&Sheet1!$B$2:$B$2010,0),0),"..")[/code]


Replace old one with this new one!!


Regarding incorporating drug values, I have done this here in this file:


http://dl.dropbox.com/u/60644346/jsto7380_solution_modified.xlsx


Hope that helps!! Keep posting..


A note of caution regarding Array Formulas is that they tend to be slower when you are working with huge data sets, if yours is the one of the type i suggest you to stick to SirJB7's solution, that will be faster then this.


Regards,

Faseeh
 
Hi, jsto7380!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top