Posts by RichardS

  • cmp 2 cols and assign occurences count

    Re: cmp 2 cols and assign occurences count


    You eg looks like column C & D are doing the same thing. I've assumed you want to assign a count (1) in column C, then a cumulative count in column D. If so, try this


    In column C a simple test to see if A & B are the same


    Code
    =IF(B1=A1,1,0)


    In column D, a cumulative total


    Code
    =IF(C1=1,SUMIF($B$1:B1,B1,$C$1:C1),0)


    which is copied down as far as your data goes.


    Any help?


    Richard

  • member info retrivial from number???

    Re: member info retrivial from number???


    Sorry about delay in responding, I've been sleeping etc.


    In the workbook I attached, the formulae can be modified to look at a list in a different book. Once you have one row working OK, it's just a matter of copying it down as far as you need. The $ signs in the formulae make them absolute, so when you copy them down they stay the same. Those refs without $ are relative, and will change as you copy down.


    The easiest way to create the formulae is to start typing =IF( then highlight the cells as needed.


    I'm not sure that I can provide much more advice. As you note, you appear to have limited experience with Excel. Hang in there and tough this one out. In my opinion, VLOOKUP and HLOOKUP are probably two of the most useful features of Excel, so once you get it right, you'll be on your way.


    Richard

  • Comparing list

    Re: Comparing list


    =MATCH(A1,$B$1:$B$500,0)


    will return #N/A if there is no match. If there is a match, it returns the position in column B of the corresponding entry in column A.


    HTH


    Richard

  • Footy Tipping Minimum Score

    Hi all,


    I've been charged with developing a spreadsheet to run the local footy tipping comp. The rules include one which states if you don't get your tips in on time, you get the lowest score of the other pickers. I was trying to use MIN, but of course if the Tipster didn't pick, he would have a value of 0, so that is the MIN. Therefore, what I need is a formula which selects the lowest value from a range, but ignores zero values.


    Any ideas. This is obviously really important.

  • compare and copy data

    Re: compare and copy data


    I'm sure there is a more glamorous VBA solution, but you could use VLOOKUP if you are prepared to receive errors when there is no match. You could always paste values, then sort the results and remove the errors.


    In your destination sheet, column A


    =Sheet1!A1


    Column B


    =Sheet1!B1


    Column C


    =VLOOKUP($A1,Sheet1!$C$1:$E$7,2,0)


    Column D


    =VLOOKUP($A1,Sheet1!$C$1:$E$7,3,0)


    Copy the formulae down as far as the source data in Sheet1 goes.


    If what is in column A doesn't exist in column C, you will get #N/A errors in columns C & D. As I said, once you've got the results, you could paste values, sort and delete the error rows.


    Probably not what you're after, but the best I can come up with.


    Richard

  • Re-appear after calling other application

    Re: Re-appear after calling other application


    Hi Andy,


    Thanks for the reply. That's not the way it behaves for me. If I have Explorer open, or Outlook, or our finance package, when I close the browser window, something else has the focus. Just did it then, and this browser window is active, not my program. Not a big issue, just hoped to make it a little more professional for end users. It's not a commercial system or anything, so don't sweat it.


    Thanks again,


    Richard

  • compare and copy data

    Re: compare and copy data


    There seems to be a bit of a contradiction in what you are asking. In the first part, you indicate that if column A and C in the same row are the same, transfer the values from that row, column B, D and E to another sheet. In the example, the matching values are in different rows. Which is it?


    Either way, you should be able to use an if A=C then B type formula in your other sheet to bring the desired data across.


    Richard

  • Re-appear after calling other application

    Hi,


    I have a very simple application, which has a link to a help file. The trouble is, when the help file is closed (html file), Excel is no longer the active application. I'm not hot on VBA, so some help with the code would be appreciated.



    Thanks,


    Richard

  • Autofilter - adding a column range after filter

    Re: Autofilter - adding a column range after filter


    You shouldn't need to copy to another sheet. If you autofilter and make you're selection, select a cell below the column you want to sum, and click on the autosum button, you'll get a formula like
    =SUBTOTAL(9,C6:C50)
    which will only sum the visible cells.


    Is that any help?


    Richard

  • Copy Worksheet

    Re: Copy Worksheet


    Not a macro, but a quick way to do it is copy the master, then group those two and copy, then group four and copy etc. After 8 copies, you will be up to 256 sheets, and you'll only need to select 109 of them to copy.


    Richard

  • minutes * cost/time

    Re: minutes * cost/time


    Formula is column H needs to include *24. Excel stores times as a proportion of a day, so your 80 hours is 3.333 days, multiplied by 1.5 is 5.


    Richard