On Mon, 17 Oct 2005 04:51:03 -0700, Nadeem
wrote:

I am using Excel 2003.
I have a sheet showing the Item No., Brand, Description, sales, cost, stock
of a branch. The sheet contains 2000 rows and 6 columns. I have similar
sheets from 4 other branches, but the rows do not match. I am looking for a
method to consolidate the data so that similar rows (i.e. rows starting with
the same item no.) come in one row, so that I may add up the sales and stock
figures of all branches for a given item no.

Can someone help???


One way would be to use the Advanced Filter Copy to another location.
Make sure all the four sheets have the same column field headings -
presumably Item "No", "Brand", "Description" etc. albeit that they are
in different columns

Decide on the order you want the columns to appear and copy the same 6
field heading below the last record on every sheet. Now use Advance
Filter on all the data on each sheet in turn, and Filter copy the
records to the row of 6 field headings.

Now that you have a copy of all the data on the 4 sheets in the same
column order, you can simply copy the filtered data records onto a 5th
sheet.

If this is a regular occurence you'll probably want to write this into
a VBA macro.

Better still have all the branches use the same layout, but I guess
you know that :-)

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________