transpose selected data

[bhmcpfs]

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.

[eliW]

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

[bhmcpfs]

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.

[bhmcpfs]

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.

[daleyman]

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.

[eliW]

Ok I'v got it
daleyman is right
Eli





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

[Chris Davison]

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 ]

[bhmcpfs]

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

[bhmcpfs]

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

[eliW]

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