Mr_Odwin Posted July 2, 2011 Posted July 2, 2011 Weirdly I could see all the posted solutions in that thread from my phone when I looked at it. Now it's all hidden. If you look at the source you can still see what people have suggested.
Shorty Posted July 5, 2011 Author Posted July 5, 2011 Everything I was struggling with has now been solved, but the survivors lived only to face a new nightmare: Any idea how I can have an automatic increment which restarts related to another column, such as this: id | order ------------------------ dog | 1 dog | 2 dog | 3 dog | 4 dog | 5 dog | 6 dog | 7 cat | 1 cat | 2 cat | 3 cat | 4 mouse | 1 mouse | 2 mouse | 3 mouse | 4 mouse | 5 mouse | 6 monkey | 1 monkey | 2 monkey | 3 giraffe | 1 hamster | 1 hamster | 2 hippo | hippo | etc...
Mr_Odwin Posted July 5, 2011 Posted July 5, 2011 For columns A and B. Set 1 as the first entry of the second column then this badboy: =IF(A2=A1,B1+1,1) as the second and draaaaaaag.
Rummy Posted July 11, 2011 Posted July 11, 2011 Gonna hijack this thread! Got a slightly strange one, but it's because I'm lazy and hoping for some sort of quick fix. Imagine Shorty's animal list above, but instead of having an increment, I'd want to just put a number for the first instance of each(it's a number I know). So like, Dog 8 Dog Dog Dog Cat 27 Cat Horse 22 Horse Horse etc Without using a Vlookup, is there an easy way to autofill it down so that after I type the 8 next to dog, all the cells below it also become 8 until it hits the next character like? The only option I can think atm is to make the whole column equal to the cell above, THEN go through and put the codes in where neccessary, but I was worried I might do something silly at miss stuff in places.
Cube Posted July 11, 2011 Posted July 11, 2011 You can just copy the number, select down to the next cell (shift + ctrl + down should do that for you) then paste. Carry on for each number.
Rummy Posted July 12, 2011 Posted July 12, 2011 (edited) You don't want to use vlookup? There's complications in that not all the data is properly matching, potential of selecting an earlier match, trying to avoid any unneccessary gaps in the data and if I make everything too automated I'm afraid of that(though then there's still good ol' human error). For now I've decided to use Vlookups for some bits, then just verify it myself. I've basically got a list of 1500 results, a list of 500 names, and no good/easy criteria to match them on though I'm getting by. It was no biggie, though I've ended up doing vlookups off vlookups lol. (i need the number im putting on there for later then importing it into a database) Edited July 12, 2011 by Rummy
Rummy Posted July 20, 2011 Posted July 20, 2011 New challange! I have a list of (alot) of data, mainly things that occured and the date upon which such a thing occured. Slightly complicated, that there are things in this list belonging to a certain person. Actually, I don't know why I'm being vague. I have ~450 people, with ~1500 blood tests. I need to see for person A whether there was a test in Jan(y/n), in Feb(y/n)....in Dec(y/n). So I'd like an output something like this; Jan Feb Mar..................Dec Person 1 Y N N ....................Y Person 2 N N N ....................N Is there an easy way to figure out(by database or spreasheet) this table? A person could have 5 tests in one month, or none. I need to know whether there was none, or not, essentially. I've no idea how to do it really, though. My dates are in dd/mm/yyyy format. It's ok if not possible/easy, it's just something that would simplify stuff a lot down the line.
mike-zim Posted July 20, 2011 Posted July 20, 2011 Well i would approach it in a complicated way to set-up but once setup easy to maintain. I would put the data in a table in excel. I would then make the Date format MMM. i would then use a Pivot table and drag and drop the information i needed. I am not good at explaining how to do things i normally just take the data and do it myself.
Rummy Posted July 21, 2011 Posted July 21, 2011 Mmm, some of my searching turned up PivotTables and it looks like they might be the way forward, unfortunately I know nothing about them at the moment. The data's actually already in a database, so if it's a function I could do there instead then it'd be easy(results in one table tied to person identifiers(person 1, person 2...etc), and there's also a table with all the information/demogrpahics for a person, though that's a bit irrelevant I think).
mike-zim Posted July 21, 2011 Posted July 21, 2011 Well if it is in a database already you could extract the information via microsoft query (if the database is compatible). I do it all the time at work and it saves a lot of time. Again it would be difficult to explain but once you know how to do it, it is really easy.
Rummy Posted July 21, 2011 Posted July 21, 2011 (edited) Essentially, I need the data in the above sort of format. Even if it wasn't yes no, just a count of occurences would be ok cos I could see where 0s are and make that a No and anything else a Yes. The data is currently in a format like this(either in table or spreadsheet); ID Person DATE Result 598 07 14/09/2010 0.670000017 600 686 07 31/01/2011 0.529999971 600 70 26 18/01/2011 0.360000014 2440 39 22/02/2011 0.409999996 594 55 20/12/2010 0.660000026 800 4036 55 10/05/2011 0.660000026 25 19 28/09/2010 0.219999999 26 19 03/03/2011 0.75 3348 42 01/06/2011 0.400000006 1805 64 27/10/2010 0.779999971 3039 64 02/03/2011 0.769999981 4441 11 17/12/2010 0.039999999 486 46 08/09/2010 0.419999987 The ID is irrelevant, that's access's primary keying/autonumbering. The Person ID/number and date is relevant. I need to make a table that has the Person IDs as rows, and then months as coloumns, and either counting up the occurences and putting in the relevant column to show how many entries they are for them in that month. If that makes sense. The result is also irrelevant for the present need. Those 600s/800s are also irrelevant. ADDIT: Been playing in Excel with PivotTables, think I've sort of got what I need to do down. It's just a practise run anyway for something a little down the line. They're pretty impressive beasties though! Edited July 21, 2011 by Rummy
mike-zim Posted July 21, 2011 Posted July 21, 2011 PivotTables are awesome. What i would do with that data is add another seperate tab with the data for a lookup table. Have the dates from 01/01/10 trough to what ever date you need in Column 1 in Column 2 i would have say ="Jan-10" for all Jan 10 dates so on and so forth. Once that is done use a vlookup, in the last column of your data table, on the date cell to return the month from your lookup table. Then use a pivot table on the data. Have the person id on the left with month across the top and populate the data section with result. Cahnge the result data from sum to count. Jobs a good un!
Rummy Posted July 22, 2011 Posted July 22, 2011 I think I got a little lost there, but I used MONTH() to pull the month out, as actually the only dates I'll eventually end up using will span a year(sept 10-aug 11) so there won't be confusion. Also realised I can do it in access with crosstabs, but definitely good stuff learning(just about, still don't fully understand) Pivot Tables.
mike-zim Posted July 22, 2011 Posted July 22, 2011 I was going to suggest that but wasn't sure if it was more than a years data. Pivot Tables are really useful. just a tip, if there is a particular bit of data you want to analyse in the table double click it and it will bring all the background info into a seperate tab that relates to that specific bit of data. e.g Person 75 had 6 tests in october double click the 6 and it will bring out the six results that relate to person 75 in october.
Ashley Posted July 28, 2011 Posted July 28, 2011 As this seems to have become the excel help topic... I have an interview for a job. One of the criteria is the following: High level knowledge and experience of excel and using formulas to manage data I know a bit and have used them obviously but any places I can check out that'll help me stand out?
Recommended Posts