Combine unique words in a single cell from a list of cells

[Analystbycuriosity]

I have a long list of following cells:

A11: XYZ A
A21: XYZ B
A31: XYZ C
A41: XYZ D

My desired Output:

A11: XYZ A B C D

Please help me in to find a function or formula.

 

[Gerald Higgins]

Hi.

I'm going to assume that you DON'T actually want the output to be in cell A11, which is also in the input range.

There are many ways to do this.

One very simple way would be like this . . .

=A11&" "&right(A21,1)&" "&right(A31,1)&" "&right(A41,1)

But I'm guessing your situation is really a little more complicated than what you have described, which will make the above solution inadequate.
So, if that's the case, tell us a bit more about those additional complexities that you left out of your first post.

Don't worry too much about explaining the real world application of your data.
Just focus on your data and the likely variations in it.

 

[Analystbycuriosity]

continued.......

Like XYZ there are 100 companies with different frequencies. All cells in same column.
A,B,S represents the portfolio in which the company XYZ,ABC,LMN is included.

XYZ A
XYZ B
XYZ S

ABC S
ABC B

LMN A

I have to keep scrolling down to check which company is in which portfolio. But i have time a constraint.
To take a quick decision i want the below output so that i can view entire list at one glance without scrolling. (After I get this output I shall place companies alphabetically in different columns which helps me keep data within screen area and zoomed at 55%)

OUTPUT:
Column A
XYZ A B S
ABC S B
LMN A

Please Help!

 

[Rick Rothstein]

XYZ A
XYZ B
XYZ S

ABC S
ABC B

LMN A

Is your data grouped as shown above? That is, are all the XYZ next to each other as shown and never scattered about throughout the column?

If yes, is there always a blank row between each of the groups?

Do you want the "collapsed" data overwriting the original data as your posts suggest or do you want the results outputted somewhere else so the original data is preserved?

 

[Gerald Higgins]

And are the portfolios ALL identified by a single character ?

If YES, that might make this alot easier.

If NO, please give some examples of real portfolio names.
Make up some typical ones if you don't want to post the real names.

 

[Analystbycuriosity]

Sorry for above complication.

A fraction of Real Example
Not scattered, have one blank row in between, prefer result outputted somewhere else

Axis Bank Limited (14/12/2021) IIFL
Axis Bank Ltd SunMF

Aditya Birla Finance Limited MOMF
Aditya Birla Finance Ltd SunMF

State Bank SunMF
State Bank (06/09/2021) IIFL
State Bank (16/03/2026) IIFL
State Bank MOMF

Expected output(Not exact) :
Axis Bank IIFL
Aditya Birla Finance MOMF SunMF
State Bank SunMF IIFL MOMF

 

[Rick Rothstein]

Expected output(Not exact) :

What does that mean? Remember, we know nothing about your data, so telling us your example is "not exact" does not really help us to know what we need to have our code give back to you.

Axis Bank IIFL
Aditya Birla Finance MOMF SunMF
State Bank SunMF IIFL MOMF

For example, in the above, why is the Axis Bank line not this instead...

Axis Bank IIFL SunMF

 

[Analystbycuriosity]

*Correction*
Axis Bank IIFL SunMF
Aditya Birla Finance MOMF SunMF
State Bank SunMF IIFL MOMF

I mentioned "Not exact" because I can accept outputs you can offer. Important thing is that my output includes Company name and portfolio name.
Data given is real.
Rick, it is great that you are asking questions which makes me think whether any changes should be included in output or not.

 

[Gerald Higgins]

So it looks like we are starting to see some rules to build into the solution, including . . .

1) Ignore any instance of "Limited" or "Ltd"
2) Ignore any date held in brackets
3) The fund name is identified as the string of characters following the last space character

Some of these rules are a bit fuzzy, which makes me suspect that there may be more rules like them, that we can't yet identify because the sample data you've provided was small.
What do you think ?

 

[Rick Rothstein]

Copy all of the following code into a general module and then run the Portfolios macro (output goes to Column B starting at Row 1)...
[table="width: 500"]
[tr]
[td]

Sub Portfolios()
  Dim X As Long, Z As Long, Ar As Range, Data As Range, Result As Variant
  Set Data = Columns("A").SpecialCells(xlConstants)
  ReDim Result(1 To Data.Areas.Count, 1 To 1)
  With CreateObject("Scripting.Dictionary")
    For Each Ar In Data.Areas
      X = X + 1
      For Z = 1 To Ar.Rows.Count
        .Item(Mid(Ar(Z).Value, InStrRev(Ar(Z).Value, " "))) = 1
      Next
      Result(X, 1) = CommonStart(Ar) & " " & Join(.Keys)
      .RemoveAll
    Next
  End With
  Range("B1").Resize(UBound(Result)) = Result
End Sub

Function CommonStart(ByVal V As Variant) As String
  Dim X As Long, L As Long, Letter As String
  If VarType(V) = vbString Then
    V = Split(V, ",")
  ElseIf TypeOf V Is Range Then
    If V.Rows.Count > 1 And V.Columns.Count > 1 Then
      Exit Function
    ElseIf V.Rows.Count > 1 Then
      V = Application.Transpose(V)
    ElseIf V.Columns.Count > 1 Then
      V = Application.Index(V.Value, 1, 0)
    Else
      Exit Function
    End If
  ElseIf Not IsArray(V) Then
    Exit Function
  End If
  Do
    L = L + 1
    Letter = Mid(V(LBound(V)), L, 1)
    For X = LBound(V) To UBound(V)
      If Mid(V(X) & X, L, 1) <> Letter Then
        If Len(Letter) Then
          CommonStart = RTrim(Left(V(LBound(V)), InStrRev(Left(V(LBound(V)), L), " ")))
        Else
          CommonStart = V(LBound(V))
        End If
        Exit Function
      End If
    Next
  Loop While Len(Letter)
End Function

[/td]
[/tr]
[/table]