Inserting a Column Break in Excel/Microsoft 365

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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.

Have.PNG
Need.PNG
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mamady

Board Regular
Joined
Sep 23, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
Hi dear,

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


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

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1ITEMIDK-2716ITEMIDK-2716K-2716K-2716K-2716K-2716K-2716
2HW-001K-2740HW-001K-2740K-2740K-2740K-2740K-2740K-2740
3HW-002K-2741HW-002K-2741K-2741K-2741K-2741K-2741K-2741
4HW-006K-2957HW-006K-2957K-2957K-2957K-2957K-2957K-2957
5HW-008K-402HW-008K-402K-402K-402K-402K-402K-402
6K-1111K-4080K-1111K-4080K-4080K-4080K-4080K-4080K-4080
7K-1112K-4081K-1112K-4081K-4081K-4081K-4081K-4081K-4081
8K-1113K-4082K-1113K-4082K-4082K-4082K-4082K-4082K-4082
9K-1114K-4083K-1114K-4083K-4083K-4083K-4083K-4083K-4083
10K-1115K-4084K-1115K-4084K-4084K-4084K-4084K-4084K-4084
11K-1154K-4085K-1154K-4085K-4085K-4085K-4085K-4085K-4085
12K-118K-4086K-118K-4086K-4086K-4086K-4086K-4086K-4086
13K-121K-4087K-121K-4087K-4087K-4087K-4087K-4087K-4087
14K-124K-4088K-124K-4088K-4088K-4088K-4088K-4088K-4088
15K-127K-4089K-127K-4089K-4089K-4089K-4089K-4089K-4089
16K-130K-4090K-130K-4090K-4090K-4090K-4090K-4090K-4090
17K-133K-4091K-133K-4091K-4091K-4091K-4091K-4091K-4091
18K-136K-4097K-136K-4097K-4097K-4097K-4097K-4097K-4097
19K-139K-4098K-139K-4098K-4098K-4098K-4098K-4098K-4098
20K-142K-4099K-142K-4099K-4099K-4099K-4099K-4099K-4099
21K-145K-4100K-145K-4100K-4100K-4100K-4100K-4100K-4100
22K-148K-4101K-148K-4101K-4101K-4101K-4101K-4101K-4101
23K-151SS-0013K-151SS-0013SS-0013SS-0013SS-0013SS-0013SS-0013
24K-154SS-0014K-154SS-0014SS-0014SS-0014SS-0014SS-0014SS-0014
25K-1577SS-0015K-1577SS-0015SS-0015SS-0015SS-0015SS-0015SS-0015
26K-1580SS-0016K-1580SS-0016SS-0016SS-0016SS-0016SS-0016SS-0016
27K-1583SS-0106K-1583SS-0106SS-0106SS-0106SS-0106SS-0106SS-0106
28K-1586SS-0110K-1586SS-0110SS-0110SS-0110SS-0110SS-0110SS-0110
29K-1589SS-0111K-1589SS-0111SS-0111SS-0111SS-0111SS-0111SS-0111
30K-1592SS-0113K-1592SS-0113SS-0113SS-0113SS-0113SS-0113SS-0113
31K-1595SS-0114K-1595SS-0114SS-0114SS-0114SS-0114SS-0114SS-0114
32K-1598SS-0120K-1598SS-0120SS-0120SS-0120SS-0120SS-0120SS-0120
33K-1601SS-0125K-1601SS-0125SS-0125SS-0125SS-0125SS-0125SS-0125
34K-1722SS-0142K-1722SS-0142SS-0142SS-0142SS-0142SS-0142SS-0142
35K-1725SS-0162K-1725SS-0162SS-0162SS-0162SS-0162SS-0162SS-0162
36K-1918SS-0164K-1918SS-0164SS-0164SS-0164SS-0164SS-0164SS-0164
37K-1921SS-0170K-1921SS-0170SS-0170SS-0170SS-0170SS-0170SS-0170
38K-1924SS-0180K-1924SS-0180SS-0180SS-0180SS-0180SS-0180SS-0180
39K-1930SS-0181K-1930SS-0181SS-0181SS-0181SS-0181SS-0181SS-0181
40K-1933SS-0184K-1933SS-0184SS-0184SS-0184SS-0184SS-0184SS-0184
41K-1936SS-0207K-1936SS-0207SS-0207SS-0207SS-0207SS-0207SS-0207
42K-2711SS-0209K-2711SS-0209SS-0209SS-0209SS-0209SS-0209SS-0209
43K-2712SS-0239K-2712SS-0239SS-0239SS-0239SS-0239SS-0239SS-0239
44K-2713SS-0240K-2713SS-0240SS-0240SS-0240SS-0240SS-0240SS-0240
45K-2714SS-0241K-2714SS-0241SS-0241SS-0241SS-0241SS-0241SS-0241
46K-2715SS-0245K-2715SS-0245SS-0245SS-0245SS-0245SS-0245SS-0245
47K-2716SS-0246 SS-0246SS-0246SS-0246SS-0246SS-0246SS-0246
Sheet1
Cell Formulas
RangeFormula
B1:I47B1=IF(COLUMN()=3,IF(ROW()<=46,$A1,""),IF(INDIRECT("A"&ROW()+46)=0,"",INDIRECT("A"&ROW()+46)))
 

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.

1593637285006.png
 

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,209
Members
410,905
Latest member
Extjel
Top