Can anyone offer a solution ?

The JPEG is a bit naff, here's a Word doc that's better.

Regards

Try this:

=IF(A1<1000,CHOOSE(LEN(A1),"000","00","0")&A1&B1,A1&B1)

and copy down

Hi Cutter, thanks for the reply.

It "sort of" works but is not what I'm after.

Firstly Column A has numbers above 1000 (up to 4000) I just mentioned 1000 in the first post as I need to keep the leading zeros on all numbers less than a thousand.

Secondly, for each cell in column A I need 19 cells in column C.

In the example; cell A1 = 0001, I need 0001 placed in cells C1 to C19 but with the info in cells B1 to B19 placed after it.
So column C would look like
C1 0001HS01
C2 0001HS02
C3 0001MIC
C4 0001SP01
down to
C19 0001SP16
Then C20 would start with the second cell in column A (ie 0002)
C20 0002HS01
C21 0002HS02
etc. etc.

I've dabbled with the IF command but I cannot work out exactly what your suggestion is doing. I does work for numbers less than 1000 but it doesn't address the 19 entries in column B per 1 entry in column A.

'Hope that made some sort of sense !

Thanks and regards

Shamelessly stealing an idea from Bob Philips:

=RIGHT("0000"&A1,4)&B1

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"pc300d" <[email protected]> wrote in
message news:[email protected]...
>
> The JPEG is a bit naff, here's a Word doc that's better.
>
> Regards
>
>
> +-------------------------------------------------------------------+
> |Filename: Excel Help.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=4628 |
> +-------------------------------------------------------------------+
>
> --
> pc300d
> ------------------------------------------------------------------------
> pc300d's Profile:
> http://www.excelforum.com/member.php...o&userid=33474
> View this thread: http://www.excelforum.com/showthread...hreadid=532785
>

Hi Sandy,

That works better in that it works on numbers higher that 1000.

It still doesn't address the fact that I don't need A1 added to B1 then A2 added to B2.

What I need is:
A1 added to B1, A1 added to B2, A1 added to B3........A1 added to B19.

Then A2 added to B1, A2 added to B2, A2 added to B3.......A2 added to B19.

Then A3 added to B1, A3 added to B2, A3 added to B3.......A3 added to B19.

With your formula I could manipulate column A so that it had the first 19 cells equal to A1, the next 19 cells equal to what was in A2 and then copy cells B1 to B19 down to match column A. Finally copy your formula down coumn C to produce the figures I want but that would be so much work as there are 4000 numbers in column A!

I'm not too sure a single cell formula is what I need maybe I need some sort of macro but it looks to me like it would be way beyond my expertise to come up with one (at least I've found the smileys )

Thanks and regards

In that case I think that you are right in that you will need a Macro. Try
something like:

Sub Trial()
Dim x As Long
Dim y As Long
Dim z As Long
Dim Acol As Long
Dim Bcol As Long

Application.ScreenUpdating = False

Acol = Cells(1, 1).End(xlDown).Row
Bcol = Cells(1, 2).End(xlDown).Row

For x = 1 To Acol
For y = 1 To Bcol
Cells(x * Bcol - Bcol + y, 3).Value = _
Right("0000" & Cells(1, 1).Value, 4) _
& Cells(y, 2).Value
Next y
Next x

Application.ScreenUpdating = True

End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"pc300d" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Sandy,
>
> That works better in that it works on numbers higher that 1000.
>
> It still doesn't address the fact that I don't need A1 added to B1 then
> A2 added to B2.
>
> What I need is:
> A1 added to B1, A1 added to B2, A1 added to B3........A1 added
> to B19.
>
> Then A2 added to B1, A2 added to B2, A2 added to B3.......A2 added to
> B19.
>
> Then A3 added to B1, A3 added to B2, A3 added to B3.......A3 added to
> B19.
>
> With your formula I could manipulate column A so that it had the first
> 19 cells equal to A1, the next 19 cells equal to what was in A2 and
> then copy cells B1 to B19 down to match column A. Finally copy your
> formula down coumn C to produce the figures I want but that would be so
> much work as there are 4000 numbers in column A!
>
> I'm not too sure a single cell formula is what I need maybe
> I need some sort of macro but it looks to me like it would be way
> beyond my expertise to come up with one (at least I've found the
> smileys )
>
> Thanks and regards
>
>
> --
> pc300d
> ------------------------------------------------------------------------
> pc300d's Profile:
> http://www.excelforum.com/member.php...o&userid=33474
> View this thread: http://www.excelforum.com/showthread...hreadid=532785
>

try making the A1 cell absoute

=RIGHT("0000"&$A$1,4)&B1


--
slowhand
Posted from - http://www.officehelp.in

"pc300d" <[email protected]> wrote in
message news:[email protected]...

> Sorry to be pain

You're not being a pain, it me that's being a pain - I changed the x
variable to a 1 to run a test and forgot to change it back again before
posting.

change:
Cells(x * Bcol - Bcol + y, 3).Value = _
Right("0000" & Cells(1, 1).Value, 4) _
& Cells(y, 2).Value

to:
Cells(x * Bcol - Bcol + y, 3).Value = _
Right("0000" & Cells(x, 1).Value, 4) _
& Cells(y, 2).Value

and it should work as you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"pc300d" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you for your time Sandy, we're very nearly there !
>
> The macro is hard for me to understand, as I said before I'm fairly new
> to all this, so I don't know which "bit" needs tweaking.
>
> Once the macro is run the resulting column (column C) is "parsed" with
> the 19 cells in column B which is perfect, but the prefix is always
> cell A1.
>
> ie. as in my previous posts, column C has 0001 as the 1st 4 digits in
> every cell (all 9000 of 'em) so the macro isn't stepping down column
> A.
>
> Sorry to be pain but if this works it will save me an enormous amount
> of time in the future.
>
> Many thanks
>
>
> --
> pc300d
> ------------------------------------------------------------------------
> pc300d's Profile:
> http://www.excelforum.com/member.php...o&userid=33474
> View this thread: http://www.excelforum.com/showthread...hreadid=532785
>

Hi,

If I read you right, here is one way to do it with worksheet functions.

Enter in column C and fill down:

=RIGHT("0000"&INDEX(A:A,INT((ROW()-1)/19)+1),4)&INDEX(B:B,MOD(ROW()-1,19)+1)

(Formula borrowed from Bob and Sandy.)

HTH
Anders Silven

"pc300d" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Cutter, thanks for the reply.
>
> It "sort of" works but is not what I'm after.
>
> Firstly Column A has numbers above 1000 (up to 4000) I just mentioned
> 1000 in the first post as I need to keep the leading zeros on all
> numbers less than a thousand.
>
> Secondly, for each cell in column A I need 19 cells in column C.
>
> In the example; cell A1 = 0001, I need 0001 placed in cells C1 to C19
> but with the info in cells B1 to B19 placed after it.
> So column C would look like
> C1 *0001*HS01
> C2 *0001*HS02
> C3 *0001*MIC
> C4 *0001*SP01
> down to
> C19 *0001*SP16
> Then C20 would start with the second cell in column A (ie *0002*)
> C20 *0002*HS01
> C21 *0002*HS02
> etc. etc.
>
> I've dabbled with the IF command but I cannot work out exactly what
> your suggestion is doing. I does work for numbers less than 1000 but it
> doesn't address the 19 entries in column B per 1 entry in column A.
>
> 'Hope that made some sort of sense !
>
> Thanks and regards
>
>
> --
> pc300d
> ------------------------------------------------------------------------
> pc300d's Profile:
> http://www.excelforum.com/member.php...o&userid=33474
> View this thread: http://www.excelforum.com/showthread...hreadid=532785
>

Yep ! that works as well, thanks Anders

With my limited knowledge of Excel this will be slightly easier for me to carry around and explain to my colleagues it's use.

Many thanks again to all those who helped.

PC300D