Concatenate in different way based on criteria

Hi,
I have data in Column A and Column B
Column A Contains category like A,A,A,B,B,B,A,A,A,B,B,B,A,A,A
And Column B Contains Items 1,2,3,1,3,4,4,5,7,5,6,7,8,9,11 (it will not be in ascending or descending order)

Now I want to result in Column D and Column E
D3="A"
D4="B"
E3="1-5,7-9,11"
E4 ="1,3-7"

Originally Posted by Marc L

Hi,

the easy way is E3 like 1,2,3,4,5,7, …

thanks for your reply
but I want to show it in a smart way

You can try this VBA CODE

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

This function can be called from any cell in a spreadsheet.

@saravnepal
thanks for your response,
It is giving correct result the Item Column (Column B) was in ascending orders only
and also if the number is not continuous like 6, 8 , 11 etc it is giving result 6-6,8-8,11-11 instead of 6,8,11
any idea

see the attached file

The following code (which figures out the category names on its own) seems longer to me than I envision it should be in my mind's eye; however, it does seem to do what you asked for...

Please Login or Register  to view this content.

In Message #7, I gave you a macro that figures the Category names in use by itself and output them all along with their Item numbers. However, I see saravnepal gave you a function (UDF) which, while it didn't do what you wanted, seemed to be the kind of solution you were after (a function rather than a macro). Given that, here is the function (UDF) that I came up with which takes the Category name as its argument (it assumes your data is in Columns A and B which I can modify if you want to physically specify the range) and produces the output you are after...

Please Login or Register  to view this content.

Thank you Jindon and Rick Rothstein
Both are working fine.