Can anyone offer a solution ?

[pc300d]

Hello everyone,

Can anybody offer a solution to my problem:

I have a list of 4 digit numbers in column A (I need to retain leading
zeros for numbers < 1000)
I have 19 cells in column B containg alpha-numeric data.
I want Column C to contain a copy of the alpha-numeric data preceded by
the 4 digit number in column A.
I'm after a Macro, I think ! or some functions to make life easier as
Column A has 440 entries !

It doesn't go down on paper as easy as it looks so I've attached an
example:

Many thanks in advance for any help one this one.

The .jpg is unreadable - I'll try something else - - -


+-------------------------------------------------------------------+
|Filename: Excel Help.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4627 |
+-------------------------------------------------------------------+

 

[pc300d]

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 |
+-------------------------------------------------------------------+

 

[Cutter]

Try this:

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

and copy dow

 

[pc300d]

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

 

[Sandy Mann]

Shamelessly stealing an idea from Bob Philips:

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[pc300d]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[pc300d]

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 ne
to all this, so I don't know which "bit" needs tweaking.

Once the macro is run the resulting column (column C) is "parsed" wit
the 19 cells in column B which is perfect, but the prefix is alway
cell A1.

ie. as in my previous posts, column C has 0001 as the 1st 4 digits i
every cell (all 9000 of 'em) so the macro isn't stepping down colum
A.

Sorry to be pain but if this works it will save me an enormous amoun
of time in the future.

Many thank

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Anders Silven]

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]

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