Help simplifying formula (if possible)

[elitef]

Hello All,
I have a large spreadsheet which I am working off (20+K rows)

The following is a formula which I wrote, but when I fill down, it takes a long time to process, so just wondering if there is a way to simplify it.
Any help would be greatly appreciated.

=IF(A1="","",
IF(COUNTIF(C:C,C1)=1,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=2,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=3,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=4,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=5,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=6,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=7,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=8,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=9,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:I1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=10,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:I1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:J1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=11,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:I1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:J1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:K1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=12,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:I1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:J1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:K1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:L1),$A$1:$G$999999,7,0),"")),
IF(COUNTIF(C:C,C1)=13,CONCATENATE(IFNA(VLOOKUP($C1&COLUMNS($A$1:A1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:B1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:C1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:D1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:E1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:F1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:G1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:H1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:I1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:J1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:K1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:L1),$A$1:$G$999999,7,0),""),"|",IFNA(VLOOKUP($C1&COLUMNS($A$1:M1),$A$1:$G$999999,7,0),"")),
""))))))))))))))

 

[DanteAmor]

I tried to understand a bit the logic of your formula and take it to a macro.


If you execute the next macro it will put the result in cell K1.
According to your formula, the macro takes the data from cell A1 and C1.


I suppose that the formula is going to put in several rows, but we are going to try this macrop only for cell A1 and then you explain where the formula goes and if you copy it down, as far as the copies.

Try and tell me.

Sub [COLOR=#008000]simplifying_formula[/COLOR]()
    Dim lra As Long, lrc As Long, n As Long, i As Long, cad As String, a As Range, b As Range, c As Range
    
    Set a = Range("K1")
    Set c = Range("C1")
    
    lra = Range("C" & Rows.Count).End(xlUp).Row
    lrc = Range("C" & Rows.Count).End(xlUp).Row
    If Range("A1") = "" Then
        a.Value = ""
    Else
        n = WorksheetFunction.CountIf(Range("C1:C" & lrc), c.Value)
        For i = 1 To n
            Set b = Range("A1:A" & lra).Find(c.Value & i, LookIn:=xlValues, lookat:=xlWhole)
            If Not b Is Nothing Then cad = cad & Cells(b.Row, "G") & "|"
        Next
    End If
    If cad <> "" Then a.Value = Left(cad, Len(cad) - 1)
End Sub

INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.
Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select simplifying_formula and press Run.