Inserting a Column Break in Excel/Microsoft 365

[jamiemarie]

Good Morning, This problem probably has a super simple solution that I just keep looking over. I've tried searching in Excel's search bar and also Google and have had no luck. I found the term Column Break, but can not find anything other than a page break in my version of Excel.

I have a list of part numbers that I need to print. I am copying them from a worksheet that has a lot more information on it, so the numbers are in one column spanning several print pages. Is there an easy way to copy the part numbers to the new sheet where it will stop at the page break and go to the next column? I have attached a picture of what I have right now, and a second picture of what I would like to have since I'm not sure if I'm explaining very well. I would just like an easy way to get the results shown in picture two without having to cut and paste over and over.

Thank you so much for your time, and if I wasn't clear on anything I will be as prompt as possible answering any questions.

 

[mamady]

Hi dear,

Please find if the formula in the attached file satisfies your requirement

Column Break.xlsx

drive.google.com

assuming that the information is in column A, and the listing starts at cell C1
=IF(COLUMN()=3,IF(ROW()<=10,$A1,""),IF(INDIRECT("A"&ROW()+10)=0,"",INDIRECT("A"&ROW()+10)))

COLUMN()=3 reflect column C, adjust where required
ROW()<=10 & ROW()+10 reflects number of data points in each column, adjust to your requirement

simply drag across the columns/rows

Best Regards
M. Yusuf

 

[jamiemarie]

Thank you for your reply. I tried the formula, and I'm a little confused. I was able to drag the first and second columns fine, but then the rest of the page ended up being a repeat of column 3?

I have downloaded XL2BB, here is what I could do with the formula.

PERSONAL.XLSB
	A	B	C	D	E	F	G	H	I
1	ITEMID	K-2716	ITEMID	K-2716	K-2716	K-2716	K-2716	K-2716	K-2716
2	HW-001	K-2740	HW-001	K-2740	K-2740	K-2740	K-2740	K-2740	K-2740
3	HW-002	K-2741	HW-002	K-2741	K-2741	K-2741	K-2741	K-2741	K-2741
4	HW-006	K-2957	HW-006	K-2957	K-2957	K-2957	K-2957	K-2957	K-2957
5	HW-008	K-402	HW-008	K-402	K-402	K-402	K-402	K-402	K-402
6	K-1111	K-4080	K-1111	K-4080	K-4080	K-4080	K-4080	K-4080	K-4080
7	K-1112	K-4081	K-1112	K-4081	K-4081	K-4081	K-4081	K-4081	K-4081
8	K-1113	K-4082	K-1113	K-4082	K-4082	K-4082	K-4082	K-4082	K-4082
9	K-1114	K-4083	K-1114	K-4083	K-4083	K-4083	K-4083	K-4083	K-4083
10	K-1115	K-4084	K-1115	K-4084	K-4084	K-4084	K-4084	K-4084	K-4084
11	K-1154	K-4085	K-1154	K-4085	K-4085	K-4085	K-4085	K-4085	K-4085
12	K-118	K-4086	K-118	K-4086	K-4086	K-4086	K-4086	K-4086	K-4086
13	K-121	K-4087	K-121	K-4087	K-4087	K-4087	K-4087	K-4087	K-4087
14	K-124	K-4088	K-124	K-4088	K-4088	K-4088	K-4088	K-4088	K-4088
15	K-127	K-4089	K-127	K-4089	K-4089	K-4089	K-4089	K-4089	K-4089
16	K-130	K-4090	K-130	K-4090	K-4090	K-4090	K-4090	K-4090	K-4090
17	K-133	K-4091	K-133	K-4091	K-4091	K-4091	K-4091	K-4091	K-4091
18	K-136	K-4097	K-136	K-4097	K-4097	K-4097	K-4097	K-4097	K-4097
19	K-139	K-4098	K-139	K-4098	K-4098	K-4098	K-4098	K-4098	K-4098
20	K-142	K-4099	K-142	K-4099	K-4099	K-4099	K-4099	K-4099	K-4099
21	K-145	K-4100	K-145	K-4100	K-4100	K-4100	K-4100	K-4100	K-4100
22	K-148	K-4101	K-148	K-4101	K-4101	K-4101	K-4101	K-4101	K-4101
23	K-151	SS-0013	K-151	SS-0013	SS-0013	SS-0013	SS-0013	SS-0013	SS-0013
24	K-154	SS-0014	K-154	SS-0014	SS-0014	SS-0014	SS-0014	SS-0014	SS-0014
25	K-1577	SS-0015	K-1577	SS-0015	SS-0015	SS-0015	SS-0015	SS-0015	SS-0015
26	K-1580	SS-0016	K-1580	SS-0016	SS-0016	SS-0016	SS-0016	SS-0016	SS-0016
27	K-1583	SS-0106	K-1583	SS-0106	SS-0106	SS-0106	SS-0106	SS-0106	SS-0106
28	K-1586	SS-0110	K-1586	SS-0110	SS-0110	SS-0110	SS-0110	SS-0110	SS-0110
29	K-1589	SS-0111	K-1589	SS-0111	SS-0111	SS-0111	SS-0111	SS-0111	SS-0111
30	K-1592	SS-0113	K-1592	SS-0113	SS-0113	SS-0113	SS-0113	SS-0113	SS-0113
31	K-1595	SS-0114	K-1595	SS-0114	SS-0114	SS-0114	SS-0114	SS-0114	SS-0114
32	K-1598	SS-0120	K-1598	SS-0120	SS-0120	SS-0120	SS-0120	SS-0120	SS-0120
33	K-1601	SS-0125	K-1601	SS-0125	SS-0125	SS-0125	SS-0125	SS-0125	SS-0125
34	K-1722	SS-0142	K-1722	SS-0142	SS-0142	SS-0142	SS-0142	SS-0142	SS-0142
35	K-1725	SS-0162	K-1725	SS-0162	SS-0162	SS-0162	SS-0162	SS-0162	SS-0162
36	K-1918	SS-0164	K-1918	SS-0164	SS-0164	SS-0164	SS-0164	SS-0164	SS-0164
37	K-1921	SS-0170	K-1921	SS-0170	SS-0170	SS-0170	SS-0170	SS-0170	SS-0170
38	K-1924	SS-0180	K-1924	SS-0180	SS-0180	SS-0180	SS-0180	SS-0180	SS-0180
39	K-1930	SS-0181	K-1930	SS-0181	SS-0181	SS-0181	SS-0181	SS-0181	SS-0181
40	K-1933	SS-0184	K-1933	SS-0184	SS-0184	SS-0184	SS-0184	SS-0184	SS-0184
41	K-1936	SS-0207	K-1936	SS-0207	SS-0207	SS-0207	SS-0207	SS-0207	SS-0207
42	K-2711	SS-0209	K-2711	SS-0209	SS-0209	SS-0209	SS-0209	SS-0209	SS-0209
43	K-2712	SS-0239	K-2712	SS-0239	SS-0239	SS-0239	SS-0239	SS-0239	SS-0239
44	K-2713	SS-0240	K-2713	SS-0240	SS-0240	SS-0240	SS-0240	SS-0240	SS-0240
45	K-2714	SS-0241	K-2714	SS-0241	SS-0241	SS-0241	SS-0241	SS-0241	SS-0241
46	K-2715	SS-0245	K-2715	SS-0245	SS-0245	SS-0245	SS-0245	SS-0245	SS-0245
47	K-2716	SS-0246		SS-0246	SS-0246	SS-0246	SS-0246	SS-0246	SS-0246
Sheet1

Cell Formulas
Range	Formula
B1:I47	B1	=IF(COLUMN()=3,IF(ROW()<=46,$A1,""),IF(INDIRECT("A"&ROW()+46)=0,"",INDIRECT("A"&ROW()+46)))

 

[jamiemarie]

Found A Solution!

In case anyone else comes across this thread. I found a macro that Tom Urtis posted on this board in 2014! So thank you so much Tom! His formula is below for anyone else that needs it. I just changed the # of rows I needed to suit my needs.

Sub Transfer100()
Application.ScreenUpdating = False
Dim LastRow&, NextRow&, NextColumn&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = 101
NextColumn = 2
Do
Range(Cells(NextRow, 1), Cells(NextRow + 99, 1)).Cut Cells(1, NextColumn)
NextRow = NextRow + 100
NextColumn = NextColumn + 1
Loop Until NextRow > LastRow
Application.ScreenUpdating = True
End Sub

 

[jamiemarie]

Ok Ok, Last time I'm updating this thread,. It was just super frustrating trying to figure this out as a newbie, especially when I was stuck on googling the wrong terms. If anyone else like me happens upon this thread, I want them to be as happy as I am to have a solution.

So I was able to modify Tom's macro to get exactly what i wanted, which is have one long column stop at the end of the print area and start in the next column while also having the header over each column. It may be sloppy, but it works.

Sub Split_Column()
Application.ScreenUpdating = False
Dim LastRow&, NextRow&, NextColumn&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = 47
NextColumn = 2
Do
Range(Cells(NextRow, 1), Cells(NextRow + 46, 1)).Cut Cells(1, NextColumn)
NextRow = NextRow + 46
NextColumn = NextColumn + 1
Loop Until NextRow > LastRow
Application.ScreenUpdating = True

Range("B1").Select
    Selection.Cut
    Range(Selection, Selection.End(xlDown)).Select
    Range("A47").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.Copy
    Range("B1:S1").Select
    ActiveSheet.Paste
End Sub

All you have to do is change how many rows you need and your range for your header.

Here is a screenshot of the end results.