calculate difference between two separate filtered measure

Got it! Here's how we can approach writing a VBA script to create the new Excel file with all the formatting you need. I'll break it down into steps based on your description, and I'll provide the VBA code for each.

### Steps Breakdown:
1. **Customer Reference** – Copy as is.
2. **Full Name** – Combine Forename, Middle Name, and Surname into one column with proper capitalization.
3. **Date of Birth** – Combine the day, month, and year columns into one column in the `DD/MM/YYYY` format.
4. **Address** – Combine the address columns (building number, street, postcode, city, and country) into one column, ensuring capitalization and no extra spaces.
5. **Email and Mobile** – Add after the address.
6. **Codes and Meanings** – Use an IF function to display the appropriate meaning based on the code.
7. **Output** – Create a new sheet with these combined columns in the same order.

---

### VBA Code:

```vba
Sub CreateNewFile()
Dim wsSource As Worksheet
Dim wsNew As Worksheet
Dim lastRow As Long
Dim i As Long
Dim customerRef As String, fullName As String, dob As String, address As String, email As String, mobile As String
Dim code As String, meaning As String
Dim forename As String, middleName As String, surname As String
Dim day As String, month As String, year As String
Dim building As String, street As String, postcode As String, city As String, country As String

' Reference to the original sheet (adjust if your sheet name is different)
Set wsSource = ThisWorkbook.Sheets("Sheet1")

' Create new sheet
Set wsNew = ThisWorkbook.Sheets.Add
wsNew.Name = "FormattedData"

' Get the last row in the source data (assuming data starts from row 2, row 1 being headers)
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

' Loop through each row of data
For i = 2 To lastRow
' 1. Customer Reference
customerRef = wsSource.Cells(i, 1).Value

' 2. Full Name (combine Forename, Middle Name, Surname)
forename = Application.WorksheetFunction.Proper(wsSource.Cells(i, 2).Value)
middleName = Application.WorksheetFunction.Proper(wsSource.Cells(i, 3).Value)
surname = Application.WorksheetFunction.Proper(wsSource.Cells(i, 4).Value)
fullName = forename & " " & middleName & " " & surname

' 3. Date of Birth (combine Day, Month, Year)
day = Format(wsSource.Cells(i, 5).Value, "00")
month = Format(wsSource.Cells(i, 6).Value, "00")
year = wsSource.Cells(i, 7).Value
dob = day & "/" & month & "/" & year

' 4. Address (combine Building, Street, Postcode, City, Country)
building = wsSource.Cells(i, 8).Value
street = wsSource.Cells(i, 9).Value
postcode = wsSource.Cells(i, 10).Value
city = wsSource.Cells(i, 11).Value
country = wsSource.Cells(i, 12).Value
address = Application.WorksheetFunction.Proper(Trim(building & " " & street & " " & postcode & " " & city & " " & country))

' 5. Email and Mobile (get email and mobile)
email = wsSource.Cells(i, 13).Value
mobile = wsSource.Cells(i, 14).Value

' 6. Codes and Meanings (example logic)
code = wsSource.Cells(i, 15).Value
meaning = GetCodeMeaning(code)

' 7. Add the results to the new sheet (order as requested)
wsNew.Cells(i, 1).Value = customerRef
wsNew.Cells(i, 2).Value = fullName
wsNew.Cells(i, 3).Value = dob
wsNew.Cells(i, 4).Value = address
wsNew.Cells(i, 5).Value = email
wsNew.Cells(i, 6).Value = mobile
wsNew.Cells(i, 7).Value = meaning
Next i
End Sub

' Function to get the meaning of a code (can be expanded for more codes)
Function GetCodeMeaning(code As String) As String
Select Case code
Case "A1"
GetCodeMeaning = "Meaning 1"
Case "B2"
GetCodeMeaning = "Meaning 2"
Case "C3"
GetCodeMeaning = "Meaning 3"
Case Else
GetCodeMeaning = "Unknown Code"
End Select
End Function
```

### Explanation:

1. **Customer Reference** – Simply copies the customer reference as it is.
2. **Full Name** – The `PROPER` function is used to ensure the first letter of each name part is capitalized. We combine the forename, middle name, and surname.
3. **Date of Birth** – The day, month, and year columns are combined using `Format` to ensure leading zeros for the day and month. We concatenate them in `DD/MM/YYYY` format.
4. **Address** – The building, street, postcode, city, and country are combined into one string, and `PROPER` ensures the address is properly capitalized. `Trim` ensures no extra spaces.
5. **Email and Mobile** – These are placed after the address.
6. **Codes and Meanings** – The `GetCodeMeaning` function checks the code and returns the corresponding meaning.
7. **New Sheet** – The script creates a new sheet called `"FormattedData"`, and it fills the columns with the formatted data in the order you requested.

---

### How to Use the Code:

1. Press `Alt + F11` to open the VBA editor in Excel.
2. In the editor, click `Insert > Module` to create a new module.
3. Paste the code into this module.
4. Press `F5` or run the macro from Excel to execute it.

This will create a new sheet in the original workbook with the formatted data as specified. If you need to adjust any of the column indexes or logic, feel free to tweak the code. Let me know if you'd like any further modifications or explanations!