Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: transpose selected data

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have an imported .txt file with ~10,000 rows of data. In column B, there are group headings (e.g. red, blue, black) Under each group heading is a variable number of rows containing data. I would like to get the group headings at the end of each row (in column Z). Can I do this? I've tried several variations of an IF worksheet function, with little success. I can get the first row beneath the heading correct, but since my reference varies, I'm not knowledgable enough to get further. Any assistance would be helpful. Sorry for the long post.

  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 08:42, bhmcpfs wrote:
    I have an imported .txt file with ~10,000 rows of data. In column B, there are group headings (e.g. red, blue, black) Under each group heading is a variable number of rows containing data. I would like to get the group headings at the end of each row (in column Z). Can I do this? I've tried several variations of an IF worksheet function, with little success. I can get the first row beneath the heading correct, but since my reference varies, I'm not knowledgable enough to get further. Any assistance would be helpful. Sorry for the long post.
    Can you be more specific and give the exact starcture of your data?
    Eli

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Row 1 are headers I've inserted, B2 contains 'Red', B3:B10 contains text (part descriptions), B11 contains 'Blue', B12:B40 contains text (as in B3:B10), B41 contains 'Black', B42:B48 contains text, etc. I'd like to have 'Red' in Z3:Z10, 'Blue' in Z12:Z40, and 'Black' in Z42:Z48. Hope this helps better explain my situation. Let me know if you need more... Thanks in advance for your time.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If it helps, I have ~80 unique group headings I'd like to move. I started to work on a nested IF statement, but gave up quickly. Of significance, though is that the headings differ from the rest of the data in column B in that the first seven characters of the headings are '888_999', where the data is all alphabetic.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    in that case is not too difficult...

    Assumption I am making is that list starts at row 1...

    Firstly, in cell Z1 put:

    =B1

    then, in cell Z2 put:

    =IF(LEFT(B2,7)="888_999",B2,Z1)

    then, use the fill the equation down the list (you can use the fill button on the bottom right hand corner of the selected cell Z2 for this, so long as the values in column Y are unbroken).

    As with all large sets of data, I recommend you copy the column to values when you are done.

    hope this works.

  6. #6
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok I'v got it
    daleyman is right
    Eli





    [ This Message was edited by: eliW on 2002-04-03 12:34 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HI,

    in cell Z2, try this :

    =IF(MID(B2,1,7)="888_999",B2,Z1)

    this brings in the headings....

    if you want them blank, then try this :

    =IF(MID(B2,1,7)="888_999","",IF(MID(B1,1,7)="888_999",B1,Z1))

    edit ........ and copy down to row 10,000 +

    _________________
    Hope this helps,
    Chris


    [ This Message was edited by: Chris Davison on 2002-04-03 12:30 ]

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This only works for the first heading. My problem is that the heading changes every x rows. I want the formula to work down the data, until it encounters another heading. I'm given column A and B. I want to get C to look like this:
    [A] [B] [C]
    200 250 RED
    004 PART 1 200 250 RED
    200 350 BLUE
    469 PART 2 200 350 BLUE
    162 PART 3 200 350 BLUE
    105 PART 4 200 350 BLUE
    300 071 *BLACK
    520 PART 22 300 071 *BLACK
    281 PART 43 300 071 *BLACK
    350 PART 999 300 071 *BLACK
    081 PART 111 300 071 *BLACK

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry guys, that came out looking like cr*p. I've used commas to separate columns this time. Hope it works better. A1 is blank, B1 is '200 250 RED', A3 is blank, etc.
    [A],[B],[C]
    ,200 250 RED,
    004, PART 1, 200 250 RED
    ,200 350 BLUE
    469, PART 2, 200 350 BLUE
    162, PART 3, 200 350 BLUE
    105, PART 4, 200 350 BLUE
    ,300 071 *BLACK,
    520, PART 22, 300 071 *BLACK
    281, PART 43, 300 071 *BLACK
    350, PART 99, 300 071 *BLACK
    081, PART 11, 300 071 *BLACK

  10. #10
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 12:59, bhmcpfs wrote:
    Sorry guys, that came out looking like cr*p. I've used commas to separate columns this time. Hope it works better. A1 is blank, B1 is '200 250 RED', A3 is blank, etc.
    [A],[B],[C]
    ,200 250 RED,
    004, PART 1, 200 250 RED
    ,200 350 BLUE
    469, PART 2, 200 350 BLUE
    162, PART 3, 200 350 BLUE
    105, PART 4, 200 350 BLUE
    ,300 071 *BLACK,
    520, PART 22, 300 071 *BLACK
    281, PART 43, 300 071 *BLACK
    350, PART 99, 300 071 *BLACK
    081, PART 11, 300 071 *BLACK
    1) Copy B1 to Z1
    2) In Z2 put the formula:
    =IF(AND(CODE(B2)>47,CODE(B2)<58),B2,Z1)
    3) copy this formula all the way to your last row
    Eli

Some videos you may like

User Tag List

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
  •