in excel 2013 you may use FLASH FILL from DATA menu
On Wednesday, May 28, 2014 7:59 PM, Vaibhav Joshi <v...@vabs.in> wrote: Hi PFA, HTH//Cheers!@ On Wed, May 28, 2014 at 3:59 PM, AndyB <anindya20042...@gmail.com> wrote: > >Hi guys, > >Thanks for being so helpful on this forum/group. > >I have a problem I need your input on. > >Basically it is 5 lines of data that needs to be concatenated first. Then the >concatenation needs to be translated as in the final output column. The final >output sequence is A0, A1,A2,A3,A4,A5,A6,A7,A8,A9,B0,...........B9,C0.......Z9. The interesting part is when it goes beyond Z9. Then it needs to be AA0,AA1, AA2, AA3 till goes till ZZ9. So the final output format is always either 1character [A-Z] paired with 1 number [0-9], or 2 characters [AA-ZZ] paired with 1 number [0-9]. > > > > > Is this possible? I am certain the concatenation part is..... > > > >DATA1 DATA2 DATA3 DATA4 DATA5 CONCATE FINAL OUTPUT >Apple0 Bat0 Cat0 Dog0 Eagle0 Apple0Bat0Cat0Dog0Eagle0 A0 >Apple1 Bat1 Cat1 Dog1 Eagle1 Apple1Bat1Cat1Dog1Eagle1 A1 >Apple2 Bat2 Cat2 Dog2 Eagle2 Apple2Bat2Cat2Dog2Eagle2 A2 >Apple3 Bat3 Cat3 Dog3 Eagle3 Apple3Bat3Cat3Dog3Eagle3 A3 >Apple4 Bat4 Cat4 Dog4 Eagle4 Apple4Bat4Cat4Dog4Eagle4 A4 >Apple5 Bat5 Cat5 Dog5 Eagle5 Apple5Bat5Cat5Dog5Eagle5 A5 >Apple6 Bat6 Cat6 Dog6 Eagle6 Apple6Bat6Cat6Dog6Eagle6 A6 >Apple7 Bat7 Cat7 Dog7 Eagle7 Apple7Bat7Cat7Dog7Eagle7 A7 >Apple8 Bat8 Cat8 Dog8 Eagle8 Apple8Bat8Cat8Dog8Eagle8 A8 >Apple9 Bat9 Cat9 Dog9 Eagle9 Apple9Bat9Cat9Dog9Eagle9 A9 >Apple10 Bat10 Cat10 Dog10 Eagle10 Apple10Bat10Cat10Dog10Eagle10 B0 >Apple11 Bat11 Cat11 Dog11 Eagle11 Apple11Bat11Cat11Dog11Eagle11 B1 >Apple12 Bat12 Cat12 Dog12 Eagle12 Apple12Bat12Cat12Dog12Eagle12 B2 >Apple13 Bat13 Cat13 Dog13 Eagle13 Apple13Bat13Cat13Dog13Eagle13 B3 >Apple14 Bat14 Cat14 Dog14 Eagle14 Apple14Bat14Cat14Dog14Eagle14 B4 >Apple15 Bat15 Cat15 Dog15 Eagle15 Apple15Bat15Cat15Dog15Eagle15 B5 >Apple16 Bat16 Cat16 Dog16 Eagle16 Apple16Bat16Cat16Dog16Eagle16 B6 >Apple17 Bat17 Cat17 Dog17 Eagle17 Apple17Bat17Cat17Dog17Eagle17 B7 >Apple18 Bat18 Cat18 Dog18 Eagle18 Apple18Bat18Cat18Dog18Eagle18 B8 >Apple19 Bat19 Cat19 Dog19 Eagle19 Apple19Bat19Cat19Dog19Eagle19 B9 >Apple20 Bat20 Cat20 Dog20 Eagle20 Apple20Bat20Cat20Dog20Eagle20 C0 >Apple21 Bat21 Cat21 Dog21 Eagle21 Apple21Bat21Cat21Dog21Eagle21 C1 > >Many thanks, >Andy -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
