Sort numbers in ascending order and the concatenate

[sandubandu]

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]

You need to use VBA for this thing

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

[hemesh]

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

Please Login or Register  to view this content.

[sandubandu]

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]

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

[Glenn Kennedy]

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]

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]

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

Please Login or Register  to view this content.

[sandubandu]

could you please tell me How to use this code ?

[jindon]

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

[sandubandu]

Thank you !