consolidating data

[potman]

How can i make this:

JUNE 2008
Advertiser Stats
AAB 5
ARL 2
MGM 1
WPC 3
EPM 10

JULY 2008
Advertiser Stats
LFB 7
LCV 8
ARL 3
IGC 5
AAB 1
EPM 3
____________________________________________________________________
Into this:

Advertiser June Stats July Stats
XXX XXX XXX
XXX XXX XXX

Note: worksheet contains 10,000 rows, some advertisers in JUNE are not in
JULY stats & vice-versa. what i want to do is consolidate the advertisers in
columnn A without repeating anyone of them with corresponding JUNE & JULY
stats to get the variance per advertiser and i have to do it as fast as i
could to meet the deadline. can anyone help me on this, thanx.

 

[M Kan]

Agree with Tim. Stack your data so that your month and year associate with
each record, then you can run a sumproduct function like this. Also, make
your column headers just June and July, so that they match the data in the
month column, that way you can use them as the reference in the formula

sumproduct(--(range_advertiser=$a2),--(range_dates=B$1),range_stats)

 

[Joerg Mochikun]

You could add columns for Year and Month, copy values into new columns,
remove all blank lines and surplus headers. The result would look like this:
Advertiser Stats Year Month
AAB 5 2008 June
ARL 2 2008 June
MGM 1 2008 June
WPC 3 2008 June
EPM 10 2008 June
LFB 7 2008 July
LCV 8 2008 July
ARL 3 2008 July
IGC 5 2008 July
AAB 1 2008 July
EPM 3 2008 July


Now use pivot tables to do you analysis.

Cheers,

Joerg Mochikun