Splitting rows based on number of entries in a cell

K

KW1M

New Member
Joined
Jul 21, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hitting a wall when I try to record a macro...

I "think" this is straightforward but my brain is not working...

What I want to accomplish:
  1. Column A (already done here to remove IP)
    1. Select column A; remove hyperlinks, remove underscores (manual now and easy)
  2. Dummy data in Columns B:H
  3. Column I (labeled CCR) - our bug tracking system.
    1. CCR is always 7 characters with a space delimiter between CCRs when more than one appears on arom
    2. Desired result (manual today)
      1. In example, row 10 has 2 CCRs
      2. Copy row, paste insert
      3. in row 10 (original) - delete last CCR and space
      4. In new row 11 - delete first CCR and space
      5. Reiterate until no more data value in Column A
    3. Sometimes there will be 3+ or more CCRs, need to copy/insert for 3+ total rows
    4. When all done, select column I and "convert to a number" (warning says number saved as text which screws up my follow on macros to lookup that value).
Appears my IT has disabled use of XL2BB
KeySummaryPToolProjectStatusLabelsFix Version/sCCR
CCR-1325DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE1.13072328
CCR-1309DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE
CCR-1281DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE3071232
CCR-1276DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE4.13045964
CCR-1403DUMMY TITLECriticalTDEFAULTJUNKNONSENSE
CCR-1402DUMMY TITLECriticalWDEFAULTJUNKNONSENSE3118926
CCR-1396DUMMY TITLECriticalFDEFAULTJUNKNONSENSE
CCR-1362DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103144
CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103241 3104005
CCR-1201DUMMY TITLECriticalDEFAULTJUNKNONSENSE3067005
CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.12959321 3000606
CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983897 2983898
CCR-1188DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983900
CCR-1153DUMMY TITLECriticalDEFAULTJUNKNONSENSE2958715
CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972142 2972144
CCR-886DUMMY TITLECriticalDEFAULTJUNKNONSENSE2949579
CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2925388 2936952
CCR-828DUMMY TITLECriticalDEFAULTJUNKNONSENSE2908605
CCR-904DUMMY TITLECriticalDEFAULTJUNKNONSENSE2891958
CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12810172 2823252 2823255
CCR-1075DUMMY TITLEMajorDEFAULTJUNKNONSENSE2807282

Thanks, Martin
 
Sort by date Sort by votes
All maual now. Highlght row, copy, paste/insert copy.

Tried recording a macro without luck.
 
Upvote 0
Easily accomplished with Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"CCR", type text}}, "en-US"), {{"CCR", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CCR")
in
    #"Split Column by Delimiter"

Book5
ABCDEFGHI
1KeySummaryPToolProjectStatusLabelsFix Version/sCCR
2CCR-1325DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE1.13072328
3CCR-1309DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE
4CCR-1281DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE3071232
5CCR-1276DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE4.13045964
6CCR-1403DUMMY TITLECriticalTDEFAULTJUNKNONSENSE
7CCR-1402DUMMY TITLECriticalWDEFAULTJUNKNONSENSE3118926
8CCR-1396DUMMY TITLECriticalFDEFAULTJUNKNONSENSE
9CCR-1362DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103144
10CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103241 3104005
11CCR-1201DUMMY TITLECriticalDEFAULTJUNKNONSENSE3067005
12CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.12959321 3000606
13CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983897 2983898
14CCR-1188DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983900
15CCR-1153DUMMY TITLECriticalDEFAULTJUNKNONSENSE2958715
16CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972142 2972144
17CCR-886DUMMY TITLECriticalDEFAULTJUNKNONSENSE2949579
18CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2925388 2936952
19CCR-828DUMMY TITLECriticalDEFAULTJUNKNONSENSE2908605
20CCR-904DUMMY TITLECriticalDEFAULTJUNKNONSENSE2891958
21CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12810172 2823252 2823255
22CCR-1075DUMMY TITLEMajorDEFAULTJUNKNONSENSE2807282
23
24KeySummaryPToolProjectStatusLabelsFix Version/sCCR
25CCR-1325DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE1.13072328
26CCR-1309DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE
27CCR-1281DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE3071232
28CCR-1276DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE4.13045964
29CCR-1403DUMMY TITLECriticalTDEFAULTJUNKNONSENSE
30CCR-1402DUMMY TITLECriticalWDEFAULTJUNKNONSENSE3118926
31CCR-1396DUMMY TITLECriticalFDEFAULTJUNKNONSENSE
32CCR-1362DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103144
33CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103241
34CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3104005
35CCR-1201DUMMY TITLECriticalDEFAULTJUNKNONSENSE3067005
36CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.12959321
37CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.13000606
38CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983897
39CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983898
40CCR-1188DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983900
41CCR-1153DUMMY TITLECriticalDEFAULTJUNKNONSENSE2958715
42CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972142
43CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972144
44CCR-886DUMMY TITLECriticalDEFAULTJUNKNONSENSE2949579
45CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2925388
46CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2936952
47CCR-828DUMMY TITLECriticalDEFAULTJUNKNONSENSE2908605
48CCR-904DUMMY TITLECriticalDEFAULTJUNKNONSENSE2891958
49CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12810172
50CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12823252
51CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12823255
52CCR-1075DUMMY TITLEMajorDEFAULTJUNKNONSENSE2807282
Sheet1


Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
www.excelcampus.com

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.
www.excelcampus.com www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.
www.excelcampus.com

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.
www.excelcampus.com www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...
excel.solutions
 
Upvote 0
Solution
Thanks -- I'll see if I can install PowerQuery... played for a while and brute forced it like this:

VBA Code: 
Sub Split_CCRs()
'
' Split_CCRs Macro
'
' Keyboard Shortcut: Ctrl+q
'

' Loops thru entire worksheet and duplicates rows where CCR (Column I) has more than one CCR
    botRow = 600
    For i = botRow To 1 Step -1
      If Len(Cells(i, 9).Value) = "15" Then
        Rows(i).EntireRow.Copy
        Rows(i + 1).Insert Shift:=xlDown
        Cells(i, 9) = Mid(Cells(i, 9), 1, 7)
        Cells(i + 1, 9) = Mid(Cells(i + 1, 9), 9, 7)
      End If
      If Len(Cells(i, 9).Value) = "23" Then
        Rows(i).EntireRow.Copy
        Rows(i + 1).Insert Shift:=xlDown
        Rows(i).EntireRow.Copy
        Rows(i + 1).Insert Shift:=xlDown
        Cells(i, 9) = Mid(Cells(i, 9), 1, 7)
        Cells(i + 1, 9) = Mid(Cells(i + 1, 9), 9, 7)
        Cells(i + 2, 9) = Mid(Cells(i + 2, 9), 17, 7)
      End If
    Next i
    
' Convert Text to Number to resolve Excel Warning/Error
    Range("I:I").Select 
    With Selection
        .NumberFormat = "General"
        .Value = .Value
    End With
    
' Remove hyerlinks and underline from Column A
    Columns("A:A").Select
    Selection.Hyperlinks.Delete
    Selection.Font.Underline = xlUnderlineStyleNone

' Increase Row Height to accomodate Titles
    Rows("1:1").Select
    Selection.RowHeight = 30
    
End Sub
 
Upvote 0
No need to install. It is on the Data Tab and called "Get and Transform Data"
 
Upvote 0
You could also do this with a single worksheet formula in the top-left result cell. The formula below uses the new REGEXREPLACE function but if you don't have that it can still be done with just a slightly longer formula.

KW1M.xlsm
ABCDEFGHI
1KeySummaryPToolProjectStatusLabelsFix Version/sCCR
2CCR-1325DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE1.13072328
3CCR-1309DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE
4CCR-1281DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE3071232
5CCR-1276DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE4.13045964
6CCR-1403DUMMY TITLECriticalTDEFAULTJUNKNONSENSE
7CCR-1402DUMMY TITLECriticalWDEFAULTJUNKNONSENSE3118926
8CCR-1396DUMMY TITLECriticalFDEFAULTJUNKNONSENSE
9CCR-1362DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103144
10CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103241 3104005
11CCR-1201DUMMY TITLECriticalDEFAULTJUNKNONSENSE3067005
12CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.12959321 3000606
13CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983897 2983898
14CCR-1188DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983900
15CCR-1153DUMMY TITLECriticalDEFAULTJUNKNONSENSE2958715
16CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972142 2972144
17CCR-886DUMMY TITLECriticalDEFAULTJUNKNONSENSE2949579
18CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2925388 2936952
19CCR-828DUMMY TITLECriticalDEFAULTJUNKNONSENSE2908605
20CCR-904DUMMY TITLECriticalDEFAULTJUNKNONSENSE2891958
21CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12810172 2823252 2823255
22CCR-1075DUMMY TITLEMajorDEFAULTJUNKNONSENSE2807282
23
24KeySummaryPToolProjectStatusLabelsFix Version/sCCR
25CCR-1325DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE1.13072328
26CCR-1309DUMMY TITLEBlockerBDEFAULTJUNKNONSENSE
27CCR-1281DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE3071232
28CCR-1276DUMMY TITLEBlockerDDEFAULTJUNKNONSENSE4.13045964
29CCR-1403DUMMY TITLECriticalTDEFAULTJUNKNONSENSE
30CCR-1402DUMMY TITLECriticalWDEFAULTJUNKNONSENSE3118926
31CCR-1396DUMMY TITLECriticalFDEFAULTJUNKNONSENSE
32CCR-1362DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103144
33CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3103241
34CCR-1361DUMMY TITLECriticalDEFAULTJUNKNONSENSE3104005
35CCR-1201DUMMY TITLECriticalDEFAULTJUNKNONSENSE3067005
36CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.12959321
37CCR-1192DUMMY TITLECriticalDEFAULTJUNKNONSENSE2.13000606
38CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983897
39CCR-1191DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983898
40CCR-1188DUMMY TITLECriticalDEFAULTJUNKNONSENSE2983900
41CCR-1153DUMMY TITLECriticalDEFAULTJUNKNONSENSE2958715
42CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972142
43CCR-990DUMMY TITLECriticalDEFAULTJUNKNONSENSE2972144
44CCR-886DUMMY TITLECriticalDEFAULTJUNKNONSENSE2949579
45CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2925388
46CCR-945DUMMY TITLECriticalDEFAULTJUNKNONSENSE2936952
47CCR-828DUMMY TITLECriticalDEFAULTJUNKNONSENSE2908605
48CCR-904DUMMY TITLECriticalDEFAULTJUNKNONSENSE2891958
49CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12810172
50CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12823252
51CCR-761DUMMY TITLEMajorDEFAULTJUNKNONSENSE2.12823255
52CCR-1075DUMMY TITLEMajorDEFAULTJUNKNONSENSE2807282
Split Rows
Cell Formulas
RangeFormula
A24:I52A24=LET(d,A1:I22,x,TAKE(d,,-1),rw,TEXTSPLIT(TEXTJOIN(" ",,REGEXREPLACE(1&x,"\w+",ROW(x)-ROW(TAKE(x,1))+1)),," "),ccr,TEXTSPLIT(TEXTJOIN(" ",0,x),," "), a,MAKEARRAY(ROWS(rw),COLUMNS(d),LAMBDA(r,c,IF(c<9,INDEX(d&"",INDEX(rw,r),c),INDEX(ccr,r)))),IFERROR(--a,a))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Share this page

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