VBA coding needed badly

[Seeking help]

Hey there,

I am currently working on a project which required me to do a project
using macro. I have to create a macro which enables me to join up two
excel file based on a unique key and paste it on a new sheet.The unique
key that is used sometimes only appeared in sheet 1 but not sheet 2 or
vice versa but i have to compile all the information together and also
i have to choose some of the column that should be added on.Thanks!

 

[mudraker]

If the unique key is only on 1 sheet how do you match it with data on
the other sheet?

You need to supply more details

 

[JLatham]

Clarify a littl please. I think what you mean is that if a unique key value
doesn't appear in one file but does in another file, then you need to add
that to the first list so that one of the files ends up containing a complete
list of all unique keys?

Example Workbook 1 contains these keys
1
2
3
7

and Workbook 2 contains
1
3
4
5
7

you want one of the workbooks to end up with a list like this?
1
2
3
4
5
7
without duplicate 'key' entries.

Is this what you're looking for?

 

[Seeking help]

THat right JLatham!!

A shorter version of example is like that

Sheet 1 might contain

NUMBERS ANS REGION
12345678 yes Asia
12345679 no Europe

Sheet 2 contain
NUMBERS PETS
12345678 dog
12345679 cat

So i got to displayed a result like this in my results sheet

NUMBERS ANS REGION PETS
12345678 yes Asia dog
12345679 no Europe
cat

 

[Mr-Excel]

THat right JLatham!!

A shorter version of example is like that

Sheet 1 might contain

NUMBERS ANS REGION
12345678 yes Asia
12345679 no Europe

Sheet 2 contain
NUMBERS PETS
12345678 dog
12345679 cat

So i got to displayed a result like this in my results sheet

NUMBERS ANS REGION PETS
12345678 yes Asia dog
12345679 no Europe
cat

will there be any common columns in both the sheets....?

 

[Seeking help]

ya,there is a common column named Numbers in that example..it is
something like a user ID.

 

[JLatham]

See if this doesn't help some.
http://www.jlathamsite.com/uploads/for_SeekingHelp.zip
should be fast even on dialup, only 19KB.

It's a .zip file with 2 .xls file in it. One has the code. Both have to be
open for it all to work. Simulates your situation.

Let me know how I did in the class - or if I was late getting a chair...

 

[Seeking help]

Thanks alot Jlatham!!This code is greati hope it is able to copy the ten
thousands rows of info i have to do.I have a question on some
coding..will hope to receive your reply soon.

Dim MoveInfo(1 To 2, 1 To 2) As String
MoveInfo(1, 1) = "B" 'from column B in other workbook...
MoveInfo(1, 2) = "D" '...to column D in this workbook
MoveInfo(2, 1) = "C" ' from column C in other workbook...
MoveInfo(2, 2) = "E" ' ...to column E in this workbook

I have changed it into
Dim MoveInfo(1 To 11, 1 To 11) As String
MoveInfo(1, 1) = "B"
MoveInfo(1, 2) = "K"
MoveInfo(2, 1) = "H"
MoveInfo(2, 2) = "L"
MoveInfo(3, 1) = "I"
MoveInfo(3, 2) = "M"
MoveInfo(4, 1) = "J"
MoveInfo(4, 2) = "N"
MoveInfo(5, 1) = "K"
MoveInfo(5, 2) = "O"
MoveInfo(6, 1) = "L"
MoveInfo(6, 2) = "P"
MoveInfo(7, 1) = "M"
MoveInfo(7, 2) = "Q"
MoveInfo(8, 1) = "N"
MoveInfo(8, 2) = "R"
MoveInfo(9, 1) = "O"
MoveInfo(9, 2) = "S"
MoveInfo(10, 1) = "P"
MoveInfo(10, 2) = "T"
MoveInfo(11, 1) = "Q"
MoveInfo(11, 2) = "U"
*Basically i need to copy total info of 11 columns.
They are from column B, H-Q in 2nd sheet to K-U in 1st sheet.Am i right
in the fill in??

With gratitude,
Seeking help

 

[JLatham]

Sorry for the wait.

Actually It'll work as you have it written, but you didn't have to dimension
the array as you did: Dime MoveInfo(1 to 11, 1 to 2) would have been
sufficient.

 

[Seeking help]

Thanks alot for that help..it was alright to wait and thanks once more
for the hard coding.