Multiple Data Copy & Paste routine in single cell

[Data_Analyst]

What I am trying to do is find a string in one worksheet and if it matches the content of another worksheet, then it copies certain texts to it. I have been able to do that, but where I am stuck is if the same string matches the string on the other worksheet then it overwrites the previous content of the cell. So my question is how do I make it possible, so that it keeps the current content and basically adds the new content next to it?

(comma denotes new column)

Example:

Worksheet 1:


Account # , Data

12345 , A
67890 , B
45678 , C
89743 , D
12345 , E



Worksheet 2:


Account # , Data

12345
67890
45678
89743


Final Result:

Worksheet 2:


Account # , Data

12345 , A E
67890 , B
45678 , C
89743 , D



As you can see the string/account # on worksheet 1 was found twice with different data, so on worksheet two it basically pasted that data next to the existing one.

I have managed to paste the data if the string matches, but when it finds a same string (12345) it just pastes the new data on top of the old one (Pastes E over the A).

Any help would be appreciated with this.

Thank you!

 

[hiker95]

Data_Analyst,

Welcome to the MrExcel forum.


Sample worksheets before the macro:

Excel Workbook
	A	B
1	Account #	Data
2	12345	A
3	67890	B
4	45678	C
5	89743	D
6	12345	E
7
Sheet1

Excel Workbook
	A	B
1	Account #	Data
2	12345
3	67890
4	45678
5	89743
6
Sheet2

After the macro:

Excel Workbook
	A	B
1	Account #	Data
2	12345	A E
3	67890	B
4	45678	C
5	89743	D
6
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Comp12()
' hiker95, 06/21/2011
' http://www.mrexcel.com/forum/showthread.php?t=558930
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then
    If w2.Range("B" & FR) = "" Then
      w2.Range("B" & FR).Value = c.Offset(, 1).Value
    Else
      w2.Range("B" & FR).Value = w2.Range("B" & FR).Value & " " & c.Offset(, 1).Value
    End If
  End If
Next c
Application.ScreenUpdating = True
End Sub

Then run the Comp12 macro.

 

[Data_Analyst]

Just Brilliant! Thank you so much, really appreciate it!

 

[hiker95]

Data_Analyst,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.

 

[Data_Analyst]

Based on the above information, here's what I am thinking...

I have 3 files.

workbook 1: has the vba residing in it
workbook 2: has the data
workbook 3: where I would like to put the data (one of each account below)

(workbook 2) data document has a bunch of accounts and corresponding info in column B, for example:

Column A

101010
111111
222222
334234

Column B

1
2
3
4


I would like the program to run through this list, so when it hits 101010,
it opens up the 3rd workbook which has that account number for the file name - "101010.xls" enters the information from column B which is associated with that account (for 101010, it would be 1), saves it and closes it, goes down the list in workbook 2 and continues until it reaches the end.

Thank you for your help with this matter. I greatly appreciate it.

 

[Data_Analyst]

Issue resolved -

Info is here...

http://www.ozgrid.com/forum/showthread.php?t=159105

Thanks for the help!