+ Reply to Thread
Results 1 to 15 of 15

Transpose a column that contains blanks, but not copy the blank cells

  1. #1
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Transpose a column that contains blanks, but not copy the blank cells

    I have downloaded a template that I am adding to that produces a league table. When I transpose a list (column) of match results to a row elsewhere on the worksheet, the blank cells in that list ie.the matches yet to be played and therefore empty, return a zero in the row. I can hide these zeros or format the font colour and cell background to be the same so that they can't be see - but nonetheless they are still there, seen or otherwise. This has a detrimental effect on another worksheet that examines the results to calculate a league table because if there is ANYTHING at all in the cell, it counts as a match played (see League Table tab)

    As I am not the original author of the workbook, I am not exactly sure on how the whole thing works. if you look at the attached file, I have added only the 2 fixtures sheet and the scores sheet.

    Ultimately, I wish to simply enter the score for each match once only in the list on the scores tab, and this to be echoed to the results grid. I have tried TRIM, ISBLANK etc but to no avail. any help gratefully appreciated thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    well there seems to be a problem with that xls
    Attached Images Attached Images
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    Okay thanks. I'll try and upload it again

  4. #4
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    I hope this can be downloaded
    Attached Files Attached Files

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    nope it crashes my excel

  6. #6
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    Hmmm - sorry about that. I'll try once more but before that, I'll strip out the irrelevant bits. Meanwhile I'm wondering if any else has been able to download it - whether they can offer a possible solution or not. Admiin? I've been researching the problem I have, it seems it is impossible for a cell to be blank - even if there is just a formula or the result ("")of formula in it.

  7. #7
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: I need to transpose a column that contains blanks, but not copy the blank cells

    Try this please - unless it's causing you problems
    Attached Files Attached Files

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Transpose a column that contains blanks, but not copy the blank cells

    nope still the same

  9. #9
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: Transpose a column that contains blanks, but not copy the blank cells

    Okay - thanks anyway Martin . I can't think of a work around either

  10. #10
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: Transpose a column that contains blanks, but not copy the blank cells

    #martindwilson I think if I uploaded a couple of screen shots to here, I might be better able to illustrate the problem I have if you think that might help.? Give me half hour

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Transpose a column that contains blanks, but not copy the blank cells

    well i opened it with open office and re-saved as excel but i cant see how this is supposed to work at all
    there are loads of named ranges one that stands out is "teams" and is defined as =Results!$A$2:$A$25
    so how the formula
    =VLOOKUP(A3,teams,COLUMN(),FALSE) could ever work is beyond me

  12. #12
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: Transpose a column that contains blanks, but not copy the blank cells

    It does work if I enter each result individually into the Results! tab - the table produced is correct. But when I try to copy and paste or transpose or link cells from Scores! tab to Results! tab the affect is shown in the enclosed screenshots. I've also uploaded the file again from my pc rather than the Android tablet I was using earlier. Like I said in the original post - it is not my creation, it is a template I downloaded and I'm trying to add to it. Maybe there are hidden worksheets in there somewhere and protected. I understand if you want to give up though lol.Thanks very much for your time this evening.
    Attached Files Attached Files

  13. #13
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Transpose a column that contains blanks, but not copy the blank cells

    ok i took a chance and ignored the error warning and opened it and can see everything
    what particular formula is seeing all these 0 values
    i cant see whats wrong with the league table

  14. #14
    Registered User
    Join Date
    11-03-2012
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    70

    Re: Transpose a column that contains blanks, but not copy the blank cells

    I have entered the results separately into the results worksheet. That works OK. What I want to do is somehow copy the results en masse from the scores worksheet (from the list of individual matches). The problem is the matches not yet played, they result in a zero when copied - however so. There must be workings out elsewhere that I can't see - eg. how the teams are ranked. Thanks again

    Edit. It's the transpose function (using an array - { and }. )
    Last edited by mtouhig; 09-15-2013 at 04:16 PM.

  15. #15
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Transpose a column that contains blanks, but not copy the blank cells

    well you can replace the transpose with for results in scores b2 with
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    that will give "" instead of 0
    ranking is done on sheet "league table unsorted" which is hidden,right click any tab select unhide

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code to Copy Non Blanks/Cells with Values until Formula returns as blank
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-16-2013, 10:18 PM
  2. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  3. Scan a Column A (numbers and blanks) and copy its contents WITHOUT BLANKS into Column B
    By bighandsam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2012, 05:16 PM
  4. Replies: 4
    Last Post: 05-10-2012, 08:16 PM
  5. Copy cells to adjacent column, removing blanks
    By jbitluk in forum Excel General
    Replies: 8
    Last Post: 12-11-2009, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1