Excel Question

[kirk82]

I am using Ms Office 97. I have created a spreadsheet for my Dart League Stats. It does everything I need except.

I need to find a way for it to be able to tell the difference between male and female players. Once that is done to take the information from that players row and copy it to another location. Not just the cell, the row.

Anybody have any idea as to how I can do this.

P.s. I am a novice user. Self taught by trial, error and the odd manual.

[EzyStvy]

Add another column for gender. Type M or F. You could then "sort" all making the gender column the primary sort.

[kirk82]

Yes I have already done that. What I want is to have excel to search for M or F when it finds them to then take them and copy them to another location.

Example.

John, W M 43.3 120
Jane, S F 45.9 130
Find the above information then copy it to another location. I would like it done automatically.



John, W M 43.3 120







Jane, S F 45.9 130

[jong2k4]

What you're talking about sounds more like a database type thing. I think EzyStv's answer is the best if you're going to stay in an excel file- after you sort for gender it is a simple matter to copy and paste over to another location.

I have an idea for a REALLY half-assed way to do this in excel using if functions, but doing it manually would probably be faster and easier.

But if you're really keen on the idea of having it automatically generate two seperate reports based on gender, it would be best to do it in Access.

[kirk82]

Please explain how this could be done using "if" functions.

Thank-you

[jong2k4]

It is, as I said above, not a very good solution. You might want to look into using the dget function, or possibly something else. But if you want it...

Create a new worksheet that is the same size as your worksheet that contains the raw data. Each cell in this new worksheet will contain an if function. Somewhere outside this range will be two cells that contain "m" and "f". Then for a given row, each if statement will contain the same logical test. If your gender data is stored in column M, and your original sheet is named Sheet 1, then for row 3 all of your if statements will start out with =if(Sheet1!M3=Z1..... and Z1 would be the cell that contains either "m" or "f" to compare against. Then the next argument of the function will always be the cell in the first sheet that corresponds to the cell that function currently resides in. The last argument will be "".

This will generate a table of values that corresponds to the first table precisely except there are blank rows for those records which did not pass the test. From this point, you can select all of the cells, copy them, then with them still highlighted paste the values only, to eliminate the if statements and leave just the result, so then you can sort it as you like.

It's not a very pretty solution, but it will work. Once you write the if statements for the first row you can just extend that row down as far as you want and it will automatically adjust for row number, so you don't have to type the whole thing out by hand.

[jbert]

Try doing an advanced filter. You have the option of copying the cells to another location. You can set up a macro to automatically run the advanced filter for you. If you want to send me the spreadsheet, I can set it up for you. I am Microsoft Office User Specialist (MOUS) certified, and also have my expert MOUS certification. If the information is sensitive, just send me a dummy workbook with the exact column headings you are using.

[PMich]

It really is a simple sort followed by a copy and paste. Yes you could use and advanced filter and yes you could use a macro to run the advanced filter but unless you are talking about hundreds of entries (players) its kind of overkill. Besides, there are probably fewer keystrokes involved with a sort and copy and paste than there is in finding the macro unless you assign it a hotkey.