Any spreadsheet/excel wizards?

[Mr_Odwin]

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]

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]

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.

[Shorty]

Worked a charm, thanks once more

[Rummy]

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.

[Mr_Odwin]

You don't want to use vlookup?

[Cube]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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?