Extract only unique numbers

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,</SPAN></SPAN>

In the columns B:AD there are 9 numbers divided in 3 parts I need a Formula or a VBA solution to extract the only unique numbers from the column B:AD of the each row and list them in columns AG:AX in the ascending order left to right</SPAN></SPAN>

Here is an example....</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1
2n1n2n3n4n5n6n7n8n9n1n2n3n4n5n6n7n8n9n1n2n3n4n5n6n7n8n9n1n2n3n4n5n6n7n8n9n10n11n12n13n14n15n16n17n18
384211100563401762153113240185632180123456810111517182132
4952201116745107316412335129674300123456791112162233
5100231122785621841750346230785410123456781012172334
61112420389673295061357341096520123456789112435
7122250149107043106172368052110763012345678910122536
803261251011815411028337016321187401234567810112637
Sheet3


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Put this in AG3 and copy down and across as needed:

=IF(MAX($B3:$AD3)=MAX($AF3:AF3),"",SMALL($B3:$AD3,COUNTIF($B3:$AD3,"<="&AF3)+1))
 
Upvote 0
For those of you using Excel365, the new functions make quick work of this task.

Put this in AG3 and copy downwards:

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SORT(UNIQUE(A4:AD4,1),,,1)[/FONT]
 
Upvote 0
For those of you using Excel365, the new functions make quick work of this task.

Put this in AG3 and copy downwards:

Code:
[FONT=Verdana]=SORT(UNIQUE(A4:AD4,1),,,1)[/FONT]
Hello DRSteele, I am using very old version excel 2000 so unfortunately it did not work for me. Thank you for your help</SPAN></SPAN>

Have a nice weekend
</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0
Put this in AG3 and copy down and across as needed:

=IF(MAX($B3:$AD3)=MAX($AF3:AF3),"",SMALL($B3:$AD3,COUNTIF($B3:$AD3,"<="&AF3)+1))
Eric, formula worked superb!! Thank you so much for your kind help </SPAN></SPAN>

Have a nice weekend
</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Moti :)
</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top