VBA coding needed badly

[Seeking help]

Hey there,


1) I am currently working on a project which required me to create a macro that allow me to join up two excel worksheet containing a whole lot of information based on a unique key(userID,8 digit long) and is present in both sheets and paste it on a new sheet.


2) How do you delete the information in a column?

Example like, in a column i want to delete numbers starting with "0000XXXX" but retain numbers starting with "1234XXXX".

[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" wrote:

>
> 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!
>
>
> --
> Seeking help
> ------------------------------------------------------------------------
> Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
> View this thread: http://www.excelforum.com/showthread...hreadid=554398
>
>

[Seeking help]

THat right JLatham!!

A shorter version of example is like that

[Mr-Excel]

Seeking help wrote:
> 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
>
>
> --
> Seeking help
> ------------------------------------------------------------------------
> Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
> View this thread: http://www.excelforum.com/showthread...hreadid=554398

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" wrote:

>
> ya,there is a common column named Numbers in that example..it is
> something like a user ID.
>
>
> --
> Seeking help
> ------------------------------------------------------------------------
> Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
> View this thread: http://www.excelforum.com/showthread...hreadid=554398
>
>

[Seeking help]

Thanks alot Jlatham!!This code is great..i 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 editing of codes??
And one more thing,they are able to paste duplicate keys right??cause i just realise that in the excel sheets,one key might contains many different information.

God bless
With gratitude,
Seeking help

[QUOTE=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...

[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" wrote:

>
> 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 Wrote:
> > 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
> ------------------------------------------------------------------------
> Seeking help's Profile: http://www.excelforum.com/member.php...o&userid=35658
> View this thread: http://www.excelforum.com/showthread...hreadid=554398
>
>

[Seeking help]

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