Another interp on your post (think your requirements are quite complex)
Here's one possible formulas play to arrive at the results set that you seek
Illustrated in this sample:
http://www.freefilehosting.net/download/3ggca
Merge n extract uniques n corresp max value.xls
Source data assumed in cols A to D, data from row2 down
In E2: =IF(A2="","",ROWS($1:1))
In F2: =IF(C2="","",ROWS($1:1))
In G2:
=IF(ROWS($1:1)COUNT($E:$E),IF(ROWS($1:1)-MAX($E:$E)COUNT($F:$F),"",INDEX(C:C,SMALL($F:$F,R OWS($1:1)-MAX($E:$E))+1)),INDEX(A:A,SMALL($E:$E,ROWS($1:1))+ 1))
Copy G2 to H2
In I2:
=IF(G2="","",IF(COUNTIF(G$2:G2,G2)1,"",RIGHT(G2)+ ROW()/10^10))
In J2:
=IF(ROWS($1:1)COUNT($I:$I),"",INDEX(G:G,MATCH(SMA LL($I:$I,ROWS($1:1)),$I:$I,0)))
In K2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(J2="","",MAX(IF(G$2:G$100=J2,H$2:H$100)))
(Ranges G$2:G$100, H$2:H$100 are arbitrary. Adapt/extend to suit)
Select E2:K2, copy down as far as required. Cols J n K returns the required
results, ie a uniques listing of the combined codes in cols A and C, with
codes sorted in ascending order by their single number char in col J, with
the corresponding maximum values for the codes in col K. Minimize/hide away
cols E to I.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"henriques" wrote:
I have data for 2 different months.
I need a formula or macro that creates a column with all data included in
both months sorted by column A.
See example
A B C D E F
Jan Feb. All
code value code value code value
a1 3 a1 5 a1 5
a2 5 a3 12 a2 5
a3 9 a4 6 a3 12
a5 2 a7 2 a4 6
a6 3 a8 10 a5 2
a8 6 a6 3
a7 2
a8 10
How to manage this
Thanks a lot
António