Formatting issue for an itemized list

Please advise if the explanation provided on what I am looking to accomplish in the test workbook is not clear enough.

Thanks,

D

Attached the requested workbook. Sheet 1 contains the original information that the code provided formats. Sheet 2 shows the results of executing the code and shows what the format looks like. Sheet 3 shows the itemized list causing the issue. It is added into the original information.

The 1st workbook attached attempts to explain where the cells need to end up so that it all looks like sheet 2. This becomes complicated (at least in my mind) because it not only needs to reorder the cells, but draw from previous cells to fill in the missing pieces.

Here is the code provided to me, which works off of reassembling 13 cells of information per item. The additional code would need to address the issue of items that only contain 3 cells of information (because they are itemized under one number vs. one item per number) , but need the 10 other cells to draw from in order to reassemble like every other row of items.

I know it may sound a bit confusing, thus my cell by cell breakdown by colored backgrounds in the 1st workbook.

The code-

Option Explicit

Sub ReformatData()
Dim CaseFND As Range
Dim LR As Long, Rw As Long
Dim NR As Long
    
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Set CaseFND = Range("A:A").Find("Case:", LookIn:=xlValues, LookAt:=xlPart)
    If CaseFND Is Nothing Then Exit Sub

    Range("A1", CaseFND.Offset(-1)).Delete xlShiftUp
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For Rw = LR To 1 Step -1
        If Range("A" & Rw).Font.Bold = False Then
            Range("A" & Rw + 1, "A" & LR).Delete xlShiftUp
            Exit For
        End If
    Next Rw

    LR = Range("A" & Rows.Count).End(xlUp).Row
    NR = 1
    For Rw = 2 To LR Step 12
        Range("A" & Rw).Resize(12).Copy
        Range("B" & NR).PasteSpecial xlPasteAll, Transpose:=True
        NR = NR + 1
    Next Rw
    
    Range("A2:A" & LR).ClearContents
    Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

Attachments can't seem to be uploaded right now so i will have to wait-

d

Weird...would not let me upload the excel file, had to zip it first. First time that has happened.

Anywho here is the file, broken down on each sheet as requested. Hopefully between this working workbook and the "test2" workbook that breaks down the cell formatting needed, it is now clearer on what I am trying to achieve.

Thanks for all of your help,

Dave

Per Stan's advice I am selecting to BUMP this topic.

First- thank you Stan for the help that you have provide don this.

I am going to try and break this down as best I can.

What I am attempting to do is convert a html report into an Excel file.

Thus far I have been provided with two great solutions to a report that assigns one item number to an item. The coding takes information on each item and places it into one row per item,over several columns.

The information to form each item row comes from 13 cells in column A.

CASE NO: 020-10-08 A-1 Starting cell
MUTTON 98 LARS 89 B-1
N/A C-1
$100.00 D-1
N E-1
03-abc-011122 F-1 (1 item number per item)
1/29/2003 G-1
7/29/2005 H-1
5/29/2009 I-1
5/29/2003 J-1
N/A K-1
2744 L-1
N M-1 Ending Cell


Where I ran into an unexpected problem is that on some reports a single item number is used for multiple items, called an itemized list.

The reason the coding will not work in on an itemized list is because the information to form each row only comes from only 3 cells in column A.


$30.00 D-2
Lincoln Leg 3421 B-2
1 F-2
$69.00 D-3
Tanner 98 LARS B-3
2 F-3
$640.00 D-3
Banner 56 B-4
3 F-4


I am looking for coding that will identify and form a full 13 column single row for an itemized list item, using not only the 3 cells on the report (B, D, F), but also draw from the other 10 cells (A,C,E,G,H,I,J,K,L,M,N) of the last full 13 cell structure and repeat this process until reaching the end of the itemized list.

If that coding works then the converted report will have the same format, whether the item has a single item number, or is one of multiple items in a list that share the same item number.

Thank you for your patience and help, as this is not an easy thing to explain.

Dave

BUMP

Still looking for any guidance or assistance on adding coding within the provided codes to address itemized lists.

Thanks,

D