Transforming an Excel spreadsheet

[just_david]

So I've been given an excel spreadsheet to import into Outlook.

The problem is the format is all wrong.

 

I need to convert it from:

[TABLE=width: 1391]

[TR]

[TD]Day[/TD]

[TD]Mth[/TD]

[TD]Date[/TD]

[TD]Wk[/TD]

[TD]Mtg[/TD]

[TD]Ass[/TD]

[TD]General[/TD]

[TD]Y7[/TD]

[TD]Y8[/TD]

[TD]Y9[/TD]

[TD]Y10[/TD]

[TD]Y11[/TD]

[TD]Y12[/TD]

[TD]Y13[/TD]

[TD]Govs/SSOC[/TD]

[/TR]

[TR]

[TD]Tues[/TD]

[TD]Sep[/TD]

[TD=align: right]1[/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD]INSET Day[/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[/TR]

[TR]

[TD]Weds[/TD]

[TD]Sep[/TD]

[TD=align: right]2[/TD]

[TD]A[/TD]

[TD]SLT[/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[/TR]

[TR]

[TD]Thurs[/TD]

[TD]Sep[/TD]

[TD=align: right]3[/TD]

[TD]A[/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD]Enrichment Taster Session 4-6pm[/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD] [/TD]

[TD]Year 12 start[/TD]

[TD] [/TD]

[TD] [/TD]

[/TR]

[/TABLE]

into

 

[TABLE=width: 128]

[TR]

[TD=class: xl63, width: 64]Inset Day[/TD]

[TD=width: 64, align: right]10915[/TD]

[/TR]

[TR]

[TD=class: xl63]SLT[/TD]

[TD=align: right]20915[/TD]

[/TR]

[TR]

[TD=class: xl63]Enrichement Taster Session[/TD]

[TD=align: right]30915[/TD]

[/TR]

[TR]

[TD=class: xl63]Y12 Start[/TD]

[TD=align: right]30915

 

 

[/TD]

[/TR]

[/TABLE]

 

Every non blank cell from the fifth column onwards needs to get a new entry on a new row with the date calculated from the first 3 columns

 

And make a template file so this can be done often.

Then converted to a csv file

[pcstru]

I'd be resorting to a macro for this. How's your VBA?

[just_david]

Good enough that hearing you mention it made my heart sink.

I can modify existing scripts and do basic troubleshooting is about all.

[pcstru]

OK, if you can modify code then below is a bit of code which could form the core of what you need to do. It takes a selection block and then compiles a list of unique items in a sheet called tmp. You need to define your own selection and add a couple more things. When you find a new unique item, you need to grab data from the cells which contain the date. They will be on the same row so you can use the row attribute of the current cell to find that and then reference the appropriate cells for the date and write that to tmp. Lastly, save the tmp sheet as a csv - you should be able to use the record macro to generate the code for that.

 

Sub UnqBlock()

   Dim sRange, tRange As Range
   
   Set sRange = Selection
   Set tRange = Worksheets("tmp").Range("A1:A10000")
   
   Dim iIdx As Integer
   Dim finished As Boolean
   
   ' Determine unique values in a block of data
   For Each cell In sRange
       
       iIdx = 1
       finished = False
       
       While iIdx < 10000 And Not finished
           If tRange(iIdx, 1).Value = cell.Value Then
               finished = True
           Else
               If tRange(iIdx, 1).Value = "" Then
                   tRange(iIdx, 1).Value = cell.Value
                   finished = True
               End If
           End If
           iIdx = iIdx + 1
       Wend
                   
   Next
   

End Sub

[howartp]

Can you post the spreadsheet, or a few lines of, rather than a copy-paste?