Columns into 2 way tables

[Christian]

Hi, I'm stuck. I need to tabulate and sort data in 3 columns. The Input data
looks as follows:
A B C
1 Risk Name Sales
2 Mid Joe 24000
3 High Jack 54000
4 Low Bill 65300
5 Low Mary 76000
6 High Bess 120000
7 Low Cathy 234000
8 Mid John 136000

The Output needs to be a table with 3 columns: Low, Mid and High and show
the corresponding name in a descending order of sales. Like this:

A B C
1 Low Mid High
2 Cathy John Bess
3 Mary Joe Jack
4 Bill
5

The function needs to be completely automated and foolproof (ie no manual
filters, sorting..) as it's tool that will be used by teams of sales people
with various level of Excel skill.
Many Thanks

PS there is a fixed number of customers (ie 40 rows)

 

[Dave]

Hi Christian,
One way, a bit involved, but works ok:
Output table in colums F, G, H
In F1, enter: Low
In G1, enter: Mid
In H1, enter: High
In F2, enter: =VLOOKUP(F$1,$A$2:$B$8,2,0)
Fill across to H1
In F3, enter:
=VLOOKUP(F$1,INDIRECT("A"&2+(MATCH(F2,$B$2:$B$9,0))&":$C$10"),2,0)
Fill across to H3
Select F3:H3
Fill down until all columns are displaying #N/A
You can trap for the #N/A with an IF statement if you like.

Regards - Dave.

 

[Billy Liddel]

Christian

Perhaps someone will give you a formula solution but if you do not mind
using macros will do the job. It assumes the List starrt in A1 and copies the
data to columns E:F. The range is dynamic so it will not matter if you change
the number of rows in the future.

Option Base 1
Sub listRisk()
'copy list A2:C? to Range E1:G?
Dim i As Integer, lr As Integer, row As Integer, j As Integer
Dim c, risk
risk = Array("Low", "Mid", "High")
'clear old data
Range("E1").CurrentRegion.ClearContents
lr = Range("a1").CurrentRegion.Rows.Count
Range("E1:G1") = risk
k = 1
For j = 5 To 7
For i = 2 To lr
row = Application.CountA(Columns(j)) + 1
If Cells(i, 1) = risk(k) Then
Cells(row, j) = Cells(i, 2)
Else
'do nothing
End If
Next i
k = k + 1
Next j

End Sub

Press ALT + F11 to open the VB Editor, Insert, Module and paste the code.
Close the module and return to the sheet with the list. Press ALT + F8,
select the macro and click Run.

Regards
Peter Atherton

 

[Teethless mama]

In E1: Low
In F1: Mid
In G1: High

In E2:
=IF(ISERR(MATCH(LARGE(IF(Risk=E$1,Sales),ROWS($1:1)),Sales,0)),"",INDEX(Name,MATCH(LARGE(IF(Risk=E$1,Sales),ROWS($1:1)),Sales,0)))

ctrl+shift+enter, not just enter
copy across and down

 

[T. Valko]

The function needs to be completely ... foolproof

While your formula does work on the posted sample data it will fail if there
are duplicate sales amounts for a category. Only the OP would know if that's
a possibility.

You can reduce the error trap to:

=IF(ISERR(LARGE(IF(Risk=E$1,Sales),ROWS($1:1))),"",

 

[Christian]

Dave,
the formula works in sorting out the people in the right colums. However
within a given column, the people still need to be sorted in descending sales
value.

Any ideas on this?

Regards Christian

 

[Christian]

Peter,
nice idea and again the sorting into columns work fine. However within a
given column, the people still need to be sorted in descending sales value.

Any ideas on this?

Regards Christian

 

[Christian]

Hi
I'm not used in workibg with array. I followed the instructions (including
the ctrl + alt + enter) but the cells remain empty. Are there any lists or
ranges that need to be named beforehand?

thanks Christian

 

[Dave]

Hi Christian,
"... including the ctrl + alt + enter)
Should be: ctrl+shift+enter
Dave.

 

[Christian]

I fixed the array formula but I get the following output. The sorting
according to the sales works, but not the risk level

Risk Name Sales Low Mid High
Mid Joe 24000 Cathy Cathy Cathy
High Jack 54000 John John John
Low Bill 65300 Bess Bess Bess
Low Mary 76000 Mary Mary Mary
High Bess 120000 Bill Bill Bill
Low Cathy 234000 Jack Jack Jack
Mid John 136000 Joe Joe Joe

the array formula usrd is :
=IF(ISERR(MATCH(LARGE(IF(Risk=E$1;Sales);ROWS($1:1));Sales;0));"";INDEX(name;MATCH(LARGE(IF(Risk=E$1;Sales);ROWS($1:1));Sales;0)))

Note: that in Belgium the "," separator is a ";" for whatever strange reason

Thanks for helping out

Regards Christian

 

[Christian]

Hi Dave
Thanks. I got the array wortking - the 3 ranges needed to be defined. But
the sorting according the risk level doesn't work. I posted the results in
the response from Teethless Mama. Any ideas?

regards Christian

 

[Dave]

Hi Christian,
I tried TM's formula, and it works for me.

You have to name ranges:
A2:A10 = Risk
B2:B10 = Name
C2:C10 = Sales

Then enter:
Low into E1
Mid into F1
High into G1

Then TM's formula in E2
Fill across, then down.

Any luck?
Dave.

 

[Christian]

Hi Dave,
it worked!!
I had included the first row in the ranges.
Many thanks,
Enjoy a virtual belgian beer on my behalf
regards Christian

 

[Billy Liddel]

Hi

You have probably deleted this reference by now but for interest I finally
created a solution using a formula and macro. The formula was in response to
TVAlco's post regarding duplicate values in Sales. I know it is highly
improbably, but still.

To find the Nominal Rank, nominal because it depends on who occurs first in
the list, add this formula:=
=SUMPRODUCT(--(risk=A2),--(Sales>=C2))-COUNTIF($C$2:C2,C2)+1

The macro uses this formula to decide where to place the Name.

Sub RiskByRank()
Dim i As Integer, lr As Integer, r As Integer, j As Integer
Dim c, risk, rnk As Integer, col As Integer
Dim rng As Range
risk = Array("Low", "Mid", "High")
'clear old data
Range("F1").CurrentRegion.ClearContents
lr = Range("a1").CurrentRegion.Rows.Count
Range("F1:H1") = risk
Set rng = Range(Cells(2, 1), Cells(lr, 1))
col = 6
For i = LBound(risk) To UBound(risk)
r = 1
For Each c In rng
rnk = c.Offset(0, 3)
If c = risk(i) Then
Cells(r + rnk, col) = c.Offset(0, 1)
End If
Next c
col = col + 1
Next i

End Sub

Regards
Peter Atherton