Someone please help me with this one.

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
Good day,

I have a list that looks something like this:

ItemNumber DescriptionCost
100-13Open Coil Innerspring Demo78
1020025Rocker Recliner/Pikara219.99
1020035Loveseat/Pikara/Gunsmoke254.99
1020038Sofa/Pikara/Gunsmoke269.99
1030025Rocker Recliner/Carnell/Brown259.99
1030087Reclining Power Sofa/Carnell519.99
1030088Reclining Sofa/Carnell/Brown419.99
1030094Double Rec Loveseat w/Console419.99
1030096DBL REC PWR Loveseat w/Console519.99
1030098Power Rocker Recliner/Carnell309.99

<tbody>
</tbody>

I have 3 columns, each column as shown above has the following headers; Item Number, Description, and Cost.

I would like to insert "Item Number" under each new set of numbers. A set of numbers is the first 3 numbers.
For example; The first "Item Number" 100-13 is one number. I would like to insert a "Item Number" under it so I can sort the new set of numbers which in this case would be "1020025-1020038".

The 102 is the primary number that this list must be sorted by. The next set is 103 (1030025-1030098). Under each set of numbers i would like to add a blank row so i can insert the headers that correspond to what that column indicates ("Item Number, Description, Cost").

At the end it should look something like this:

ItemNumber DescriptionCost
100-13Open Coil Innerspring Demo78
ItemNumberDescriptionCost
1020025Rocker Recliner/Pikara219.99
1020035Loveseat/Pikara/Gunsmoke254.99
1020038Sofa/Pikara/Gunsmoke269.99
ItemNumberDescriptionCost
1030025Rocker Recliner/Carnell/Brown259.99
1030087Reclining Power Sofa/Carnell519.99
1030088Reclining Sofa/Carnell/Brown419.99
1030094Double Rec Loveseat w/Console419.99
1030096DBL REC PWR Loveseat w/Console519.99
1030098Power Rocker Recliner/Carnell309.99

<tbody>
</tbody>

<tbody>
</tbody>

Its a very large list of items, over 6300 items to be exact. Macros are welcome.
ANY HELP WOULD BE APPRECIATED.
You guys are awesome. Thank you
 
Last edited:
So it's always a single letter followed by 3 digits? Would you ever have a transition from say, B473-39 to 473 ...... and if yes, do you want a separator between those cells?

Correct, its a single letter followed by 3 numbers. As for the "B473-39 to 473", that doesnt exist. Although i do have "4730125" for example. So yes, those are two different items, which will need a separator. Hopefully this makes sense, I can send you a short copy if you would like to see what I am referring too.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Correct, its a single letter followed by 3 numbers. As for the "B473-39 to 473", that doesnt exist. Although i do have "4730125" for example. So yes, those are two different items, which will need a separator. Hopefully this makes sense, I can send you a short copy if you would like to see what I am referring too.
Not necessary. Let me see what I can do based on the information you have supplied.
 
Upvote 0
See if this works for you.
Code:
Sub InsertMoreHeaders()
Dim rHdrs As Range, i As Long
Set rHdrs = Range("A1:C1")
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
    If Left(Cells(i, "A"), 1) Like "[A-Za-z]" Then
        If Left(Cells(i, "A"), 1) <> Left(Cells(i - 1, "A"), 1) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        ElseIf Mid(Cells(i, "A"), 2, 3) <> Mid(Cells(i - 1, "A"), 2, 3) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        End If
    Else
        If Left(Cells(i, "A"), 3) <> Left(Cells(i - 1, "A"), 3) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
See if this works for you.
Code:
Sub InsertMoreHeaders()
Dim rHdrs As Range, i As Long
Set rHdrs = Range("A1:C1")
Application.ScreenUpdating = False
For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
    If Left(Cells(i, "A"), 1) Like "[A-Za-z]" Then
        If Left(Cells(i, "A"), 1) <> Left(Cells(i - 1, "A"), 1) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        ElseIf Mid(Cells(i, "A"), 2, 3) <> Mid(Cells(i - 1, "A"), 2, 3) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        End If
    Else
        If Left(Cells(i, "A"), 3) <> Left(Cells(i - 1, "A"), 3) Then
            Cells(i, "A").EntireRow.Insert
            With Cells(i, "A")
                .Resize(1, 3).Value = rHdrs.Value
                .Resize(1, 3).Font.Bold = True
            End With
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub

This did exactly what was needed. Thank you Very much once again. A gentleman and a scholar.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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