• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Arrange in order

S

sachar

Member
Dear All,

With reference to the attachment, is it possible to arrange the data of column A & B in proper order by applying the excel formula?
 

Attachments

  • STATUS_INVENTORY_ARRANGE.xlsx
    16.4 KB · Views: 2
Hi sachar,
Not clear to me, can you be more specific ?
What do you mean by arrange data in proper order?

Regards,
 
Dear Khalid,

To more clear about, in this attachment, I arranged manually from Row no. 27., that I want to do with excel formula. Can be possible?
 

Attachments

  • STATUS_INVENTORY_ARRANGEMENT.xlsx
    17 KB · Views: 2
Thanks for the clarification,
Try this in A27:
=SMALL($A$2:$A$23,ROW(A1))

And this in B27:
=VLOOKUP(A27,$A$2:$B$23,2)

Copy down both.

Regards,
 
or this in A27:
=INDEX($A$2:$B$23,MATCH(SMALL($A$2:$A$23,ROW(A1)),$A$2:$A$23,0),COLUMN(A1))

copy down and right.
 
Dear Khalid,

Many… thanks for answering, but, when, applied the VLOOKUP formula the result come in, figure (-2) not like the Condition Formatting ICON, as a condition set. Can be possible that also?
 
sachar said:
Dear Khalid,

Many… thanks for answering, but, when, applied the VLOOKUP formula the result come in, figure (-2) not like the Condition Formatting ICON, as a condition set. Can be possible that also?
Click to expand...

I think:
1) Typing formula in any cell does not remove CF Rules, else you copy / paste.
2) May be you have applied formula on some other range.
3) Copy formula, Press F2 on B27 and Paste.

Now copy down.
 
Khalid NGO said:
Thanks for the clarification,
Try this in A27:
=SMALL($A$2:$A$23,ROW(A1))

And this in B27:
=VLOOKUP(A27,$A$2:$B$23,2)

Copy down both.

Regards,
Click to expand...
Dear Khalid,

I am Forwarding my previous mail with the same problem but, in column. May I know the mistake in column formula?
 

Attachments

  • STATUS_INVENTORY_ARRANGEMENT_IN COLUMN.xlsx
    17.3 KB · Views: 2
Hi Sachar -

Just change the bold and underlined part of your formula i.e. SMALL($F$1:$W$1,COLUMNS(F$1)) to SMALL($F$1:$W$1,COLUMN(A$1))

then drag it...you will get the result..

Usage of Columns Function: If you use columns function in a scenario like yours..you need to select the range and not just the column...Columns(F$1) is giving 6 i.e. the 6th column..so your function is pulling up the 6th Smallest value in the range F1 : W1. But that is not what you are aiming at...

Had your formula been something like...SMALL($F$1:$W$1,COLUMNS($A$1:A$1))..it would yield you the right result...so what does this do..

COLUMNS($A$1:A$1) results into 1 as it counts all the columns between the range $A$1:A$1 and that is only 1 column..

Now your formula evaluates to SMALL($F$1:$W$1,COLUMNS($A$1:A$1)) results into SMALL($F$1:$W$1,1) = 8 and gives the first smallest number in the range..

Now once you drag it to the next column the formula looks like: =SMALL($F$1:$W$1,COLUMNS($A$1:B$1)) where COLUMNS($A$1:B$1) results into 2 as there are 2 columns in this range..the formula will look SMALL($F$1:$W$1,2) = 12..
So on and so forth..

Usage of Column Function: As per the solution suggested: i.e. SMALL($F$1:$W$1,COLUMN(A$1))

The above solution evalutes to SMALL($F$1:$W$1,1), where column(A$1) results into 1
So the formula looks up for the first smallest value in the range $F$1:$W$1 and give 8

Now if you drag the formula towards right, the formula looks like
SMALL($F$1:$W$1,COLUMN(B$1)), where Column(B$1) results into 2..

And finally formula looks like SMALL($F$1:$W$1,2) and pulls up the second smallest number within the range..
 
Asheesh said:
Hi Sachar -

Just change the bold and underlined part of your formula i.e. SMALL($F$1:$W$1,COLUMNS(F$1)) to SMALL($F$1:$W$1,COLUMN(A$1))

then drag it...you will get the result..

Usage of Columns Function: If you use columns function in a scenario like yours..you need to select the range and not just the column...Columns(F$1) is giving 6 i.e. the 6th column..so your function is pulling up the 6th Smallest value in the range F1 : W1. But that is not what you are aiming at...

Had your formula been something like...SMALL($F$1:$W$1,COLUMNS($A$1:A$1))..it would yield you the right result...so what does this do..

COLUMNS($A$1:A$1) results into 1 as it counts all the columns between the range $A$1:A$1 and that is only 1 column..

Now your formula evaluates to SMALL($F$1:$W$1,COLUMNS($A$1:A$1)) results into SMALL($F$1:$W$1,1) = 8 and gives the first smallest number in the range..

Now once you drag it to the next column the formula looks like: =SMALL($F$1:$W$1,COLUMNS($A$1:B$1)) where COLUMNS($A$1:B$1) results into 2 as there are 2 columns in this range..the formula will look SMALL($F$1:$W$1,2) = 12..
So on and so forth..

Usage of Column Function: As per the solution suggested: i.e. SMALL($F$1:$W$1,COLUMN(A$1))

The above solution evalutes to SMALL($F$1:$W$1,1), where column(A$1) results into 1
So the formula looks up for the first smallest value in the range $F$1:$W$1 and give 8

Now if you drag the formula towards right, the formula looks like
SMALL($F$1:$W$1,COLUMN(B$1)), where Column(B$1) results into 2..

And finally formula looks like SMALL($F$1:$W$1,2) and pulls up the second smallest number within the range..
Click to expand...
Asheesh said:
Hi Sachar -

Just change the bold and underlined part of your formula i.e. SMALL($F$1:$W$1,COLUMNS(F$1)) to SMALL($F$1:$W$1,COLUMN(A$1))

then drag it...you will get the result..

Usage of Columns Function: If you use columns function in a scenario like yours..you need to select the range and not just the column...Columns(F$1) is giving 6 i.e. the 6th column..so your function is pulling up the 6th Smallest value in the range F1 : W1. But that is not what you are aiming at...

Had your formula been something like...SMALL($F$1:$W$1,COLUMNS($A$1:A$1))..it would yield you the right result...so what does this do..

COLUMNS($A$1:A$1) results into 1 as it counts all the columns between the range $A$1:A$1 and that is only 1 column..

Now your formula evaluates to SMALL($F$1:$W$1,COLUMNS($A$1:A$1)) results into SMALL($F$1:$W$1,1) = 8 and gives the first smallest number in the range..

Now once you drag it to the next column the formula looks like: =SMALL($F$1:$W$1,COLUMNS($A$1:B$1)) where COLUMNS($A$1:B$1) results into 2 as there are 2 columns in this range..the formula will look SMALL($F$1:$W$1,2) = 12..
So on and so forth..

Usage of Column Function: As per the solution suggested: i.e. SMALL($F$1:$W$1,COLUMN(A$1))

The above solution evalutes to SMALL($F$1:$W$1,1), where column(A$1) results into 1
So the formula looks up for the first smallest value in the range $F$1:$W$1 and give 8

Now if you drag the formula towards right, the formula looks like
SMALL($F$1:$W$1,COLUMN(B$1)), where Column(B$1) results into 2..

And finally formula looks like SMALL($F$1:$W$1,2) and pulls up the second smallest number within the range..
Click to expand...
 
You must log in or register to reply here.
Back
Top