# Comparing Data

H

#### henriques

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

T

#### Tanya

Henriques,
select any cell in the range you want to sort and then go to the menu bar -
From the menu bar select 'Data' then 'Sort'

Then:
From the sort dialogue box, under 'My data range..' make a selection from

cheers
Tanya

M

#### Max

Here's one possible formulas play to arrive at the results set that you seek

Illustrated in this sample:
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,ROWS(\$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(SMALL(\$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.

H

Thanks Max

Antonio