would like to combine 2 or more duplicate rows as shown in the attachment.
thanks
would like to combine 2 or more duplicate rows as shown in the attachment.
thanks
b12=SUMIF($A$3:$A$7,$A12,B$3:B$7)
or
b12=IFERROR(INDEX(B$3:B$7,MATCH(1,INDEX(($A$3:$A$7=$A12)*(B$3:B$7>0),0),)),"")
try and copy across
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
It works fine, thanks.
How this can be achieved if text comes in place of numbers in the respective cells. For example if 5A comes in place of 5. Just to combine the rows, no need to sum.
it works fine, thanks
One more request.
In the table given above, If cell B3 has 5A and B4 has 15B, then how cell B12 can get 5A15B?
If there is a way to conditionally concatenate within one spreadsheet formula I am unaware of it.
I am reluctant to say that it can't be done.....but I strongly suspect it.
There is a User Defined Function (VBA) that shows up on the Forum from time to time. It is written by
tigeravatar.
This is the code. Copy and paste into a module in the VBA editor.
When I used it like this:'tigeravatar ExcelForum Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String 'Created by TigerAvatar at www.excelforum.com, September 2012 'Purpose is to concatenate many strings into a single string 'Can be used with arrays, range objects, and collections Dim DataIndex As Variant 'Used to loop through arrays, range objects, and collections Dim strResult As String 'Used to build the result string 'Test if varData is an Array, Range, or Collection If IsArray(varData) _ Or TypeOf varData Is Range _ Or TypeOf varData Is Collection Then 'Found to be an, array, range object, or collection 'Loop through each item in varData For Each DataIndex In varData 'Check if the item isn't empty If Len(DataIndex) > 0 Then 'Found the item isn't empty, check if user specified bUnique as True If bUnique = True Then 'bUnique is true, check if the item has been included in the result yet If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then 'Item has not been included in the result, add item to the result strResult = strResult & "||" & DataIndex End If Else 'bUnique is not true, add item to the result strResult = strResult & "||" & DataIndex End If End If Next DataIndex 'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter strResult = Replace(Mid(strResult, 3), "||", sDelimiter) Else 'Found not to be an array, range object, or collection 'Simply set the result = varData strResult = varData End If 'Output result ConcatAll = strResult End Function
it worked like you described.Formula:=ConcatAll(IF($A12=$A$3:$A$7,B$3:B$7&"",""),"")
This must be array entered.....committed by pressing and holding Ctrl + Shift while hitting Enter. Fill down and across.
Note that I appended "" to the data range B$3:B$7&""
If you don't all the blanks will introduce leading zeros.
Last edited by FlameRetired; 02-25-2015 at 03:44 PM. Reason: corrections to closing statement
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks