Hi All,
I'm Confused how create a formula for this.
Here all Product Collate in single cell based on the Ref No.
Plz help this
Hi All,
I'm Confused how create a formula for this.
Here all Product Collate in single cell based on the Ref No.
Plz help this
Are you OK t use VBA for this? If not, what is the MAXIMUM number of products that will be collated in one cell?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Here you go, I used UDF & INDEX+MATCH to consolidate as you required,
Pls refer the sheet for more info
UDF:
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant Dim xResult As String Dim i As Integer On Error Resume Next If CriteriaRange.Count <> ConcatenateRange.Count Then ConcatenateIf = CVErr(xlErrRef) Exit Function End If For i = 1 To CriteriaRange.Count If CriteriaRange.Cells(i).Value = Condition Then xResult = xResult & Separator & ConcatenateRange.Cells(i).Value End If Next i If xResult <> "" Then xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1) End If ConcatenateIf = xResult Exit Function End Function
Regards,
Thangavel D
Appreciate the help? CLICK *
Non-VBA alternative.
Helper in D3, copied down:
=IFERROR(B3&", "&INDEX(D4:D16,MATCH(A3,A4:A16,0)),B3)
In H3, copied down:
=IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($H$2:$H2,$A$2:$A$16),0),0)),"")
In I3, copied down:
=IF(H3="","",INDEX($D$3:$D$16,MATCH(H3,$A$3:$A$16,)))
and in J3, copied down:
=IF(H3="","",SUMIF(A:A,H3,C:C))
Thank You... ALL
It's Working
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks