consolidating data
 

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.

consolidating data
 

Quickest way would be to add a column to the data so it was organized
as follows:

MONTH | ADVERTISER | STATS

the use a pivot table to summarize by advertiser.



On Jul 29, 8:56*am, potman wrote:

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.

consolidating data
 

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)

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"potman" wrote:

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.

consolidating data
 

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


"potman" wrote in message
...
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.