Formula assistance

[Christmas May]

I'm given two columns. Column A is an object description. Column B is how
many of that object I have.

Steel blocks 3
Wood blocks 4
Plastic blocks 5
Aluminum blocks 3
Copper blocks 4
Graphite blocks 3

All the blocks are the same size and I have containers to hold specific
quantites of each.

3
4
5

I'm in need of a formula (preferrably not VBA) to populate column B with
which objects will be in which containers
3 Steel blocks, Aluminum blocks, Graphite blocks
4 Wood blocks, Copper blocks
5 Plastic blocks

Any assistance would be greatly appreciated.

Thanks in advance,

Christmas May

 

[Jacob Skaria]

If you need the data in Column B (cell data combined with comma delimiter)
using formula then you will have to use multiple IF statements; but again if
you have a big list even that would not be possible. You will have ti use a
VBA solution. Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the below formula.

Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, 1)), _
strLookupValue, vbTextCompare) = 0 Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1)
End Function


If this post helps click Yes