Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
A few of you are Excel experts I know, so here's a bit of a challenge if you like.
I need a formula that gives the following output:
R201A01
R201A02
R201A03
R201A04
R201A05
R201A06
R201B01
R201B02
R201B03
R201B04
R201B05
R201B06
R201C01
R201C02
[snip]
R201E05
R201E06
R202A01
R202A02
[snip]
R220E05
R220E06
So...
Last number counts from 01 to 06.
Then letter is increased from A to E.
Then first number counts from 201 to 220.
Can it be done?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Does it need to be an Excel formula or could you use a macro/VBA?
If the latter, then you can do it with a number of nested 'for loops' 
Edit - Otherwise it's a case of dismantling the previous cells string using the 'mid' function, incrementing individual segments and then using 'if statements' to reset. VBA is far easier 
[Edited on 17-06-2015 by Dom]
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
quote:
Originally posted by Dom
could you use a macro/VBA?
I don't know, could I? 
Way out of my comfort zone here.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Am I simplifying it too much by saying concatenate it?
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
Could I break it down in to different columns somehow?
Column A:
R201 (+1 every 30 rows)
Column B:
A (+1 every 5 rows, starting over at E)
Column C:
01 (+1 every 1 row, starting over at 6)
Column D:
"=A1&B1&C1"
All I'd need is the formula for the three columns. Or am I dreamin?
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
quote:
Originally posted by pow
Am I simplifying it too much by saying concatenate it?
Depends what that means.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Exactly what I meant balling
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Give me an email address I'll fire something over
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
Cheers!
Email is in my profile.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Sent.
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
I'd like to see the finished formula please
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
quote:
Originally posted by pow
Sent.
You're a legend!
But as Brett says, how?
I probably need to do a new string in a year, so unless you want me to just email you I'd love to have the formula.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Just using Excel 2013 I made the pattern by copy pasting, then =CONCATENATE("B",A1,B1,C1)
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
So you filled in the A, B and C columns manually?
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Right, so no formula really at all
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
=CONCATENATE("R2",TEXT(INT((ROW()-1)/30)+1,"00"),CHAR(64+MOD(QUOTIENT(ROW()-1,6),5)+1),TEXT(IF(MOD(ROW(),6)=0,"6",MOD(ROW(),6)),"00"))
|
Ellis
Member
Registered: 11th Sep 07
Location: Aberdeenshire
User status: Offline
|
quote:
Originally posted by Brett
=CONCATENATE("R2",TEXT(INT((ROW()-1)/30)+1,"00"),CHAR(64+MOD(QUOTIENT(ROW()-1,6),5)+1),TEXT(IF(MOD(ROW(),6)=0,"6",MOD(ROW(),6)),"00"))
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Was going to suggest MOD
Have a big long column that's a regular count then mod that to give you the periods.
I'd be using CHAR to give you the letters as well, add 64 on to get to A.
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
It does
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
="R2"&IF(INT(A1/156)+1<10,"0","")&INT(A1/156)+1&CHAR(65+INT(MOD(A1-1,30)/6))&IF(MOD(A1-1,6)+1<10,"0","")&MOD(A1-1,6)+1
That's wrong but you get the idea.
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Mine works as required and doesn't require cell refs?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
I just wanted a go
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Fair enough Boss 
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
In fact Ian, if you want to join in, I missed the bit about it starting again after 220 CBA, got work to do.
|
Balling
Premium Member
Registered: 7th Apr 04
Location: Denmark
User status: Offline
|
quote:
Originally posted by Brett
=CONCATENATE("R2",TEXT(INT((ROW()-1)/30)+1,"00"),CHAR(64+MOD(QUOTIENT(ROW()-1,6),5)+1),TEXT(IF(MOD(ROW(),6)=0,"6",MOD(ROW(),6)),"00"))
Sweet! 
Needed to switch all the commas to semicolons to get it working with Excel for Mac, but other than that it seems flawless.
|
