# How to look up string data fro two different excel sheets and copy them to the third sheet?

I have to find a subset of all the strings from the first column of two sheets. These may be present in one or both sheets. Based upon the string which is present, copy that string to the third sheet. Then copy the data in the next column from one or both sheets. Then find out the difference. Repeat the exercise for other columns for the same row. This code works if the string is present in both the sheets. How do I make it work if the string in the first column is not present in one or both? I want to include all the data from the two sheets. This is the code:

Sub Macro5()
'
' Macro5 Macro
'

'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Dim coli As Double
Dim Coli3 As Double
Dim rowy As Double

Dim numCols As Double
Dim startRow As Double
Dim lastRow As Double

startRow = 6 'assuming data starts here
Coli3 = 2 ' start the columns out on ws3

Set ws1 = ThisWorkbook.Worksheets("sheet1")
Set ws2 = ThisWorkbook.Worksheets("sheet2")
Set ws3 = ThisWorkbook.Worksheets("sheet3")

Application.ScreenUpdating = False

ws3.Cells.Clear

'ws1.Range("A1").EntireColumn.Copy Destination:=ws3.Range("A1")

'Find how many columns there are in sheet1 based on data in row 1
numCols = ws1.Cells(7, Columns.Count).End(xlToLeft).Column
For coli = 2 To numCols
'Find last Data row in the given column in sheet1
lastRow = ws1.Cells(ws1.Rows.Count, coli).End(xlUp).Row

For rowy = 6 To lastRow
'perform calculation and place in the right spot on sheet 3
If rowy = "6" Then
ws3.Cells(rowy, Coli3) = ws1.Cells(rowy, coli) & "-sheet1" ' copy sheet 1 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 1) = ws2.Cells(rowy, coli) & "-sheet2" 'copy sheet 2 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 2) = "Difference"
Else
If ws1.Cells(rowy, 1) = ws2.Cells(rowy, 1) Then
ws3.Cells(rowy, 1) = ws1.Cells(rowy, 1)
ws3.Cells(rowy, Coli3) = Format(ws1.Cells(rowy, coli).Value, "#,##0") ' copy sheet 1 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 1) = Format(ws2.Cells(rowy, coli).Value, "#,##0") 'copy sheet 2 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 2) = Format((ws1.Cells(rowy, coli).Value) - (ws2.Cells(rowy, coli).Value), "#,##0")
Else
ws3.Cells(rowy, 1) = ws1.Cells(rowy, 1)
ws3.Cells(rowy, Coli3) = Format(ws1.Cells(rowy, coli).Value, "#,##0") ' copy sheet 1 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 1).Value = 0 'copy sheet 2 to the right spot of sheet 3
ws3.Cells(rowy, Coli3 + 2) = Format((ws1.Cells(rowy, coli).Value) - (ws2.Cells(rowy, coli).Value), "#,##0")
End If
End If

Next rowy ' move to the next row on ws1, ws2, ws3

'Since we are placing 3 cols at a time in sheet 3 we increment differently
Coli3 = Coli3 + 3 '1 becomes 4, 4 becomes 7, 7 becomes 10 and so on

Next coli 'move to next column on ws1, ws2

End Sub
Capture.PNG
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older than dirtCommented:
Please attach a workbook that I can test with.
Author Commented:
Older than dirtCommented:
This code works if the string is present in both the sheets. How do I make it work if the string in the first column is not present in one or both? I want to include all the data from the two sheets.
A few questions:
1) How can a string in the first column no be present in both sheets?
2) Can a string be on Sheet1 but not on Sheet2?
3) Can a string be on Sheet2 but not on Sheet3?
4) Most importantly, please give me an example of what is not working.
Author Commented:
Hi Martin - My two input sheets are in Sheet1 and Sheet2. Sheet 3 is the desired result. If you look at the sheets, abc0 from column 1 is not present in sheet1 and abc2 is not present in sheet2. But sheet3 shows both of them: abc0 with 0 values from sheet1 and abc2 with 0 values from sheet2. I am not able to generate the output as shown in sheet3.
Older than dirtCommented:
OK, I think I have it. A few things to note:
• I changed several of your variables from Double to Long because Double is a floating point number with decimals, and when comparing them it is well-documented that values that are apparently equal visually are not treated as equal when compared by VBA
• All the code I changed is annotated with a 'New comment
• I added code at the end to look for data that's in Sheet2 but not in Sheet1. Note that I eliminated the "For coli = 2 To numCols" loop. You can do the same for the existing code if you like.
• I made copies of your original sheets as backup and created new ones that only have the data from your picture plus one more row to make sure my code works.
29076215.xlsm

Experts Exchange Solution brought to you by