Someone please help me with this one.

[i8ur4re]

Good day,

I have a list that looks something like this:

ItemNumber	Description	Cost
100-13	Open Coil Innerspring Demo	78
1020025	Rocker Recliner/Pikara	219.99
1020035	Loveseat/Pikara/Gunsmoke	254.99
1020038	Sofa/Pikara/Gunsmoke	269.99
1030025	Rocker Recliner/Carnell/Brown	259.99
1030087	Reclining Power Sofa/Carnell	519.99
1030088	Reclining Sofa/Carnell/Brown	419.99
1030094	Double Rec Loveseat w/Console	419.99
1030096	DBL REC PWR Loveseat w/Console	519.99
1030098	Power Rocker Recliner/Carnell	309.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 Description Cost 100-13 Open Coil Innerspring Demo 78 ItemNumber Description Cost 1020025 Rocker Recliner/Pikara 219.99 1020035 Loveseat/Pikara/Gunsmoke 254.99 1020038 Sofa/Pikara/Gunsmoke 269.99 ItemNumber Description Cost 1030025 Rocker Recliner/Carnell/Brown 259.99 1030087 Reclining Power Sofa/Carnell 519.99 1030088 Reclining Sofa/Carnell/Brown 419.99 1030094 Double Rec Loveseat w/Console 419.99 1030096 DBL REC PWR Loveseat w/Console 519.99 1030098 Power Rocker Recliner/Carnell 309.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

 

[JoeMo]

This assumes your first header is in cell A1.

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"), 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
Next i
Application.ScreenUpdating = True
End Sub

 

[deletedalien]

I was JUST about to suggest a helper cell with left 3 formula and then subtotals but that code that Joemo suggested is CLEARLY the better choice.

 

[i8ur4re]

THANK YOU SO MUCH. I really appreciate it. You are a life saver, not the candy, just a life saver. Kudos to you

 

[JoeMo]

THANK YOU SO MUCH. I really appreciate it. You are a life saver, not the candy, just a life saver. Kudos to you

You are welcome - thanks for the reply.

 

[i8ur4re]

Least i can do. I just have one little problem, sorry i never mentioned it in the post. But i also have letters for some of the item numbers, i realized this worked beautifully for the numbers. What if i have letters as well. For example; B044-52,45,23, that would be one set of numbers, the next one would be B045, etc. I noticed that the macro did work for some of the letters, but not all. Any ideas. Thank you in advance

 

[JoeMo]

Least i can do. I just have one little problem, sorry i never mentioned it in the post. But i also have letters for some of the item numbers, i realized this worked beautifully for the numbers. What if i have letters as well. For example; B044-52,45,23, that would be one set of numbers, the next one would be B045, etc. I noticed that the macro did work for some of the letters, but not all. Any ideas. Thank you in advance

Can you clarify? You may have both numbers and letters at the start of an entry. If numbers, then the first 3 digits define a "set". If letters, there is a single letter followed by 3 digits that define a 'set". Is that correct?

Can you post a few samples of the letters type to go with the data in your OP?

 

[i8ur4re]

The letter in the beginning lets me know what kind of item it is (B=Bed). The numbers preceding is the item number.

The letters change, but the 3 numbers following that letter are for a set. Below is an example.

B470-94

B470-96

B470-97

B473-31

B473-36

B473-39

<colgroup><col></colgroup><tbody>
</tbody>

 

[JoeMo]

The letter in the beginning lets me know what kind of item it is (B=Bed). The numbers preceding is the item number.

The letters change, but the 3 numbers following that letter are for a set. Below is an example.

B470-94

B470-96

B470-97

B473-31

B473-36

B473-39

<tbody>
</tbody>

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?

 

[SpillerBD]

I was JUST about to suggest a helper cell with left 3 formula and then subtotals but that code that Joemo suggested is CLEARLY the better choice.

I agree with using a helper cell. While Joe's solution does have a certain elegance, it means that the table is de-normalized. That may limit its use in other features (or at least make some features more complicated.)