formula to auto arrange the markings

[felixpanganiban]

friends,

please help me for the formula to auto arrange the markings :

Column A Column B

WWC001 WWC001
WWC005 WWC002
WWC003 WWC003
WWC002 WWC004
WWC004 WWC005


column B should be formula to arrange the column A.

[AlKey]

Try this array formula

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Data Range

	A	B
1	WWC001	WWC001
2	WWC005	WWC002
3	WWC003	WWC003
4	WWC002	WWC004
5	WWC004	WWC005

or regular version

Please Login or Register  to view this content.

[Tony Valko]

Try this...

Data Range

	A	B
1	Data	Sorted
2	WWC001	WWC001
3	WWC005	WWC002
4	WWC003	WWC003
5	WWC002	WWC004
6	WWC004	WWC005

This array formula** entered in B2 and copied down:

=INDEX(A$2:A$6,MATCH(SMALL(COUNTIF(A$2:A$6,"<"&A$2:A$6),ROWS(B$2:B2)),COUNTIF(A$2:A$6,"<"&A$2:A$6),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[bebo021999]

If all cells have unique first 3-characters, then rank the next 3 digits:

="WWC"&TEXT(SMALL(INDEX(--RIGHT($A$1:$A$5,3),),ROW(1:1)),"000")

[Tony Valko]

Try this array formula

=INDEX($A$1:$A$5,MATCH(ROWS(A$1:A1),COUNTIF($A$1:$A$5,"<="&$A$1:$A$5),0))

Just be aware that that formula will fail if there are duplicate entries:

Data Range

	A	B
1	Data	Sorted
2	WWC001	WWC001
3	WWC003	WWC002
4	WWC003	#N/A
5	WWC002	WWC003
6	WWC004	WWC004

[felixpanganiban]

thank you. its great...

[felixpanganiban]

Yes, how about if there is subletter A,B,C etc...

WWC001
WWC001A
WWC001B

something like that. its not function..

[bebo021999]

Yes, how about if there is subletter A,B,C etc...

WWC001
WWC001A
WWC001B

something like that. its not function..

Are they full representative ? Is there others, i.e WWC001AZ, or WWC001-A?

[felixpanganiban]

only like this WWC001a, WWC002ab.,,,,,

[bebo021999]

With original entries in A1:A5, type below formula in B1 then drag down:

Please Login or Register  to view this content.

[felixpanganiban]

friends,

also the attached file. pls help.

[felixpanganiban]

sir,

the above formula is not working

[AlKey]

If you have duplicates use formula suggested by Tony Valco in post#3

[bebo021999]

www.Withdraw.com by bebo021999
with latest sample file not also I could not find any sub-letter, but I could find other prior strings like "CCF","BR"...

[Tony Valko]

also the attached file. pls help.

This array formula** entered in G8:

=IFERROR(INDEX(B$8:B$23,MATCH(SMALL(IF(D$8:D$23>=G$5,IF(D$8:D$23<=G$6,COUNTIF(B$8:B$23,"<"&B$8:B$23))),ROWS(G$8:G8)),IF(D$8:D$23>=G$5,IF(D$8:D$23<=G$6,COUNTIF(B$8:B$23,"<"&B$8:B$23))),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Here's your file with this formula implemented...

[felixpanganiban]

thank you very very much mr tony valko. its great!!!!!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.