Consolidate an XLS sheet

[smaruzzi]

Hi,

I would like to consolidate a new sheet retrieving data from two separate
tabs having exactly the same logical organization. Number of rows is known,
and varies among the two tabs.

Conceptually it is a simple sort of iterative loop, not sure how to
implement it though.

Thanks, Stefano

 

[Pete_UK]

With the two sheets in one workbook, you can insert a new worksheet
and copy the field which uniquely defines a record to the new sheet
from both sheets (one under the other). Then you can obtain a unique
list of these using Advanced filter. Then using a VLOOKUP function you
can bring data from one sheet, or the other, or both (eg descriptions
and other text need only be brought once, but a record may not be
present in both sheets, whereas numbers from both sheets may need to
be added or subtracted).

Sorry the answer is generalised - give further details of your data
layout if you require a more specific solution.

Hope this helps.

Pete

 

[smaruzzi]

Pete,

I was looking for something which might automate the process something like
extracting X rows from one sheet, Y rows from another, and then collate them
(X + Y) in a new worksheet.

Any idea?

Thanks, Stefano

 

[Pete_UK]

Ron d Bruin has code here:

http://www.rondebruin.nl/copy2.htm

for merging two or more worksheets. I'm sure you can find a suitable
macro there for what you want to do.

Hope this helps.

Pete