Search results

I got it in the end by adding a blank column to the left! Thanks

Hello, It didn't work - I need the numbers not the helper column contents. The problem is that in the first instance it's necessary to skip three columns, and afterwards two. I've been trying to work out how this formula works for hours now and still have no idea - any insights would be...

Hello again, Is there anything I can do to help the 'electric load' - I'm not really sure what that means? I just wanted to ask what the two formulas are doing, perhaps I could work out a solution... Faseeh's formula: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)*2)) Narayan's formula in a...

I have no idea what is happening but often when I am adding a link here in the forum I am unable to post. I have tried to add the link several times now with no luck with the file in skydive... Edit: trying to add by editing http://sdrv.ms/UzBf9x Edit 2: seems to have worked...

I'm sorry to trouble you again Faseeh, but due to the set up of my worksheet my table starts at B2 and not A1, and unfortunately the formula doesn't perform in the same way with this shift of cells. Any ideas on how to tweak the formula so one might have control in describing the columns by...

I did try - copied and pasted exactly as it is - and unfortunately I did get X Z B for some reason... ### sorry! the *2 dropped off somewhere before pasting!!! Thanks !!

Thanks - that's really cool, and really really useful to know However I don't need columns X Z B , so I need to be able to select the rows I am transposing. Any ideas?

Hello I'm trying to create a sort of dashboard, and I'm looking to transpose certain columns to a different set of rows while maintaining links to the original table. My question is masterfully answered in this post http://bit.ly/UzsUmr, however I cannot get it to work for the opposite ie...

Thanks Luke M! Thanks Colin Legg! A nice addition for the toolkit

I just realised I had tried this in my data file but it comes back with a 0 value. Any ideas why this might be? This is the formula - $G$3 is equivalent to 'some' =SUMPRODUCT($C$2:$C$4233=C3,$G$2:$G$4233=$G$3,$H$2:$H$4233) #### FORGET THIS ---- I KEEP USING SOMEPRODUCT INCORRECTLY!! ---...

That's it! Thank you!

Hello, Im looking for an automated way to put a value in one of my columns (amount in 'some') so that I can complete a division (amount/amount in 'some'). This may be more clear in the table below I think this can be achieved with a sumproduct formula to retrieve the 'amount' in 'some' but...

Thank you! Worked perfectly

Dear community I'm looking for a way to number an item in a way that corresponds to associated conditions. In the example below I would like to achieve that the item "a" (id 1 visit 3, rows 4 and 5) becomes a1 and a2, and the same (a1 and a2) corresponding to id 2, visit 4 (rows 12 and 13)...

Hi Luke, sorry for the late response - I had very sillily entered the second formula in column F. When I figured this - all your formulas clicked and I finally really understood what was going on. Thank you, the solution was perfect

Thanks a lot! The first formula is cool, and would work with the sums above. The second formula didn't work for me, I got back a message about being in a circular function or something along those lines... I was going for 1s and 0s to eventually be able to filter the entries. The G column was...

Thank you very much Luke M! I have one more question to add because your solution, although perfect, wont always fit my data. The problem is that Date of Visit is an artificial entry, and is not always entered correctly. What I need to do is to identify when there are multiple entries in a...

Hi Luke M! Thank you for the prompt reply I've copied and pasted some lines here - I hope that formatting wont be an issue and you could copy and paste the same lines into Excel... If that doesn't work, let me know what you think I could do the show you the data Many thanks, Subject No...

Dear community, I have a set of data where for a given visit I usually have a single entry for a given drug. On occasions, there are multiple entries in rows, one after the other: I need to sum the doses. In addition, each entry may correspond to a number of days treated. I have used to...

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

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

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

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

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

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