Vlookup for sorting

[Nick11]

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

 

[MARK858]

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?

 

[Aladin Akyurek]

@ Nick11

Row\Col	A​	B​	C​	D​	E​	F​
1​						5​
2​	apple	80​			mulberry	100​
3​	pear	25​			quince	100​
4​	orange	10​			apple	80​
5​	mulberry	100​			pear	25​
6​	quince	100​			orange	10​
7​

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

 

[Nick11]

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

 

[mole999]

The IFERROR code does not work for Excel 2003. IFERROR is not a valid Excel function... Should be IF(ISERROR...

> https://www.thespreadsheetguru.com/...ping-an-iferror-function-around-your-formulas

 

[Aladin Akyurek]

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

 

[markmzz]

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

	A	B	C	D	E	F
1	Product	Storage		Product	Storage
2	Pencil	80		Notebook	160
3	Pen	55		Ballpen	160
4	Ink	5		Pencil	80
5	Notebook	160		Pen	55
6	Ballpen	160		Ink	5
7
***	**********	*******	**	**********	*******	**

<tbody>
</tbody>

Markmzz

 

[Nick11]

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

 

[Aladin Akyurek]

Change LARGE to SMALL in the set up I provided...

Row\Col	A​	B​	C​	D​	E​	F​
1​						5​
2​	apple	80​			orange	10​
3​	pear	25​			pear	25​
4​	orange	10​			apple	80​
5​	mulberry	100​			mulberry	100​
6​	quince	100​			quince	100​
7​

<tbody>
</tbody>

In F1 enter:

=COUNTIF($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),COUNTIF($F$2:F2,F2))))

In F2 just enter and copy down:

=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

 

[markmzz]

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

	A	B	C	D	E	F
1	Product	Storage		Product	Storage
2	Pencil	80		Ink	5
3	Pen	55		Pen	55
4	Ink	5		Pencil	80
5	Notebook	160		Notebook	160
6	Ballpen	160		Ballpen	160
7
***	**********	*******	**	**********	*******	**

<tbody>
</tbody>

Markmzz