Many thanks for the tip, Richard. But the method that you've described would
give me five rows for each item (or am I missing something?) I want my
master-sheet to have "Item No" & "Brand" followed by the sales figures of all
the branches in a single row in front of each item. I would then be able to
add a column at the end showing the sum of sales all branches.

Could you help me with a macro for this? Thanks in advance.

"Richard Buttrey" wrote:

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
__________________________