Combining two spreadsheets.
 

Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.

Combining two spreadsheets.
 

Data / Pivot Table / Multiple Consolidation Ranges
--
Gator


"Gameware" wrote:


Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.

Combining two spreadsheets.
 

There is a different way.

1-Go into a new blank sheet
2-Copy and Paste the column that contains every item in the new sheet
3-Click in first cell of column where data will next go.
4-Goto Data / Consolidate / Function - Sum / Reference - drag and highlight
the column starting with the heading to the last cell with data (or click on
first cell and scroll down and hold shift and click last cell) / Add / check
- Use labels - Top row
5-do steps 3 and 4 for other columns in other sheets.

--
Gator


"Gator" wrote:

Data / Pivot Table / Multiple Consolidation Ranges
--
Gator


"Gameware" wrote:


Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.

Combining two spreadsheets.
 

sorry, there is a flaw in the previous reply
--
Gator


"Gator" wrote:

There is a different way.

1-Go into a new blank sheet
2-Copy and Paste the column that contains every item in the new sheet
3-Click in first cell of column where data will next go.
4-Goto Data / Consolidate / Function - Sum / Reference - drag and highlight
the column starting with the heading to the last cell with data (or click on
first cell and scroll down and hold shift and click last cell) / Add / check
- Use labels - Top row
5-do steps 3 and 4 for other columns in other sheets.

--
Gator


"Gator" wrote:

Data / Pivot Table / Multiple Consolidation Ranges
--
Gator


"Gameware" wrote:


Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.

Combining two spreadsheets.
 

instead what you would do is step 4 (below) and include all the columns of
each sheet, starting with the sheet that includes all items.
--
Gator


"Gator" wrote:

sorry, there is a flaw in the previous reply
--
Gator


"Gator" wrote:

There is a different way.

1-Go into a new blank sheet
2-Copy and Paste the column that contains every item in the new sheet
3-Click in first cell of column where data will next go.
4-Goto Data / Consolidate / Function - Sum / Reference - drag and highlight
the column starting with the heading to the last cell with data (or click on
first cell and scroll down and hold shift and click last cell) / Add / check
- Use labels - Top row
5-do steps 3 and 4 for other columns in other sheets.

--
Gator


"Gator" wrote:

Data / Pivot Table / Multiple Consolidation Ranges
--
Gator


"Gameware" wrote:


Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.

Combining two spreadsheets.
 

Is this for 03 or 07? We have 07. Thanks.

"Gator" wrote:

instead what you would do is step 4 (below) and include all the columns of
each sheet, starting with the sheet that includes all items.
--
Gator


"Gator" wrote:

sorry, there is a flaw in the previous reply
--
Gator


"Gator" wrote:

There is a different way.

1-Go into a new blank sheet
2-Copy and Paste the column that contains every item in the new sheet
3-Click in first cell of column where data will next go.
4-Goto Data / Consolidate / Function - Sum / Reference - drag and highlight
the column starting with the heading to the last cell with data (or click on
first cell and scroll down and hold shift and click last cell) / Add / check
- Use labels - Top row
5-do steps 3 and 4 for other columns in other sheets.

--
Gator


"Gator" wrote:

Data / Pivot Table / Multiple Consolidation Ranges
--
Gator


"Gameware" wrote:


Spread sheet 1 shows entire inventory data base and includes:

Item Description # column (ie 0001)
Item Description column (ie Widget)
Quantity on-hand column (ie 12)

Spread sheet 2 shows only inventory items that have selling activity.

Item Description # column (0001)
Item Description column (Widget)
Quantity sold column. (6)

How can I combine these two spreadsheets so I can get the following data?:

Item # Description Qty on hand Qty sold
0001 Widget 12 6

Spreadsheet 1 shows all 10,000 inventory items while spreadsheet 2 shows
only those inventory items that have sold. (Only a small fraction of the
10,000 inventory items.) What I really need is a final spreadsheet that
shows only inventory items that have sold that includes the information
above. Thanks.