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


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


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
3. Put, "Jan" in cells C2:C4; "Feb" in cells C5:C7;
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.

OzGrid Business Applications