Concatenate Code 1

[APElliott]

Hello,

I have been sent a disk, but I need to fit it into a workbook of my own.

The trouble with the disk I've been sent is that text in column B that should be all in one row as been spread over a number of rows.

There is however column [A:A] that has numbers within it. These number indicate what text is linked

What I want to do is at the first row where a number appears add a Concatenate formula in Column C for the number of row where the number is the same.

A B C
1 20 EXTERNAL WALLS =concatenate(b1)
2 40 CONCRETE WORK =concatenate(b2)
3 60 Precast concrete =concatenate(b3)
4 80 150 x 125 mm Bullnosed =concatenate b4," ",b5," ",b6," ",b7)
5 80 concrete kerb or purpose made
6 80 threshold built into masonry at
7 80 external door openings
8 140 BRICKWORK AND BLOCKWORK
9 160 Thuristone buff blend external facing
10 160 bricks (by The Brick Business) in
11 160 coloured mortar built fair face and
12 160 pointing with a bucket handle joint
13 180 Half brick skin to hollow wall
14 180 pointed one side
15 200 Half brick skin to hollow wall in
16 200 piers pointed one side

Thanks,

Andrew

 

[SkipVought]

Hey drew,

You can do this on your sheet with formulas.

Make a copy of your sheet & save

If your list needs to be maintained in a certain order, number the rows (and adjust the formulas below accordingly)

1. Insert a row above your list

2. enter this formula in c2 and copy down

=IF(A2<>A1,B2,C1&&quot; &quot;&B2)

3. copy column c and paste special-values to get rid of the formulas

4. enter this formula in column d

=IF(A2<>A3,1,0)

5. copy column d and paste special-values

6. sort by column d and delete rows with 0 in column d

VOLA!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

I bow to you

Cheers mate thats just brill.

Thanks

Andrew