Posts by Bryan Hessey

    Re: Identifying Non-reciprocal Pairs


    Quote from stevefil

    I have a list of people, identified by number, who are giving each other gifts. Some of them want to give to other people who give to them even if they are not on their original list, so I have to identify what is NOT there.


    Hi,


    If I have read your question correctly, then try something like


    =IF(SUMPRODUCT(--(C$2:C$50=A2),--(A$2:A$50=C2))>0,"",IF(C2="","",IF(ISERROR(MATCH(C2,A$2:A$50,0)),"",IF(OFFSET(B$1,MATCH(C2,A$2:A$50,0),0)<>"x","",C2&" to "&A2))))


    ---

    Re: Importing Data


    Quote from bdub91284

    "http://www.epa.gov/EPA-AIR/1996/October/Day-22/pr-23769.txt.html"


    Go to bottom to find time vs. speed table. How can I import that into Excel? (Column of time and separate column with speed.)


    Help,
    Brandon

    Hi,


    one method is to copy and paste into Excel, then
    Select column A and Data, Text to Columns, delimited by space.


    in C1 put


    =IF(ROW()<11,LEFT(A1,1),IF(ROW()<101,LEFT(A1,2),LEFT(A1,3)))


    and formula fill that to the end of the data, then Copy that column and Paste Special = Values back over column A


    delete column C and the few odd rows from the page changes.


    hth
    ---
    added,


    if you want column A to be numeric then


    =IF(ROW()<11,VALUE(LEFT(A1,1)),IF(ROW()<101,VALUE(LEFT(A1,2)),VALUE(LEFT(A1,3))))


    is a better formula.
    ---

    Re: Sum Group In Report


    Steve,


    If this is not yet resolved, the Sum( ) portion will ignore blanks, but the +Instal and +Delivery will give a #Value error if they are blank.


    hth
    ---

    Re: Expand diagram


    On the Control Toolbox toolbar, with Design Mode selected, rightmouse and Properties on the scrollbar.
    Select the Back Colour option and from the dropdown select the Palette option.
    The chart is one I used to discover what a barchart could do.
    Glad it helped.

    Re: Expand diagram


    Select D2:Q7 and rightmouse Format Cells, Font, and select Automatic colour. It is currently white on white.


    Also noted, the formula at N4 is
    =SUMPRODUCT(OFFSET($C37,$M$6+$K6*10,0):OFFSET($C37,$N$6+$K6*10,0))
    and should of course be
    =SUMPRODUCT(OFFSET($C1,$M$6+$K6*10,0):OFFSET($C1,$N$6+$K6*10,0))


    ie, $C1 not $C37 two times used.
    Please amend the formula for correctness.

    Re: Expand diagram


    It's not a Line diagram, but the idea of a similar setup with an ever-expanding capability might give you a clue as to what is possible.


    Range D2:Q7 can be set to view the displayed data and gives a clue as to happenings.


    For this type of operation you would simply add your new data to the end of the data-column and increase the counter (up to =Count(A:A )


    Hope this helps your thinking, sorry about it being a .zip but the size limit inhibits the .xls
    A free unzip is available from http://www.winzip.com if required.


    Re: text from word


    Hi Bill,


    Your problem is to split the middle two items, the first item appears detectable by a space, the last item is 10 characters long.
    The other two I counted the spaces, if only two spaces the item could be split on the space (ignore the trailing space). If three spaces then it needs more lines of data to see if 'half-way' is the correct place to split.


    If you have lines that don't conform please post a few and some adjustments may be possible.


    After the split, you can Copy & Paste Special = Values for the blue columns, (back over themselves) to freeze the results, and then delete any non-blue columns as required.


    Good Luck


    Quote from billgras

    Hi Bryan
    Thanks for your reply and I'm working on it.


    regards bill

    Re: Trying to find and delete rows


    From your .jpg file, I would insert two columns before D to use as helper columns.


    In D2 (new blank column) put
    =IF(C2="",A2,C2)


    and formula copy that down, then sort the data over column D


    In E2 (new blank column) put
    =IF(D2=D1,"Dup","")


    and formula copy that down.


    Formula Copy that down, then select column E, Copy and Paste Special = Values over itsself.


    Sort over column E and bulk-delete all 'Dup' entries.
    --------------------
    Another thought is that in view of the data shown, that you retain the data that you are deleting.
    After the sort over column D, in (say column Z) Z2 put
    =IF(D2=D3,F2&":"&G2&":"&H2,"")


    and formula-drag that down to the end of your data. Copy & Paste Special, Values column Z to preserve the deletions for later reference.

    Re: Deleting specific rows of data


    Gizzy,


    There are nicer VB ways to do this, but I would use my general purpose method of using column H to number the rows as they are, (put 1 in H1 and CTRL-Formula drag down to cover all data), then in cell I1 put
    =IF(OR(C1="",C1="("),"Del","")
    and formula drag that down.


    Select the two columns H & I and Copy, Paste Special Values back over themselves, then sort over column J and bulk delete all 'Del' items.
    Re-sort over column H and then delete columns H and I


    Sample (semi-complete) attached

    Re: text from word


    The easy way would be to have your data in MS Word in a Table (hide gridlines, No Borders) where you could then copy/paste into Excel.


    Alternately, you could try the formula in the attached, the blue columns are your data, but the format of some items means you will need to check further lines for being a good fit, hence the 'helper' columns have been left in the sheet.


    Good luck with your data

    Re: Excel to Word very slow using DDE


    Hi,


    Using Excel 2003 and Word 2003 (with SP1) your macro seems to perform with no detectable delay for a 5 column 18 row print area with simple arithmetic formulas (having created a folder C:\Docs and a document John_Smith.doc etc)


    If word is not open a request opens Word and includes the new data.


    Dunno if this helps you, but there appears nothing greatly amiss with your idea.