Vlookup for sorting

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
When I duplicate it, the "=iferror...." formula shown in step 5 does not work for me.
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
@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.

If you are on an older system, replace COUNTIFS with COUNTIF in the set up I suggested.
 
Upvote 0
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

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
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

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

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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