Copy from one Merge Cell into a Column

AArcher2

New Member
Joined
Oct 22, 2019
Messages
3
Hello,

I need a formula or macro to look for a value in one column and copies x times into a different column based on the amount of rows under that value. I have tried Vlook up and some codes but nothing has work. I have the following table

Grade
Name
Position
Date
Code
1111111, p
1
xxxx



2
sdfaf



1
asd



56
Asf



222222, l
1
asfa



OP
dsfa



2
adsfa



3
dsfa



4
adfe




<tbody>
</tbody>

For example I want to be able to look at the grade column and copy “1111111, p” that value 4 times or as many rows are under it until it hits the next header value which will be “222222, l”. The end table should look something like:
Grade
Name
Position
Date
Code
1111111, p
1
xxxx
1111111, p


2
sdfaf
1111111, p


1
asd
1111111, p


56
Asf
1111111, p


222222, l
1
asfa
222222, l


OP
dsfa
222222, l


2
adsfa
222222, l


3
dsfa
222222, l


4
adfe
222222, l



<tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I came up with a quick solution using a helper cell (which you could hide).


Excel 2010
ABCDEF
1GradeNamePositionDateCode
21111111, p 1111111, p
31xxxx1111111, p1111111, p
42sdfaf1111111, p1111111, p
51asd1111111, p1111111, p
656Asf1111111, p1111111, p
7222222, l222222, l
81asfa222222, l222222, l
9OPdsfa222222, l222222, l
102adsfa222222, l222222, l
113dsfa222222, l222222, l
124adfe222222, l222222, l
Sheet2
Cell Formulas
RangeFormula
C2=IF(A2=F2,"",F2)
F2=IF(ISNUMBER(FIND(",",A2)),INDIRECT("A"&ROW()),F1)
 
Upvote 0
Actually, F2 can be this: =IF(ISNUMBER(FIND(",",A2)),A2,F1)
 
Upvote 0
Does this, copied down, do what you want?

Excel Workbook
ABC
1GradeNamePosition
21111111, p 
31xxxx1111111, p
42sdfaf1111111, p
51asd1111111, p
656Asf1111111, p
7222222, l
81asfa222222, l
9OPdsfa222222, l
102adsfa222222, l
113dsfa222222, l
124adfe222222, l
Copy x
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top