1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

help with excel formula PLEASE

Discussion in 'Business Applications' started by ozdogs, Jan 31, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I have a spreadsheet which has results in columns.

    Column A has the name of runners in the event, column B has where they finished (WON, 2nd, 3rd, etc). In column X I want to list the winner of the event for all runners except the winner.

    Is there a formula that will do this for me.

    The formula would need to be something like:

    If column B =WON, then leave column X blank, if column B=(anything other than WON) find next record above where column B=WON and paste column A of that record into column X of this record.

    I know that is not very well explained, but i have lots of records of races usually with 8 runners in each and against all that didnt win, I want to put the winners name in column X.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,952
    First Name:
    Wayne
    so you have something like this
    Code:
    Race1 Name1 Won
    Race1 Name2 2nd
    Race1 Name3 3rd
    Race1 Name4 4th
    Race1 Name5 5th
    Race2 Name1a Won
    Race2 Name2a 2nd
    Race2 Name3a 3rd
    Race2 Name4a 4th
    Race2 Name5a 5th
    and you want
    Code:
    Race1 Name1 Won  - Name1
    Race1 Name2 2nd   - Name1
    Race1 Name3 3rd   - Name1
    Race1 Name4 4th   - Name1
    Race1 Name5 5th   - Name1
    Race2 Name1a Won   - Name1a
    Race2 Name2a 2nd   - Name1a
    Race2 Name3a 3rd   - Name1a
    Race2 Name4a 4th   - Name1a
    Race2 Name5a 5th   - Name1a
    can you upload a sample spreadsheet - with the data and how you want the result to look
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    These two lines seem to contradict each other:
    The first, well, I don't understand, but it sounds from the latter that you just want the names of all the winners in column X. If that is so, do you want them listed against the race they were in, so you get something like
    Race1 - runner2 (or whoever the winner was)
    Race2 - runner4
    Race3 - runner3
    Race4 - runner8
    and so on?
     
  4. Stacers

    Stacers

    Joined:
    May 8, 2009
    Messages:
    51
    I've attached an Excel file (one in 2007 format, and one compatible with 2003) that I think does what you're looking for - in a roundabout way, but it works.

    Columns D and E are hidden - they form part of my solution. Column D is putting together the name of the event with the place that the runner was in. Column E is there for the sake of the lookup, it's listing the runner's name again (what's in column B).

    Now on to column F, where it will show the name of the winner of that particular race, unless it's in the row of the winner itself. I've named the range in columns D and E 'Lookup'*, and the formula is looking for the name of the winner of that particular race.

    *A note on this named range, because I'm not sure of your experience level. If your spreadsheet isn't going to change, you could just select your data in columns D and E and name the range as you normally would. In this case, I've gone into the name manager and named the range dynamically so that it expands as you add information. Here's the formula I used: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!C:C),2)

    I hope that helps!
     

    Attached Files:

  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    (Notwithstanding we don't know if an "Event" or "Race #" column even exists), how about a "megaformula"? (© John Walkenbach).

    =IF(B2=1,"WON","Runner "&INDEX(INDIRECT("A"&MATCH(C2,C:C,0)&":A"&COUNTIF(C:C,C2)+MATCH(C2,C:C,0)-1),MATCH(1,INDIRECT("B"&MATCH(C2,C:C,0)&":B"&COUNTIF(C:C,C2)+MATCH(C2,C:C,0)-1),0)))

    :D

    HTH :)

    OK :)

    (ETA: visited your site, saw a link about "Canada at WorldSkills 2009" -- maybe I'm mistaken but ... didn't you once have a different user name? ;) )
     

    Attached Files:

  6. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I have included a sample sheet. I want to put the winners name in column Y. If possible, I would like to pput the tunner up in column Y against the winner, but that might be too difficult.

    The race number column has races 1 - 10 (or possibly higher) for each meeting, but this repeats for each meeting so any reference to race # in this column would find many results.
     

    Attached Files:

  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Nothing done here except I converted it back to .xls for those who don't have 2007.
     

    Attached Files:

  8. Brent-PCGuy

    Brent-PCGuy

    Joined:
    Jan 25, 2010
    Messages:
    35
    The big problem with use of Excel as a database is that you get tables that are not in normal form. Then when you try to make "reports" on those tables you need extremely convoluted functions, if they can be done at all.

    1) How do you get the data in the tables? from an online source where you cut and paste? from a text printout that you type into Excel? This would allow us to suggest other ways of manipulating the data.
    2) How do you use the output? Do you email it? print it to paper? post it online? just reference it yourself?
    3) Why is it helpful to have the winner's name in a column next to each runner's name? What problem does this solve?
    4) Would you be able to accept an Access database solution? To me it would seem much more helpful to produce a report from this data after it has been put in a correct database form.
     
  9. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    Thanks for your reply.

    To answer your questions:
    I get the data from various online sources. these are in various formats, some as web pages, some downloadable txt or csv files, etc. I copy and paste into temp sheet in Excel, then manipulate the data getting rid of data I dont need, then sorting it into the column order to suit my Excel sheet before pasting into data sheet.

    I use the data to keep records of all results. This data is subjected to a range of formulas (only the values are showing in the sample I posted). I have another workbook that searches this data using various criteria to find data to use for forward ratings. This workbook applies another set of calculations to the data for upcoming races. It is all done electronically (although I do have an online version but that doesnt have anything to do with the Excel sheets). So the data and calculations are all contained on my own PC network. I would like to share them, but have had trouble using Excel in cloud computing or online collaboration as mostly macros are not supported by this.

    It is helpful to me to have the winners name as a memory jog for me. I certainly can live without it.....I have for many years, but it makes me remember whick of the thousands of races I analyse that particular data comes from. Easier to remember winners than every runner. So it doesnt solve a problem, just assists me.

    An access database would be the ideal thing for what I am doing. That's why I had the online version built but it is far mor cumbersome than the setup I have in Excel with all the macros and formulas built in. I have worked over the years to get the excel application very streamlined and it currently works well. I know that Access would be a better way of doing it, but I dont have the skills to convert it into an Access database. I dont know how to structure the relationships and I also dont know how to convert all my macros and formulas into queries and reports. That's the limitation, my knowledge. I'm not a computer person.........I'm a form analyst who started all this 30 years ago on 8 X 5 record cards and have developed it from there and learnt computers and Excel along the way.

    Once again, many thanks for you reply. The assistance I have been able to get from this forum in recent months has helped me greatly.
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I have watched someone else (Zack) write code that would automatically pull the data into Excel from selected websites - I am sure Access should be able to do the same. (If not, it could do it via Excel!) At any rate, if there are some sites you always get data from, this might be a possibility....
     
  11. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I have thought about looking for a way to automate the process of getting the data and sorting it into the fields (columns) that I need and in the right order to insert into Excel. I have been told that can be done.........maybe i will look at that sometime. Mostly I am just trying to improve the efficiency of what I am doing bit by bit as time and knowledge (some of that derived from this forum's members) allows.

    Everything works as is, just trying to make improvements. The best thing I could do is to find a way (inexpensive would be best) to be able to collaborate my excel workbooks online with other people. That would help to lighten the load as this would allow multiple people to work on the data. At present, because my sheets are full of macros to automate many of the processes........that appears to be a problem for a small individual user like myself.
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Excel workbooks are not really meant to be collaborative items - only one person can enter data at a time, unlike a database such as Access, which can accept data entry from multiple people. But you say that you are gathering this info from online - from a forum or news or??? If it is posted at a site like ESPN, then there is nothing for the others to do but read your workbook; if these are local events and you are wanting to gather data from around, say, your local high school conference, then maybe you should consider an online database like Zoho.
     
  13. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    The data I collect is results of races. This information is available online and anyone who wants to, and who knows where to find it can get it. My data is a complete collection of this information but with my own calculation and statistical analysis attached to convert the raw results into performance ratings. These calculations are based on formula I have developed by analysing data collected for many years and applying those adjustments. This is what others dont have access to.

    The problem for me is that the amount of data I collect and work on is the same as a small sporting publication would work on......maybe with a staff of several people doing the data collection and analysing. The reason I would like to be able to work collaboratively online would be to have others contribute to this process and be able then to also use the end results.

    Once again, a database would be the best thing but I dont have the expertise to change what I have to an Access database. Thats why I paid a developer to build me an online application. This application, using an SQL database works fine, but is far more cumbersome to go through all the processes than my Excel sheets because i have over time set up automated processes that the database developer was unable to replicate. Maybe I need another developer to work on it or start again but that can turn out to be a costly exercise.

    The thing with the online database, and I dont know whether this is common to all databases, is the formatting precision that is needed for all fields. In Excel, if a field is a number field (column), then if I want some data excluded I can just put an X in the column and Excel ignores it with calcs like average or small or large, etc. In my online database, a number field could only contain a number and this meant I had to do things differently, but still so it wouldnt affect the calcs. Same with having to add ID fields, having extra fields for calcs that I dont need in Excel, etc.

    You mentioned Zoho database online............I looked at those sort of options. They dont have the capacity to do what I need. Some dont have enough data storage available, others wont handle the calcs, etc. My current Excel sheet contains about 300,000 records, and that doesnt include the records that I have archived. I add about 800 records a day. Each of these records has 25 columns or fields. each record has 6 columns that auto-update values on another sheet, which inreturn become the historical data values that my other workbook uses for its calculations for upcoming races.

    Each of these sheets run many macros to automate the various processes.....from sorting of results into the right columns right through to a single button that processes a field for an upcoming race, applies multiple criteria, searches, sorts and produces the required information using advanced filters, etc (vba module is about 400 lines long just for this process).

    You see, Excel does it well, but has its limitations especially when it comes to running it online or collaboration, etc. But it really would take a fair amount of expertise, which I dont have, to convert the process to Access or to develop an online database that will be as simple to use as Excel now is with all the macros and other auto processes built in.

    All I need is for Microsoft to drop everything else and develop an online, collaborative version of Excel, with now row limitations, where all macros will work and where you can set it up by simply uploading existing workbooks and allocation permissions for other users to access the data with read-only or full access.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/899081

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice