Results 1 to 4 of 4
Like Tree1Likes
  • 1 Post By deanst

Conditional transposing and missing values find - urgent!

This is a discussion on Conditional transposing and missing values find - urgent! within the Excel Questions forums, part of the Question Forums category; Well hello, Fellas! I'm in a dire need of some assistance. My brainz has frozen and I need this yesterday. ...

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    2

    Exclamation Conditional transposing and missing values find - urgent!

    Well hello, Fellas!

    I'm in a dire need of some assistance. My brainz has frozen and I need this yesterday. Here is the case:

    A NAME B NAME NUMBER
    a 1 number between 32 and 74 (incl.)
    a 2 56
    a 3 32
    a 7 55
    a 90 44
    a 2 74
    a 4 66
    a 90 70
    b 3 32
    b 7 33
    b 8 34
    b 14 35
    c 54 36
    c 32 37
    c 1 38
    c 3 39
    c 8 40
    c 9 55
    c 1 70

    There are some A NAME with one or more corresponding B NAME. B NAME may be found against more than one A NAME. Each A NAME/B NAME relation can result in only one unique NUMBER value. NUMBER can contain a number from 32 to 74, incl. Each row is unique.

    I need to transpose the NUMBER values once per single A NAME-every B NAME horizontally, e.g.

    a 1 33
    a 2 56
    a 3 32
    b 3 32
    b 7 33

    Should become:

    a 1 33 33 56 32 ... ... ... ...
    a 2 56
    a 3 32
    b 3 32 32 33 ... ... ... ... ...
    b 7 33

    And then (or before), I'll need to find the missing values in the range of 32-74.

    Any help is greatly appreciated! Oh, almost forgot, let's stick to formulas please, it's important to me. Thanks!

  2. #2
    Board Regular deanst's Avatar
    Join Date
    Nov 2011
    Location
    Sydney, Australia
    Posts
    71

    Default Re: Conditional transposing and missing values find - urgent!

    Hi - maybe this will help. Fill the formula in D2 down and to the right.

    Dean.

    Excel 2013
    ABCDEFGHIJKLMNOPQR
    1A NAMEB NAMENUMBER123456789101112131415
    2a1333356325544746670-------
    3a256---------------
    4a332---------------
    5a755---------------
    6a9044---------------
    7a274---------------
    8a466---------------
    9a9070---------------
    10b33232333435-----------
    11b733---------------
    12b834---------------
    13b1435---------------
    14c543636373839405570--------
    15c3237---------------
    16c138---------------
    17c339---------------
    18c840---------------
    19c955---------------
    20c170---------------

    Sheet1



    Worksheet Formulas
    CellFormula
    D2=IF(OR(COUNTIF($A$2:$A2, $A2)>1, ROW(OFFSET($A1, D$1, 0))>ROW($A$20)), "-", IFERROR(INDEX(OFFSET($C1, D$1, 0):$C$20, MATCH($A2, OFFSET($A1, D$1, 0):$A$20, 0)), "-"))

    zlobby likes this.

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    2

    Default Re: Conditional transposing and missing values find - urgent!

    Hi Dean! You're the man!

    I was thinking of something like this but my brain is still not up to the capacity to go that deep.

    Anyhow, what happens if I have more than 15 horizontal values in row 1, e.g. 64? What happens if there are duplicates in column C?

    Another strange thing, why this $A$20 everywhere?

    And finally, how do you put such a nice looking table in the post?

  4. #4
    Board Regular deanst's Avatar
    Join Date
    Nov 2011
    Location
    Sydney, Australia
    Posts
    71

    Default Re: Conditional transposing and missing values find - urgent!

    Hi,

    Just drag the columns out to whatever size you need past 15. Better to have too many than not enough.

    $A$20 and $C$20 represent the end of the range that the formula is searching. Just amend the row number (20) to whatever boundary you like. Again, better to have too much than too little so set it to 1000 or something like that.

    On posting the tables, I use an add-in from Excel forums | Excel Matters (look at the bottom of the post for the add-in). It converts a selected range to html for pasting into your posts.

    Dean.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com