Sorting table data using formulas

**dandavis1** · 03-25-2010, 12:29 PM

See Attached Doc.

I have been set a task which i am halfway through but cannot figure out how to complete the task, please help.

I am struggling with the smaller table on Sheet1. As you can see i need to show the top 4 complaints in each seperate month using the data above.

Do i need to use some sort of formula for this, the bottom table should read as follows becuase these are the top complaints in the month of january:

A22 Replacement Item
A23 Lead times/booking process
A24 Lack/freedom of choice
A25 Personal Items missing

Cell A20 is a drop down so when you change the month to feb the table should change to the feb data.

Any help will be greatly appreciated!!!!

**NBVC** · 03-25-2010, 01:08 PM

Try this

Add a helper column,

In E22: =LARGE(INDEX($B$2:$M$16,0,MATCH($A$20,$B$1:$M$1,0)),ROWS($A$1:A1)) copied down 4 cells. You can make these invisible by matching font colour to background colour.

Then in A22:

=INDEX($A$2:$A$16,SMALL(IF($B$2:$B$16=E22,ROW($B$2:$B$16)-ROW($B$2)+1),COUNTIF(E$22:E22,E22)))

confirmed with CTRL+SHIFT+ENTER and copied down.

**dandavis1** · 03-25-2010, 05:28 PM

Thanks very much for your help!!! In Cell A20 when i change the month to Feb the data which i have put in E22,E23,E24,E25 changes to the correct February data as above, but in the cells A22,A23,A24,A25 the data does not change and is showing January still.

Any more suggestions? Thanks again!!!

**NBVC** · 03-26-2010, 08:03 AM

Change formula in A22 to:

Please Login or Register  to view this content.

and confirm with CSE key combo, then copy down.

**Awon** · 08-10-2011, 01:38 AM

Hello There

I come to know you about Sorting Table Data.

I have a url which can help you.

Click Here : http://www.raiseitsolutions.com/foru...php?f=27&t=151

I hope it is enough for you.

Thank You

AWON