count and arrange cell numeric values

A

amrezzat

heloo
suppose i have 2 coloumns like this

2 microsoft
5 intel
3 ge
1 gm
2 microsoft
5 intel
7 tnt
12 att
24 blue
15 ntn

how can i order them AScending in another column
 
K

kanuvas

Im no expert but the way i would-

1)record macro
2)select all data
3)copy and paste into appropriate place
4)select it and under data sort ascending.
 
D

DOR

If your left column is such that the differences between different
numbers is never smaller than say N/1000, where N is the highest
numbered row in your array (10 in this case) and assuming your array is
in A1:A10, enter in B1

=INDEX($A$1:$A$10,MATCH(SMALL($A$1:$A$10+ROW($A$1:$A$10)/10000,ROW(1:1)),$A$1:$A$10+ROW($A$1:$A$10)/10000,0))

and in C1

=INDEX($B$1:$B$10,MATCH(SMALL($A$1:$A$10+ROW($A$1:$A$10)/10000,ROW(1:1)),$A$1:$A$10+ROW($A$1:$A$10)/10000,0))

Both formulas should be entered as array formulas with Ctl-Shift-Enter.
Then drag/copy down the length of your array.

The purpose is of the term involving the 10000 is to uniquely identify
rows with the same value in column A. If your numbers are potentially
closer together, you could use a much larger number. If you cannot tell
how close they could be, you may need a different approach, such as
adding a helper column, say column C containing

=RANK(A1,$A$1:$A$10,1)+COUNTIF($A$1:A1,A1)-1 in C1

then in D1 put

=INDEX($A$1:$A$10,MATCH(SMALL($C$1:$C$10,ROW(1:1)),$C$1:$C$10,0))

and in E1 put

=INDEX($B$1:$B$10,MATCH(SMALL($C$1:$C$10,ROW(1:1)),$C$1:$C$10,0))

these formulas should NOT be entered as arrays.

Drag these three formulas down to row 10.

HTH

Declan O'R
 
A

amrezzat

thanks
it works
but i dont want to repeat the numbers
i mean
if i input
1 microsoft
2 intel
1 microsoft
the results will be
1
1
2


i dont want that
i want only
1
2
no repeating
how can i do that?
noting that the input my be so long may be 1000 rows
but they are only 50 companies (from 1 to 50)
so the results will be in a max 50 row
 
A

amrezzat

rearrange cells based on its numerical values without repeating

--------------------------------------------------------------------------------

thanks
it works
but i dont want to repeat the numbers
i mean
if i input
1 microsoft
2 intel
1 microsoft
the results will be
1
1
2


i dont want that
i want only
1
2
no repeating
how can i do that?
noting that the input my be so long may be 1000 rows
but they are only 50 companies (from 1 to 50)
so the results will be in a max 50 rows

and i want that code to activate(rerun) each time i enter the sheet2

how can i do that ?
 
D

DOR

I infer from what you ask for, that you need to extract only the unique
numbers that exist in column A, and then also extract the corresponding
value from column B for each unique number in column A. I am also
inferring that there will never be two different values (names) in
column B for the same value in column A. If these assumptions are
true, then try this:

Create a new helper column, say column C (which you may later hide if
you wish), then enter the following formulas:

C1: =IF(COUNTIF($A$1:A1,A1)=1,A1,"")

drag/copy down to the end of your list.

D1: =IF(ROW(1:1)>COUNT($C$1:$C$10),"",SMALL($C$1:$C$10,ROW(1:1)))
E1: =IF(ISNUMBER(D1),INDEX($B$1:$B$10,MATCH(D1,$A$1:$A$10,0)),"")

drag/copy D1 and E1 down as far as you need to cover all unique entries
in column A (50 rows?)

The cells will update with changes as long as automatic recalculation
is on.
 
Top