MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I consolidate columns?


Posted by Francine Maceikis on February 26, 2001 1:20 PM

I am tryimg to consolidate January through December and all data(a4:a13-m4:m13)into one column. Then auto filter it so i can just pull up jan,feb,mar, etc. in one column and all the data. Do i need to set this up through a pivot table? help me!!


Posted by Mark W. on February 26, 2001 2:11 PM

Francine, let's see if I understand your problem!
Suppose that you have a list of employees and their
sales by month in cells A1:D4 ...

{"Employee","Jan","Feb","Mar"
;"Linda",1500,2500,1000
;"Larry",1600,1625,1650
;"Louis",1400,1395,2600}

I presume that you're trying to make it look like...

{"Employee","Sales","Month"
;"Linda",1500,"Jan"
;"Larry",1600,"Jan"
;"Louis",1400,"Jan"
;"Linda",2500,"Feb"
;"Larry",1625,"Feb"
;"Louis",1395,"Feb"
;"Linda",1000,"Mar"
;"Larry",1650,"Mar"
;"Louis",2600,"Mar"}

There are a couple of ways to accomplish this, but
perhaps the easiest would be to:

1. Copy the Feb and Mar sales values in turn and
paste them beneath the Jan sales values.
2. Next, select the employee names; press and hold
the Ctrl key, and drag the fill handle (the little
black square in the lower right hand corner of the
selection) until each sales value has a corresponding
name.
3. Put, "Jan" in cells C2:C4; "Feb" in cells C5:C7;
etc.
4. Enter, "Sales" into cell B1 and "Month" into C1
and you're done.

There is an more advanced approach using the TRANSPOSE()
function, but it's not for the faint of heart. :-)

Posted by David Hawley on February 26, 2001 5:07 PM

Hi Francine

In case Marks great help is off the mark (excuse
the pun) try this.

In the cell under your heading that you want to Consolidate your data. put:
=A4 & "-" & M4

Then copy down.

Dave
OzGrid Business Applications