Merge 2 Excel Worksheets

[Mondou]

I have 2 Excel worksheets that I am trying to merge but can't figure
out how to do it with Excel 2002.

File A (PriceList.xls) has the following fields:
ITEM, DESCRIPTION, COST

File B (InfoList.xls) has the following fields:
ITEM, DESCRIPTION, WEIGHT, DIMENSIONS, UPC, DISCONTINUED( Discontiued
items have an ''X'' value)

I need to merge these 2 files to form 1 single file with updated
information keeping in mind that:

- ITEM Field: Some ITEMs from file A may not be in file B and
vice-versa.

- DESCRIPTION Field: Data from both Files may be slightly different in
which case, File B's DESCRIPTION overides File A's DESCRIPTION.

Is there a simple way to do this ?

thanks

 

[SLAnt]

Personally, I would edit both spreadsheets so that they have identically
placed fields (columns), even if on one prrice list there is no info for
some fields.
Then I would add an end column called file. For file a enter A for all
entries and for file B enter B for all entries.

Copy all data from both sheets into a new sheet with one identical
header row.

add a new column called count and enter the following formula into the
first cell below
=Countif(A:A,A2), where A2 is the corresponding ITEM.
copy this down to all entries.
then filter on the column for any entries with count >1. These are your
duplicates.
Hopefully there aren't too many and you cannot delete ones you don't
need given you have the File th eentry came from in the preceding
column.

somethinglikeant

 

[Mondou]

Thanks for your response.

Problem is, there are alot of duplicates... about 900. I am trying to
join 2 files into 1 Masterfile that contains different information for
the same item numbers. There are only a few new items.

I am getting these new files on a regular basis so I need a way to
merge them together regularly.

I've heard of the following ways:

Mail Merge (don't think that this is useful since I need to have them
in Excel not Word).

Microsoft Query (Can't figure out how to use it)

VCOUNT: ( ???)

VBA : Newbie, but willing to try if this is the only option

Which one of these option is best and how could I do this ?

Thanks

 

[Otto Moehrbach]

Mondou
VBA is the only way you're going to be able to do this. You didn't say
how you want the columns arranged in the final product. Since B has more
columns than A, I would copy the A data to the B data rather than vice
versa. So where does the COST column go in the B data?
Also you start off by saying you have 2 worksheets. But later you say
you have "File A" and "File B". What do you have, two sheets in one file or
two files? If you have 2 files, how many sheets of pertinent data do you
have in each file? Post back with the answers and I'll work up something
for you. HTH Otto

 

[JB]

http://cjoint.com/?fhiNdcKk3i

Sub Merge()
If Sheets("PriceList").[A1].End(xlDown) <> "zzz" Then
Sheets("PriceList").[A1].End(xlDown).Offset(1, 0) = "zzz"
If Sheets("InfoList").[A1].End(xlDown) <> "zzz" Then
Sheets("InfoList").[A1].End(xlDown).Offset(1, 0) = "zzz"
i = 2: j = 2: k = 2
Do While Not (Sheets("PriceList").Cells(i, 1) = "zzz" And
Sheets("InfoList").Cells(j, 1) = "zzz")
If Sheets("PriceList").Cells(i, 1) < Sheets("InfoList").Cells(j, 1)
Then
Sheets("result").Cells(k, 1) = Sheets("priceList").Cells(i, 1)
Sheets("result").Cells(k, 2) = Sheets("priceList").Cells(i, 2)
Sheets("result").Cells(k, 3) = Sheets("priceList").Cells(i, 3)
i = i + 1: k = k + 1
Else
If Sheets("PriceList").Cells(i, 1) > Sheets("InfoList").Cells(j,
1) Then
Sheets("result").Cells(k, 1) = Sheets("InfoList").Cells(j, 1)
Sheets("result").Cells(k, 4) = Sheets("InfoList").Cells(j, 3)
Sheets("result").Cells(k, 5) = Sheets("InfoList").Cells(j, 4)
Sheets("result").Cells(k, 6) = Sheets("InfoList").Cells(j, 5)
Sheets("result").Cells(k, 7) = Sheets("InfoList").Cells(j, 6)
j = j + 1: k = k + 1
Else
Sheets("Result").Cells(k, 1) = Sheets("InfoList").Cells(j, 1)
Sheets("Result").Cells(k, 2) = Sheets("InfoList").Cells(j, 2)
Sheets("result").Cells(k, 3) = Sheets("priceList").Cells(i, 3)
Sheets("result").Cells(k, 4) = Sheets("InfoList").Cells(j, 3)
Sheets("result").Cells(k, 5) = Sheets("InfoList").Cells(j, 4)
Sheets("result").Cells(k, 6) = Sheets("InfoList").Cells(j, 5)
Sheets("result").Cells(k, 7) = Sheets("InfoList").Cells(j, 6)
i = i + 1: j = j + 1: k = k + 1
End If
End If
Loop
End Sub

Cordialy JB