transpose selected data

bhmcpfs

Board Regular
Joined
Apr 1, 2002
Messages
106
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Ok I'v got it
daleyman is right
Eli
This message was edited by eliW on 2002-04-03 12:34
 
Upvote 0
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
 
Upvote 0
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] [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
 
Upvote 0
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],,[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
 
Upvote 0
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],,[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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top