For each chance in column A, copy column B and fill down in column C

Angus_12

New Member
Joined
Feb 24, 2019
Messages
2
Hi All,

I'm looking for a for a macro or formula that recognises a change in Column A, then at each change, it copies the same row from column B, pastes the value to column C and fills the next months downs until the next change in column A. At the moment I have Column A and B however I want it to create column C for me...??

ABC
1112OCTOCT
1112OCTNOV
1112OCTDEC
1113FEBFEB
1113FEBMAR
1114AUGAUG
1115MARMAR
1115MARAPR
1115MARMAY
1115MARJUN
1116SEPSEP
1116SEPOCT
1116SEPNOV

<tbody>
</tbody>


thankyou in Advance.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forums. Try the below, but it assumes your data starts in Row 2.

C2:
Code:
=UPPER(IF(ROWS($B$2:B2)=1,B2,IF(B2<>B1,B2,TEXT(DATE(2019,MONTH(1&C1)+1,1),"mmm"))))
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Assuming your Column B months are Text:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1112</td><td style=";">OCT</td><td style=";">OCT</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1112</td><td style=";">OCT</td><td style=";">NOV</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1112</td><td style=";">OCT</td><td style=";">DEC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1113</td><td style=";">FEB</td><td style=";">FEB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1113</td><td style=";">FEB</td><td style=";">MAR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1114</td><td style=";">AUG</td><td style=";">AUG</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1115</td><td style=";">MAR</td><td style=";">MAR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1115</td><td style=";">MAR</td><td style=";">APR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1115</td><td style=";">MAR</td><td style=";">MAY</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1115</td><td style=";">MAR</td><td style=";">JUN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1116</td><td style=";">SEP</td><td style=";">SEP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1116</td><td style=";">SEP</td><td style=";">OCT</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1116</td><td style=";">SEP</td><td style=";">NOV</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet610</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=UPPER(<font color="Blue">IF(<font color="Red">COUNTIF(<font color="Green">A$1:A1,A1</font>)=1,B1,TEXT(<font color="Green">EOMONTH(<font color="Purple">(<font color="Teal">B1&1</font>),COUNTIF(<font color="Teal">A$1:A1,A1</font>)-1</font>),"mmm"</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

C1 formula copied down.
 

Angus_12

New Member
Joined
Feb 24, 2019
Messages
2
Thank you both for posting this, it has worked perfectly!

greatly appreciated.

Regards.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
  1. 365
Platform
  1. Windows
Cool. You're welcome.
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome, welcome to the forum, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,163
Messages
5,527,163
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top