Merging Informatipn for Invoicing

[dmcgrathfaischools]

Hi
Please see the workbook attached.

Invoicing 2024-25.xlsx

I run a schools based soccer organisation & we offer competitions across five age groups, which we call Senior, Junior, Ty, Minor & First Year.
The workbook contains details of all schools that have entered competitions this season, all information is merged onto one sheet.
The sheet is currently organised by competition & the columns listed show the code of the competition (BSC = Boys Senior Cup, GFYBC = Girls First Year B Cup etc.) & the Yes/No response.

I need to find a simple way to merge all of this information, so that the sheet shows the following information:

Column Headings:
1) School Name
2) BSC
3) BSBC
4) BSL
5) GSC
6) GSBC
7) GJL
8) BJC
9) BJBC
10) BJL
11) BTYC
12) GJC
13) GJBC
14) GJL
15) BMC
16) BMBC
17) BML
18) GMC
19) GMBC
20) GML
21) BFYC
22) BFYBC
23) BFYL
24) GFYC
25) GFYBC
26) GFYL

Any takers?

Thanks

[AliGW]

Sorry - I don't follow this.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[JohnTopley]

Option Explicit

Sub Demo()

Dim i As Long, j As Long, lc As Long, n As Long, col As Long
Dim txt As String
Dim ar, arr
Dim t() As String
Dim dict As Object
Dim CompRng As Range

Dim comp(1 To 10) As String, a
ReDim b(1 To 600) As String

Set CompRng = Sheets("sheet2").Cells(1, 2).Resize(1, 25)

Application.ScreenUpdating = False


Set dict = CreateObject("scripting.dictionary")

    
      With Sheets("Sheet1")
           .Activate
            ar = [a1].CurrentRegion
            For i = 1 To UBound(ar, 1)
            If ar(i, 1) = "School Name" Then
                lc = .Cells(i, 1).End(xlToRight).Column
                For j = 2 To lc
                    comp(j - 1) = ar(i, j)
                Next j
                i = i + 1
            End If
            For j = 1 To lc - 1
                txt = ar(i, 1)
                If Not dict.Exists(txt) Then
                    n = n + 1
                     dict.Add txt, comp(j) & "|" & ar(i, j + 1)
                Else
                    If ar(i, j + 1) <> "" Then
                        dict.Item(txt) = dict.Item(txt) & "|" & comp(j) & "|" & ar(i, j + 1)
                    End If
                End If
                
            Next j
        Next
    
    End With


With Sheets("sheet2")
    For i = 0 To dict.Count - 1
    'Sheets("Sheet3").Cells(i + 1, 1).Resize(1, 2) = Array(dict.Keys()(i), dict.Items()(i))
    .Cells(i + 2, 1) = dict.Keys()(i)
    t = Split(dict.Items()(i), "|")
        For j = 0 To UBound(t, 1) Step 2
            col = Application.Match(t(j), CompRng, 0) + 1
            .Cells(i + 2, col) = t(j + 1)
        Next j
    Next i
End With

Application.ScreenUpdating = True

    
End Sub

[dmcgrathfaischools]

Thanks for both replies - I was about to put up an edited version when I saw John's reply - thanks.

However, the macro doesnt fully complete the requirement - I have attached the saved workbook again - if you look at sheet 2, which I have sorted by school name, the school appears more than once - is there another way to add/amedn the macro to merge this information also. There should be ballpark 300 schools in total. Sorry for being pernicity & I am extremely grateful for all help provided so far.

Thanks
DInvoicing 2024-25 Macro Version.xlsm

[JohnTopley]

The duplicates are a result of inconsistent naming:

Alexandra College Milltown
Alexandra College, Milltown

Ard Scoil Chiarain Naofa Clara
Ard Scoil Chiarain Naofa, Clara

Coláiste Chiarain Leixlip
Colaiste Chiarain, Leixlip
Coláiste Chiarain, Leixlip

St. Peters College Dunboyne
St. Peter's College Dunboyne

St. Wolstan's Community College Celbridge
St. Wolstan's Community School Celbridge

Are School/College the same ? (several occurrences of this type)


See attached with data sorted in Sheet2

[JohnTopley]

I have done some "cleaning" but there are still problems:

Examples....

Ardscoil na Trinoide Athy
Ardscoil na Trionoide Athy

Colaiste Bride Enniscorthy
Colaiste Briide Enniscorthy

ETSS Wicklow
Wicklow ETSS