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
 
Ad

Advertisements

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
with/without header row.

cheers
Tanya
 
M

Max

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,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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top