During another fun day of excel filling for program usages of Mainframe systems , i had program list ,which i fed into a jenkins search and got where they were used. Many programs were used in many modules and I wanted to show one program’s usages in single cell. So i google it. There were two options according to your excel version:
1- For newer Excel versions ; you should use textjoin , unique and if functions combined. A column is for lookup , b for value.
2- For older, poorer excel versions : You should be writing a VBA function. Unfortunately that was what i had to do. Put Alt + F11 and insert a module.
'Code by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells.Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & vbNewLine End If Next i SingleCellExtract = Trim(Trim(Left(Result, Len(Result) ))) End Function
Then you should call in the cell =SingleCellExtract and give parameters.
Do not give unlimited range because that really hurts performance.
Format your cells for WRAP TEXT option otherwise newlines are omitted.
For more goto my reference: