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
ReedsAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
Please attach a workbook that I can test with.
0
ReedsAuthor Commented:
0
Martin LissOlder 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.
0
ReedsAuthor 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.
0
Martin LissOlder 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.