I have two spread sheets. there is one common column (A). I'd like to
merge the two sheets keeping all the information from both sheets. I
dont want to cut and paste it because i dont want duplicate rows of the
common A. I hope this is clear.


--
orthomedus
------------------------------------------------------------------------
orthomedus's Profile: http://www.excelforum.com/member.php...o&userid=32641
View this thread: http://www.excelforum.com/showthread...hreadid=524481

So, if you do have duplicates in the two sheets, how do you want this
to be handled? Add the data together?

How many columns do you have in each sheet, and approx how many rows in
each sheet?

Are both sheets in one workbook?

Pete

Pete, Thanks for responding.

IF duplicates - yes add the data together.

i have approximately 7 columns and approximately 16,804 rows.

The sheets are in different workbooks. I can put them into one if it
helps.

thanks again.

Pat


--
orthomedus
------------------------------------------------------------------------
orthomedus's Profile: http://www.excelforum.com/member.php...o&userid=32641
View this thread: http://www.excelforum.com/showthread...hreadid=524481

Do you have the same number of rows in both sheets, and is there an
entry in both sheets for each unique value? Is the 16,804 rows the
total number, or the number in each sheet?

To obtain a list of unique values, you can copy column A (including
heading) from one sheet to a third sheet, and then copy the column A
values from the second sheet to just below where the first sheet's
values finish in the third sheet. Then highlight this column in the
third sheet and use Data | Filter | Advanced Filter and select Unique
Records Only and Copy to another location (specify where - I suggest
$C$1 in the third sheet). Click OK and you will have your unique list
in column C. You can then delete columns A and B.

It is probably better then to use VLOOKUP to obtain values from the
other two sheets. Assuming that they are both in the same workbook, and
that they are called Sheet1 and Sheet2, and that they have a header row
before the data, then put the same headers in row 1 of the third sheet.
In B2 you can enter this formula:

=IF(ISNA(VLOOKUP($A2,Sheet1!$A$2:$G$16804,COLUMN() ,0)),0,VLOOKUP($A2,Sheet1!$A$2:$G$16804,COLUMN(),0 ))+IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$G$16804,COLUMN (),0)),0,VLOOKUP($A2,Sheet2!$A$2:$G$16804,COLUMN() ,0))

All one formula - beware of line breaks.

Adjust the ranges to suit the data in Sheet1 and in Sheet2. The formula
can be copied across to cell G2.

I would suggest that you then copy the formula down each column in
turn, rather than down all 6 columns at the same time, as you may run
out of memory. Once you have copied it down a column, highlight all the
data in that column, click <copy followed by Edit | Paste Special |
Values (check) OK and <Esc to fix the values. When you have done this
for all 6 columns, you can delete Sheets 1 and 2 (assuming you have
them stored somewhere else).

Hope this helps.

Pete