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

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
__________________________

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
__________________________

Nadeem,

You're correct in that my suggestion would give you as many rows per
product as you have branches. What I should have added though is that
you should add an extra column to each of the sheets, in which you
would hold a code for each branch. i.e. all the records for the same
branch would have the same code.

I'd suggest you persevere with the single sheet database, now with an
additional column showing each records branch. It will make all sorts
of reporting far easier. Now by using a pivot table you can easily see
the sum of all the sales for each branch, or the sum of each product or
each product by branch, or whatever.

However if you want a single row with all the branches alongside each
other in consecutive columns, if you can set down a simple example of
the layout you expect I'll look at a suitable macro. You've thrown me a
bit when you say "sales figures of all the branches in a single row in
front of each item.". I'm not quite clear what you mean by 'in front of
each item' hence a sample layout would paint a better picture.

Rgds

Nadeem wrote:
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
__________________________