Połącz wszystkie komórki spełniające kryteria (Excel)

Mam arkusz kalkulacyjny podobny do następującego:

Oczekiwanym wynikiem są wszystkie komórki zaczynające się od określonego tekstu połączonego w kolejności ich liczby wyglądu.

Formatowanie jak na obrazie nie jest konieczne, ale jest preferowane. Jednak liczba (1, 2, 3) przed każdą częścią jest dość ważna.

Próbowałem używać SUMIF i CONCAT w odniesieniu do tabel tęczy i procesów wieloetapowych, ale wszystkie one wymykały się spod kontroli, były powolne i zawodne.

Jeśli to możliwe, chciałbym to zrobić bez makr / UDF, ponieważ mój administrator systemu ich nie lubi, ale jeśli nie ma innego sposobu, który jest w porządku.

Każda pomoc jest mile widziana!

Proponuję rozwiązanie oparte na odrobinie kodu VBA i funkcji TEXTJOIN programu Excel 2016. Pamiętaj, że będzie to działać w programie Excel 2016 i nowszych, jednak nie będzie działać w niższych wersjach. W takim przypadku musisz utworzyć UDF dla TEXTJOIN. Mam na myśli Twój tag Excel 2016.

W tym przykładzie przykładowe dane znajdują się w komórkach E4: E15.

Naciśnij ALT+, F11aby otworzyć VBA Editor. Wstaw moduł z menu Wstaw i umieść w nim następujący kod.

Function GetString(a1 As String) As String

Dim array1 As Variant
Dim mystring As String

array1 = Split(a1, "|")

For i = LBound(array1) To UBound(array1)

    mystring = mystring & i + 1 & "." & array1(i) & " "

Next i

GetString = mystring

End Function

Zapisz plik Excela jako arkusz programu Excel z włączoną obsługą makr .XLSM.

Teraz na przykład w F4 wstaw następującą formułę i naciśnij CTRL+ SHIFT+, ENTERaby utworzyć i Array Formula. Formuła powinna być zamknięta w nawiasy klamrowe, aby wskazać, że jest to formuła tablicowa.

=GetString(TEXTJOIN("|",TRUE,IF(LEFT(E4:E14,3)="fob",RIGHT(E4:E14,LEN(E4:E14)-LEN("fob")),"")))

Teraz otrzymujesz swój ciąg według życzenia.

Ograniczenie - zwróć uwagę, że tutaj używam znaku potoku „|” jako ogranicznik. Tak więc w rzadkich przypadkach, jeśli sam tekst zawiera potok, wyniki mogą być niezgodne z oczekiwaniami. W takim przypadku użyj jednego z tych znaków, które można uzyskać za pomocą ALT + klawiatury numerycznej, np. ALT + 789, np. §

Jeśli wolisz czystą formułę Excela, sugeruję poniższe rozwiązanie oparte na kolumnie pomocniczej.

W tym przykładzie przykładowe dane znajdują się w komórkach B3: B13. Teraz w C3 umieść następującą formułę i naciśnij CTRL+ SHIFT+, ENTERaby utworzyć formułę tablicową. Teraz przeciągnij go w dół do zamierzonych wierszy.

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(LEFT($B$3:$B$13,3)="fob",ROW($B$3:$B$13)-2,""),ROW(A1))),"") 

Zwróć uwagę na -2 tutaj. Jest to ważne, ponieważ ustawia wartość indeksu na początek od 1 na podstawie względnego wiersza, w którym rozpoczyna się lista.

Ta formuła utworzy zaraźliwą listę wymaganych wierszy z oryginalnej kolumny.

Teraz w D3 wstaw następującą formułę i naciśnij CTRL+ SHIFT+ ENTERz paska formuły, aby utworzyć formułę Array.

=TEXTJOIN(",",TRUE,IF(LEN(C3:C13)=0,"",ROW(C3:C13)-2&"."&RIGHT(C3:C13,LEN(C3:C13)-LEN("fob")))) 

Teraz powinieneś dostać zamierzony ciąg znaków w D3. Zobacz poniższy zrzut ekranu.