Sort numbers in ascending order and the concatenate

**sandubandu** · 01-15-2016, 06:13 AM

This is what I have in my two columns:-

Column A Column B
Medicine ID Medicine weight
1326| 323| 1442| 199 500mg|2mg|60mg|30mg
1321| 320| 2447| 1442 125mg|2mg|60mg|30mg
100| 323| 1362| 152 500mg|2mg|10mg|30mg

This is what I want:-

Medicine ID|Medicine weight
199 30mg|323 2mg|1326 500mg|1442 60mg
320 2mg|1321 125mg|1442 30mg|2447 60mg
100 500mg|152 30mg|323 2mg |2447 60mg

Column A contains Medicine ID
Column B contains Medicine weight
I want to Sort Medicine ID in ascending order and concatenate with its appropriate weight.

Thanks in advance !

**hemesh** · 01-15-2016, 06:51 AM

You need to use VBA for this thing

it is possible using formulas too but will take a lot of time.

**hemesh** · 01-15-2016, 07:03 AM

Copy paste below in D1 then hold control and shift then hit enter to make it array formula.
This formula will work for 4 medicine ID's and 4 Weight

Formula:

=INDEX(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20))&" "&TRIM(MID(SUBSTITUTE("|"&B3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(B3)-LEN(SUBSTITUTE(B3,"|","")))+1))*20,20)),MATCH(TRUE,SMALL(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),1)=--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),0))&"|"&INDEX(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20))&" "&TRIM(MID(SUBSTITUTE("|"&B3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(B3)-LEN(SUBSTITUTE(B3,"|","")))+1))*20,20)),MATCH(TRUE,SMALL(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),2)=--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),0))&"|"&INDEX(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20))&" "&TRIM(MID(SUBSTITUTE("|"&B3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(B3)-LEN(SUBSTITUTE(B3,"|","")))+1))*20,20)),MATCH(TRUE,SMALL(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),3)=--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),0))&"|"&INDEX(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20))&" "&TRIM(MID(SUBSTITUTE("|"&B3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(B3)-LEN(SUBSTITUTE(B3,"|","")))+1))*20,20)),MATCH(TRUE,SMALL(--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),4)=--(MID(SUBSTITUTE("|"&A3,"|",REPT(" ",20)),ROW(INDIRECT("1:"&(LEN(A3)-LEN(SUBSTITUTE(A3,"|","")))+1))*20,20)),0))

**sandubandu** · 01-15-2016, 08:22 AM

Thanks Its working... but in my data number of medicine IDs varies from 1 to 15 and have same number of Medicine Weights. How to use this formula for such cases ???

Example Data:-

Medicine ID Medicine weight
1326| 323| 1442| 500mg|2mg|60mg
1321| 320| 2447| 1442 125mg|2mg|60mg|30mg
100 500mg

**hemesh** · 01-15-2016, 08:24 AM

as far as i know then you need to use VBA for that case.

**Glenn Kennedy** · 01-15-2016, 08:25 AM

You should have been more clear in your original post. Hemesh will have spent a lot of time putting together his formula. You will need VBA to do this; not a formula. A formula solution is very unlikely to be practical.

**sandubandu** · 01-16-2016, 01:12 AM

I'm sorry for not being very specific with my issue. I hope I will get help.

can I get Different different formulas for different number of Medicine IDs ?

i.e
1. Formula for 2 Mdicine IDs
2. Formula for 3 Mdicine IDs
3. Formula for 4 Mdicine IDs
4. Formula for 5 Mdicine IDs..... upto 15 Medicine IDs

**jindon** · 01-16-2016, 01:43 AM

UDF
Use in cell like
=ConcSort(A3,B3,"|")
and filldown

Function ConcSort(txt1 As String, txt2 As String, delim As String) As String
    Dim x, y, i As Long, ii As Long
    With CreateObject("System.Collections.SortedList")
        x = Split(txt1, delim): y = Split(txt2, delim)
        For i = 0 To UBound(x)
            If Not .contains(Val(x(i))) Then
                Set .Item(Val(x(i))) = CreateObject("System.Collections.SortedList")
            End If
            .Item(Val(x(i)))(Val(y(i))) = y(i)
        Next
        For i = 0 To .Count - 1
            For ii = 0 To .GetByIndex(i).Count - 1
                ConcSort = ConcSort & IIf(ConcSort <> "", delim, "") & _
                    .GetKey(i) & " " & .GetByIndex(i).GetByIndex(ii)
            Next
        Next
    End With

**sandubandu** · 01-16-2016, 01:47 AM

could you please tell me How to use this code ?

**jindon** · 01-16-2016, 01:52 AM

................................

**sandubandu** · 01-16-2016, 03:11 AM

Thank you !

Sort numbers in ascending order and the concatenate

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Re: Sort numbers in ascending order and the concatenate

Thread Information

Users Browsing this Thread

Similar Threads

Sort the final digits and sort in ascending order

[SOLVED] Sort in ascending order

[SOLVED] Sort Ascending order

[SOLVED] Sort in ascending order

How to put grouped data in ascending order in excel and then concatenate

Sort the numbers in ascending order by lines

sort ascending order

Bookmarks

Bookmarks

Posting Permissions