Vlookup for sorting

N

Nick11

New Member
Joined
Apr 22, 2018
Messages
5
https://www.extendoffice.com/documents/excel/895-excel-sort-dynamic-data.html#a

I've been trying to accomplish something in Excel for about a week. After many Google searches, I was led to the link shown above. Although the type of data shown in this link is different, it is essentially showing exactly what I'm trying to accomplish.
This article seems to be written in slightly broken english, and I'm not sure I'm following it correctly. When I duplicate it, the "=iferror...." formula shown in step 5 does not work for me. Can anyone provide assistance, please?

What I'm trying to accomplish:
1. A small table in columns A,B, and C where I can enter data.
2. The individual rows of that table will be copied and sorted into rows E,F, and G, based on the value shown in column C.
3. The ability to sort must be able to accommodate duplicate numbers in column C. In other words, per last screenshot in the link above, columns E, F, and G should be able to handle duplicated numbers in the following fashion:
E.........F..............G
1.....Pen............200
2.....Ballpen.......200
3.....Pencil..........78
4.....Notebook.....55
5.....Ink...............5

Thanks for your time
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
When I duplicate it, the "=iferror...." formula shown in step 5 does not work for me.
Click to expand...
Explain what you mean by "does not work for me", does it error? if not what does it return incorrectly?
What version of Excel are you using?
 
Upvote 0
@ Nick11

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
5​
[/td][/tr]
[tr][td]
2​
[/td][td]apple[/td][td]
80​
[/td][td][/td][td][/td][td]mulberry[/td][td]
100​
[/td][/tr]
[tr][td]
3​
[/td][td]pear[/td][td]
25​
[/td][td][/td][td][/td][td]quince[/td][td]
100​
[/td][/tr]
[tr][td]
4​
[/td][td]orange[/td][td]
10​
[/td][td][/td][td][/td][td]apple[/td][td]
80​
[/td][/tr]
[tr][td]
5​
[/td][td]mulberry[/td][td]
100​
[/td][td][/td][td][/td][td]pear[/td][td]
25​
[/td][/tr]
[tr][td]
6​
[/td][td]quince[/td][td]
100​
[/td][td][/td][td][/td][td]orange[/td][td]
10​
[/td][/tr]
[tr][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F1 just enter:

=COUNTIFS($A$2:$A$6,"?*")

In E2 control+shift+enter, not just enter, and copy down:

=IF($F2="","",INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$F2,ROW($A$2:$A$6)-ROW($A$2)+1),COUNTIFS($F$2:F2,F2))))

In F2 just enter and copy down:

=IF(ROWS($F$2:F2)>$F$1,"",LARGE($B$2:$B$6,ROWS($F$2:F2)))

See the workbook which implements this set up: https://www.dropbox.com/s/3dvva6dib61hynk/Nick11%20descending%20sort.xlsx?dl=0
 
Upvote 0
@Mark: Excel 2000. This is on an old sacrificial laptop that was pulled out of a chest just for this project. Let me know if the outdated version is an issue.
With the "iferror...", I get "#name?" in the top cell of column F. The remainder of F and G remain empty

@Aladin: I searched for an answer on this forum prior to posting my question last evening. (I despise lazy people who don't do at least a little leg work on their own.) I saw a thread that had your example in it. I tried that and it didn't work. However, I do believe I see where I made an error transposing the formulas over into my application. I'll try it again later today to see what happens. I'll reply to this thread with my results.

Thank you guys very much for your time!! Have a great day.
 
Upvote 0
Nick11 said:
@Mark: Excel 2000. This is on an old sacrificial laptop that was pulled out of a chest just for this project. Let me know if the outdated version is an issue.
With the "iferror...", I get "#name?" in the top cell of column F. The remainder of F and G remain empty

@Aladin: I searched for an answer on this forum prior to posting my question last evening. (I despise lazy people who don't do at least a little leg work on their own.) I saw a thread that had your example in it. I tried that and it didn't work. However, I do believe I see where I made an error transposing the formulas over into my application. I'll try it again later today to see what happens. I'll reply to this thread with my results.

Thank you guys very much for your time!! Have a great day.
Click to expand...

If you are on an older system, replace COUNTIFS with COUNTIF in the set up I suggested.
 
Upvote 0
Nick11 said:
https://www.extendoffice.com/documents/excel/895-excel-sort-dynamic-data.html#a

I've been trying to accomplish something in Excel for about a week. After many Google searches, I was led to the link shown above. Although the type of data shown in this link is different, it is essentially showing exactly what I'm trying to accomplish.
This article seems to be written in slightly broken english, and I'm not sure I'm following it correctly. When I duplicate it, the "=iferror...." formula shown in step 5 does not work for me. Can anyone provide assistance, please?

What I'm trying to accomplish:
1. A small table in columns A,B, and C where I can enter data.
2. The individual rows of that table will be copied and sorted into rows E,F, and G, based on the value shown in column C.
3. The ability to sort must be able to accommodate duplicate numbers in column C. In other words, per last screenshot in the link above, columns E, F, and G should be able to handle duplicated numbers in the following fashion:
E.........F..............G
1.....Pen............200
2.....Ballpen.......200
3.....Pencil..........78
4.....Notebook.....55
5.....Ink...............5

Thanks for your time
Click to expand...

Hi!

I tested the formulas below in Excel 2003. No problem here.

In E2 and copy down

=IF(ISERROR(LARGE(B$2:B$6,ROWS(E$2:E2))),"",LARGE(B$2:B$6,ROWS(E$2:E2)))<strike></strike>

In D2 and copy down

=IF(E2="","",INDEX(A$2:A$6,MATCH(1,INDEX(COUNTIF(D$1:D1,A$2:A$6)+(B$2:B$6>=E2),),0)))


[TABLE="class: grid, width: 336"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Storage[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Storage[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Notebook[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ballpen[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Notebook[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Ballpen[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
Mark, that got me on the right track! Thanks so much!
One last thing:
I would like to display columns D and E in ascending order. I played around and was able to get column E to display in Ascending order, but I couldn't get D to follow suit. Can you please give the proper formula for these columns to display?
Thanks so much!
You guys have a great day!
Nick
 
Upvote 0
Change LARGE to SMALL in the set up I provided...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]apple[/TD]
[TD]
80​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]orange[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]pear[/TD]
[TD]
25​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]pear[/TD]
[TD]
25​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]orange[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]apple[/TD]
[TD]
80​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]mulberry[/TD]
[TD]
100​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]mulberry[/TD]
[TD]
100​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]quince[/TD]
[TD]
100​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]quince[/TD]
[TD]
100​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In F1 enter:
Rich (BB code): 
=COUNTIF($A$2:$A$6,"?*")

In E2 control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF($F2="","",INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$F2,ROW($A$2:$A$6)-ROW($A$2)+1),COUNTIF($F$2:F2,F2))))

In F2 just enter and copy down:
Rich (BB code): 
=IF(ROWS($F$2:F2)>$F$1,"",SMALL($B$2:$B$6,ROWS($F$2:F2)))

See: https://www.dropbox.com/s/63wnwu9x4i47ecm/Nick11 ascending sort.xlsx?dl=0
 
Upvote 0
Nick11 said:
Mark, that got me on the right track! Thanks so much!
One last thing:
I would like to display columns D and E in ascending order. I played around and was able to get column E to display in Ascending order, but I couldn't get D to follow suit. Can you please give the proper formula for these columns to display?
Thanks so much!
You guys have a great day!
Nick
Click to expand...

Hi Nick!

First, you are welcome and thanks for the feedback.

Second, for ascending order, try this small modification (in red) in my suggestion:

In E2 and copy down

=IF(ISERROR(SMALL(B$2:B$6,ROWS(E$2:E2))),"",SMALL(B$2:B$6,ROWS(E$2:E2)))

In D2 and copy down

=IF(E2="","",INDEX(A$2:A$6,MATCH(1,INDEX(COUNTIF(D$1:D1,A$2:A$6)+(B$2:B$6<=E2),),0)))


[TABLE="class: grid, width: 336"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Storage[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Storage[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pen[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Ink[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pencil[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Notebook[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Notebook[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Ballpen[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ballpen[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
580
Rnkhch
R
H
  • Solved
Vlookup: unexplainable - for me - #N/A
Replies
3
Views
79
Hans Troost
H
H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
172
Peter_SSs
Peter_SSs
tezza
  • Solved
Dynamically extend list help
Replies
3
Views
91
Cubist
Cubist
S
  • Solved
How to work out general trend in a series of numbers (film rankings)
Replies
5
Views
468
stefanaalten
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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