|
Can anyone offer a solution ?
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 ------------------------------------------------------------------------ pc300d's Profile: http://www.excelforum.com/member.php...o&userid=33474 View this thread: http://www.excelforum.com/showthread...hreadid=532785 |
Can anyone offer a solution ?
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 |
Can anyone offer a solution ?
Try this: =IF(A1<1000,CHOOSE(LEN(A1),"000","00","0")&A1&B1,A 1&B1) and copy down -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=532785 |
Can anyone offer a solution ?
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 |
Can anyone offer a solution ?
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 :confused: 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 |
Can anyone offer a solution ?
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 with @tiscali.co.uk "pc300d" wrote in message ... 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 :confused: 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 |
Can anyone offer a solution ?
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. :confused: 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 |
Can anyone offer a solution ?
try making the A1 cell absoute =RIGHT("0000"&$A$1,4)&B1 -- slowhand Posted from - http://www.officehelp.in |
Can anyone offer a solution ?
"pc300d" wrote in
message ... 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 with @tiscali.co.uk "pc300d" wrote in message ... 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. :confused: 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 |
Can anyone offer a solution ?
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" wrote in message ... 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 |
Can anyone offer a solution ?
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 -- pc300d ------------------------------------------------------------------------ pc300d's Profile: http://www.excelforum.com/member.php...o&userid=33474 View this thread: http://www.excelforum.com/showthread...hreadid=532785 |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com