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.
=TEXTJOIN(", ",TRUE,UNIQUE(IF(D2=$A$2:$A$20,$B$2:$B$20,"")))
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:
Leave a Reply