Originally Posted by
FullMoonMadness
Hello,
I am trying to extract some data and place it in another part of the workbook, and sort it. Example:
Column A has a title of "Cost" (A1), with the values being (A2:A6): $0, $0, $50.50, $0, $46
Column B has a title of "Unique Number" (B1), with values of (B2:B6): 1, 2, 3, 4, 5
Column C has a title of "Date" (C1), with the following values (C2:C6): 4/21, 5/11, 5/27, 6/4, 7/22
I want to extract data from all three cells into a new table, but only the cells where the cost is greater than zero. And I want to the new table to be sorted from lowest cost to greatest cost. The final table should look like:
Column L: $46, $50.50
Column M: 5, 3
Column N: 7/22, 5/27
The key is I don't want to use VBA. Can anyone help? Thanks in advance!
A:C houses the data, L:N the processing...
Cost 
Unique Number 
Date 

Cost 
Unique Number 
Date 
0 
1 
4/21 

46 
5 
7/22 
0 
2 
5/21 

46 
7 
7/23 
50.5 
3 
5/27 

50 
6 
7/23 
0 
4 
6/4 

50.5 
3 
5/27 
46 
5 
7/22 




50 
6 
7/23 




46 
7 
7/23 




L2, control+shift+enter, not just enter, and copy down:
Code:
=IFERROR(SMALL(IF($A$2:$A$8,$A$2:$A$8),ROWS($L$2:L2)),"")
M2, control+shift+enter and copy down:
Code:
=IF(L2="","",INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=L2,
ROW($A$2:$A$8)ROW($A$2)+1),COUNTIF($L$2:L2,L2))))
N2, just enter and copy down:
Code:
=IF(M2="","",INDEX($C$2:$C$8,MATCH(M2,$B$2:$B$8,0)))
Like this thread? Share it with others