transposing then ranking

[voltages]

I've been figuring out how this is done. This is the scenario. Every person offers pairs of bids every hour and everyday. Now I want to transform the bids to a single column and rank them which is the lowest to the highest in rank.

I cant get the formula right, i wonder if excel can do this or not. Please help, thanks.


I have attached an excel file.

http://www.mediafire.com/?vf78d7zog356g

Voltages

 

[hiker95]

voltages,

Thanks for the workbook.


Sample raw data in worksheet Sheet1, with titles beginning in cell A5:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	DATE	HOUR	Name	QUANTITY 1	PRICE 1	QUANTITY 2	PRICE 2	QUANTITY 3	PRICE 3	QUANTITY 4	PRICE 4	QUANTITY 5	PRICE 5	QUANTITY 6	PRICE 6
6	1/26/2011	1	Alice	51	0	67.3	0
7	1/26/2011	1	Benie	0	0.01	240	0.01	420	1000.19	450	1200.19	460	2400.19
8	1/26/2011	1	Charlie	0	7000.19	6	7000.19	12	7200.19	18	8000.19
9	1/26/2011	1	Daisy	0	7500.19	6	7500.19	12	7700.09	18	7800.09
10	1/26/2011	1	Joe	25	0	35	0	40	675.19	45	700.19
11	1/26/2011	1	Kent	0	10910	32	10910	40	11910	45	12910
12	1/26/2011	1	Robbie	90	60.11	110	60.11	164	2995.11
13
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E	F
1	Date	Hour	Name	QUANTITY	Price	Rank
2	1/26/2011	1	Alice	51	0	1
3	1/26/2011	1	Alice	67.3	0	1
4	1/26/2011	1	Benie	0	0.01	5
5	1/26/2011	1	Benie	240	0.01	5
6	1/26/2011	1	Benie	420	1000.19	11
7	1/26/2011	1	Benie	450	1200.19	12
8	1/26/2011	1	Benie	460	2400.19	13
9	1/26/2011	1	Charlie	0	7000.19	15
10	1/26/2011	1	Charlie	6	7000.19	15
11	1/26/2011	1	Charlie	12	7200.19	17
12	1/26/2011	1	Charlie	18	8000.19	22
13	1/26/2011	1	Daisy	0	7500.19	18
14	1/26/2011	1	Daisy	6	7500.19	18
15	1/26/2011	1	Daisy	12	7700.09	20
16	1/26/2011	1	Daisy	18	7800.09	21
17	1/26/2011	1	Joe	25	0	1
18	1/26/2011	1	Joe	35	0	1
19	1/26/2011	1	Joe	40	675.19	9
20	1/26/2011	1	Joe	45	700.19	10
21	1/26/2011	1	Kent	0	10910	23
22	1/26/2011	1	Kent	32	10910	23
23	1/26/2011	1	Kent	40	11910	25
24	1/26/2011	1	Kent	45	12910	26
25	1/26/2011	1	Robbie	90	60.11	7
26	1/26/2011	1	Robbie	110	60.11	7
27	1/26/2011	1	Robbie	164	2995.11	14
28
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub TransposeRank()
' hiker95, 03/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=537333
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, LC As Long, NR As Long
Dim a As Long, aa As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
wR.Range("A1:F1") = [{"Date","Hour","Name","QUANTITY","Price","Rank"}]
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
For a = 6 To LR Step 1
  LC = w1.Cells(a, Columns.Count).End(xlToLeft).Column
  For aa = 4 To LC Step 2
    NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    wR.Range("A" & NR).Resize(, 3).Value = w1.Range("A" & a).Resize(, 3).Value
    wR.Range("D" & NR).Resize(, 2).Value = w1.Range(w1.Cells(a, aa), w1.Cells(a, aa + 1)).Value
  Next aa
Next a
LR = wR.Cells(Rows.Count, 1).End(xlUp).Row
wR.Range("A2:A" & LR).NumberFormat = "m/d/yyyy"
wR.Range("F2").Formula = "=RANK(E2,$E$2:$E$" & LR & ",1)"
wR.Range("F2").AutoFill Destination:=wR.Range("F2:F" & LR)
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Save you workbook, Save As, a macro enabled workbook.



Then run the TransposeRank macro.


Each time you run the TransposeRank macro, the prior data in worksheet Results will be removed.