compare between worksheets and write into cell

Hi Everyone,

I'm confronted with writing a VBA Excel Macro and I'm on beginners level So i hope I can get some insight from you on how to do this here.
This is what i want my macro to do:
I have two worksheets in one workbook (Tabelle2 and Tabelle1).

worksheet Tabelle1 contains following structure:

	A	B
1	Entity	BU Name
2		Alpha
3		Betha
4		Delta
5		Gamma

Worksheet 2 contains structured data that I have to copy from another source in Tabelle2 and this is were my macro should come into play.

Worksheet 2 looks like this:

A	B	C	D	E
1		BU_GER	BU_FR
2		Alpha	Gamma
3		Gamma	Delta
4		Delta

So the final outcome of my macro is that in worksheet 1 in column A is written either "BU_GER" or "BU_FR" or "BU_GER and BU_FR" depending whether the worksheet 2 contains the mentioned values in column C and D.
I know it sounds kinda simple but I somehow can't get it working.

My first try is the following:

Code:

---

Sub findandcopy()
This.Worksheet("Tabelle2").Activate
If Range("C2").Value = This.Worksheet("Tabelle1")  Range("B2").Copy Destination:=Range("A2")
' Is this even working?
Do Until IsEmpty(ActiveCell)
Loop
End Sub

---

I have following problems which I dont know how to solve:
1. How do I connect the cell check with an "And" condition?
2. Do I have to check every Cell? Currently I only check cell by cell.

Thanks for all your help and regards from Europe!
Oryos

welcome to vbax oryos.

you should clearly define the input and desired output. sample files may help members understand the requirement, like: "these are current tables, i want an output table like this." specify the conditions.



homework
search with these key words: "excel vba if function" or "excel vba if then else statement"
you will probably see OR and AND functions/operators within the search results as well...

hi mancubus,

thank you for your response and please excuse my late reply.
I have attached the file I'm currently working on.

The file contains two sheets. sheet 1 should contain the results in column A. The results are basically just a comparison of data on sheet 2.
As you can see on sheet 2 the data (Alpha etc) belongs to a BU (headline in Row1). So i wanna create a macro that just check whether the data (sheet1, column B) is part of BU BU_GER or BU_FR (sheet 2, column C and D). The desired outcome is that on sheet 1 on column A is the BU written that the data belongs to.

I'll have a look into your search suggestions.

Thanks a lot.
Attachment 14367

attached workbook is only a spreadsheet version of your message 1 with a difference.
in Tabelle 2 cols E and G contain values as opposed to col C and D in the first message.

the uploaded file tells me nothing.
perhaps i dont understand the requirement.
i hope one of forum members may help.

ok, the difference in cols is just coincidental. Its not that important what column it is.
But I think your reply made it easier for me to understand that i didnt get my point straight.

if you would ask me how to put my expression into an logical excel formular the following expression would be written in Tabelle 1 cell A2:
=IF B2 of Tabelle 1 is part of row E in Tabelle 2 write BU_GER into the cell.
=IF B2 of Tabelle 1 is part of row G in Tabelle 2 write BU_FR into the cell
=IF B2 of Tabelle 1 is part of row G and E in Tabelle 2 write BU_FR and BU_GER into the cell

does that make sense?

instead of B2 and A2 (Tabelle1) you should have used "Col B values" and "Col A values" respectively.

Code:

---

Sub vbax_53617_CondFillCellsIfValExistsInAnotherSheet()

    Dim i As Long
   
    With Worksheets("Tabelle1")
        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            If Application.CountIf(Worksheets("Tabelle2").Range("E:E"), .Cells(i, "B")) > 0 And _
                Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_FR and BU_GER"
            ElseIf Application.CountIf(Worksheets("Tabelle2").Range("E:E"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_GER"
            ElseIf Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_FR"
            Else
                .Cells(i, "A").Value = "Not found in Col E or G of Tabelle2"
            End If
        Next i
    End With

End Sub

---

i used Application.CountIf to check the existence of a value in a range.

Application.Match, Range.Find, Range.Autofilter methods or Loops (or some other methods?) could be used as well.

Hi mancubus,

sorry for my late response but i was able to use your script and i also added a little message box and an autofilter.
This is my final result:

Code:

---

Sub task()
Dim i As Long
With Worksheets("Tabelle1")
        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            If Application.CountIf(Worksheets("Tabelle2").Range("E:E"),  .Cells(i, "B")) > 0 And _
            Application.CountIf(Worksheets("Tabelle2").Range("G:G"),  .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_FR and BU_GER"
            ElseIf  Application.CountIf(Worksheets("Tabelle2").Range("E:E"), .Cells(i, "B"))  > 0 Then
                .Cells(i, "A").Value = "BU_GER"
            ElseIf  Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B"))  > 0 Then
                .Cells(i, "A").Value = "BU_FR"
            Else
                .Cells(i, "A").Value = "not found"
            End If
        Next i
With Tabelle1
          .AutoFilterMode = False
        .Range("A1:D1").AutoFilter
          .Range("A1:B1").AutoFilter Field:=1, Criteria1:="BU_GER"
                        MsgBox "List is created!"
End With
  End With

---

thank you very much for your support.

However I have another problem which I cant solve cause I have no idea where to begin with.
My data structure has changed and now suddenly my BU name is not written at the top of the column but two cells to the left.

so it looks like this now:

column1		column2
BU_FR		Gamma
BU_FR		Delta
BU_GER		Alpha
BU_GER		Gamma
BU_GER		Delta

Please see my file with my macro attached.

for the last two hours i played with Application.match but I cant get it working.

I wrote this:
If Application.Match(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) > 0 Then .Cells(i, "A").Value = "BU_FR and BU_GER"

but I'm missing the expression to look up two columns to the left an compare.

Can you help here?

Thank you very much!
Attachment 14662

the worksbooks in message 3 and in message 7 are the same. and i dont understand your requirement.



and pls use code tags when pasting your code here.

explained here: http://www.access-programmers.co.uk/...d.php?t=240420
same applies to vba Express...

like this:

Code:

---

Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet()
   
    Dim i As Long
   
    With Worksheets("Tabelle1")
        .AutoFilterMode = False

        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            If Application.CountIf(Worksheets("Tabelle2").Range("E:E"), .Cells(i, "B")) > 0 And _
            Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_FR and BU_GER"
            ElseIf Application.CountIf(Worksheets("Tabelle2").Range("E:E"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_GER"
            ElseIf Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) > 0 Then
                .Cells(i, "A").Value = "BU_FR"
            Else
                .Cells(i, "A").Value = "Not found in Col E or G of Tabelle2"
            End If
        Next i
   
        .Cells(1).CurrentRegion.AutoFilter Field:=1, Criteria1:="BU_GER"
    End With
       
    MsgBox "List is created!"
   
End Sub

---

thanks for the advice with the tags. I will do that in the future.
The two workbooks are different but only on the worksheet "Tabelle2"

the data sheet on the first one I uploaded looks like this:

BU_GER		BU_FR
Alpha		Gamma
Gamma		Delta
Delta

due to the change of my data structure it looks like this now:

BU_FR		Gamma
BU_FR		Delta
BU_GER		Alpha
BU_GER		Gamma
BU_GER		Delta

so in excel I would formulate following formular for column A in Tabelle1
= If values in B in Tabelle1 are existent in column G of Tabelle2 look up in column E what BU it belongs to and write it in cell at the BU name the value belongs to.
= If values in B in Tabelle1 are not existent write no BU assigned.

So in case the value is part of BU_GER and part of BU_FR write BU_GER and BU_FR.
is the value only part of BU_GER write only BU_GER in the cell.

Does that make sense?
Thanks for any insight on how to solve this

i insist on saying that both workbooks are the same!

compare the workbooks you uploaded to the file i attached to this message. :devil2:

Code:

---

Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet()
   
    Dim i As Long, j As Long
    Dim tempStr As String
   
    With Worksheets("Tabelle1")
        .AutoFilterMode = False
       
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
            If Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Cells(i, "B")) = 0 Then
                .Range("A" & i).Value = "Not found"
            Else
                tempStr = ""
                For j = 2 To Worksheets("Tabelle2").Range("G" & Rows.Count).End(xlUp).Row
                    If Range("B" & i).Value = Worksheets("Tabelle2").Range("G" & j).Value Then
                        tempStr = tempStr & " and " & Worksheets("Tabelle2").Range("E" & j).Value
                    End If
                Next j
                .Range("A" & i).Value = Mid(tempStr, 6)
            End If
        Next i
       
        .Cells(1).CurrentRegion.AutoFilter Field:=1, Criteria1:="BU_GER"
    End With
   
    MsgBox "List is created!"

End Sub

---

man mancubus I must say I really appreciate your patience with me :)

I tried your code and what happens now is that more than one BU is written in the cells in column A of Tabelle1 which is a good outcome but it is enough to just know what BU the unit belongs to and it is not necessary to kind of list more than one name of the BU.

So I guess I have to express better what I actually need and become more clear that I have the feeling that actually a combination of two operation is needed in order to achieve what I want.

So the script should compare the values in column B in Tabelle1 with column G in Tabelle2. So far so good, both scripts you put in here are doing this very well. If the value can be found the script should look up the corresponded BU in column E in Tabelle2 in the same row (since the values are linked) and write this into the cells in column A in Tabelle1. It is possible that the script finds more entries. For example might the value "Gamma" and "BU_GER" occure more than once in column G and E of Tabelle2. Here it is not important how often the script found the entries. It is only important what BU the entry belongs to. As i see it the entries can only belong to

1. GER_BU and FR_BU
2. Only GER_BU
3. Only FR_BU
4. Cant be found at all

And as the final outcome I would like these 4 possible outcomes to be displayed in Tabelle1 in column A next to the entries which subsequently I want to filter according to the BU i'm interested in.

So to express it in a more logiccal way:
1. first look up if the values in column B of Tabelle1 can be found in column G of Tabelle2. If found, look up the respective BU that the value belongs to in column E of Tabelle2 and write it into column A of Tabelle1. If one or more values are found with BU_GER write BU_GER into the cells of column A of Tabelle1. If one or more values are found with BU_GER and BU_FR write "BU_GER and BU_FR" into the cells of column A of Tabelle1. (The actual amount of entries dont matter because it might be possible that I have a lot of entries with the same BU allocation. If one or more values are found with BU_FR write BU_FR into the cells of column A of Tabelle

@oryos

you know your file and data. all we have is a workbook with 3-5 rows af sample data.

upload two workbooks.
the first one is the workbook you are working with. in this workbook provide enough number of rows to give us an idea of the data structure in the rows.
the second workbook is the workbook which the first workbook will look like as if a macro is run on it. you neeed to do this manually.

if below helps, omit my previous message.

Code:

---

Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet_v3()
   
    Dim i As Long, j As Long
    Dim tempStr As String
   
    With Worksheets("Tabelle1")
        .AutoFilterMode = False
       
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
            If Application.CountIf(Worksheets("Tabelle2").Range("G:G"), .Range("B" & i)) = 0 Then
                .Range("A" & i).Value = "Not found"
            Else
                tempStr = ""
                For j = 2 To Worksheets("Tabelle2").Range("G" & Rows.Count).End(xlUp).Row
                    If .Range("B" & i).Value = Worksheets("Tabelle2").Range("G" & j).Value And _
                        Application.CountIfs(Worksheets("Tabelle2").Range("E2:E" & j), Worksheets("Tabelle2").Range("E" & j), _
                            Worksheets("Tabelle2").Range("G2:G" & j), Worksheets("Tabelle2").Range("G" & j)) = 1 Then
                        tempStr = tempStr & " and " & Worksheets("Tabelle2").Range("E" & j).Value
                    End If
                Next j
                .Range("A" & i).Value = Mid(tempStr, 6)
            End If
        Next i
       
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="BU_GER"
    End With
   
    MsgBox "List is created!"
End Sub

---

Hi Mancubus,

I had a look into here today and tried your script with my data. I am excited how well that works! Thanks a lot. This was exactly what I needed. I'll have a look at the functions tomorrow. I haven't really figured out what this tempStr really does.

Thanks a lot for your help and effort!

Hi mancubus,

I used your code and it works like a charm. However I encountered two issues that I'd like to fix and I would very much appreciate your comments on my approach.
1. It happens that there are more values in Column C and D on Sheet1 that belong to the values in column B (kind of sub-BU's)

my approach was now to define those colums as well as variables as you did (C and D). However I dont get the desired result. Any idea whats wrong here?

Code:

---

  Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet_v4()

  Dim i As Long, j As Long
  Dim c As Long, d As Long
 
    Dim tempStr As String
   
    With Worksheets("Sheet1")
        .AutoFilterMode = False
       
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
        For c = 2 To .Range("C" & .Rows.Count).End(xlUp).Row
       
            If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("B:C" & i & c)) = 0 Then
                .Range("A" & i & c).Value = "Not found"
            Else
                tempStr = ""
                For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                    If .Range("B:C" & i & c).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                    Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                    Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Tabelle2").Range("G" & j)) = 1 Then
                        tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
                    End If
                Next j
                .Range("A" & i & c).Value = Mid(tempStr, 6)
            End If
        Next
       
       
   
    End With
   

End Sub

---

the second thing I want to achieve is to make both columns on sheet2 variable so that the script searches for the header name and then does the value comparion between the columns in sheet1 and sheet2 because the values in the columns in sheet2 might not be always in column E and G instead they might be in other columns so I thought I could bind the values to the column header.

I tried to work with the "find-function" but didnt really get it going.

please find attached my file.

I was working on my macro again today and came up with following code that is supposed to solve my problem:

Code:

---

Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet_v5()
  Dim i As Long, j As Long
  Dim c As Long, d As Long
 
    Dim tempStr As String
   
    With Worksheets("Sheet1")
        .AutoFilterMode = False
       
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
       
               
            If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("B" & i)) = 0 Then
                .Range("A" & i).Value = "Not found"
            Else
                tempStr = ""
                For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                    If .Range("B" & i).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                    Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                    Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Sheet2").Range("G" & j)) = 1 Then
                        tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
                    End If
                Next j
                .Range("A" & i).Value = Mid(tempStr, 6)
            End If
            For c = 2 To .Range("C" & .Rows.Count).End(xlUp).Row
            If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("C" & c)) = 0 Then
                .Range("A" & c).Value = "Not found"

Else
                tempStr = ""
                For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                    If .Range("C" & c).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                    Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                    Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Sheet2").Range("G" & j)) = 1 Then
                        tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
End If
Next j
End If
  Next c
Next
End With
End Sub

---

Now the macro also compares column C on sheet1 with column G on sheet2 but it doesnt take into account the result between the check of column B in sheet1. So in case a BU was found in B but not in C it states "not found" which isnt what i want since it was already found in column B.
what i want is that the macro first checks column B in sheet1 with column G in sheet2. If Bu cant be found it should go on to column C and so on.
I have the feeling I have to use Elseif somehow but cant really figure out where to implement it :banghead:

i have not used that many if stetements before.... :D

Code:

---

Sub vbax_53617_Fill_Cells_If_Val_Exists_In_Another_Sheet_v4()
   
    Dim i As Long, j As Long
    Dim tempStr As String
   
   
    With Worksheets("Sheet1")
       
        .AutoFilterMode = False
        .Range("A2:A" & .Rows.Count).ClearContents
       
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
           
            If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("B" & i)) = 0 Then
                .Range("A" & i).Value = "Not found"
            Else
                tempStr = ""
                For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                    If .Range("B" & i).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                    Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                    Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Sheet2").Range("G" & j)) = 1 Then
                        tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
                    End If
                Next j
                .Range("A" & i).Value = Mid(tempStr, 6)
            End If
               
            If Len(Trim(.Range("C" & i))) > 0 Then
                If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("C" & i)) = 0 Then
                    If .Range("A" & i).Value = "" Then .Range("A" & i).Value = "Not found"
                Else
                    tempStr = .Range("A" & i).Value
                    If tempStr = "Not found" Then tempStr = ""
                    For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                        If .Range("C" & i).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                        Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                        Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Sheet2").Range("G" & j)) = 1 Then
                            tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
                        End If
                    Next j
                    If Left(tempStr, 5) = " and " Then tempStr = Mid(tempStr, 6)
                    .Range("A" & i).Value = tempStr
                End If
            End If
       
            If Len(Trim(.Range("D" & i))) > 0 Then
                If Application.CountIf(Worksheets("Sheet2").Range("G:G"), .Range("D" & i)) = 0 Then
                    If .Range("A" & i).Value = "" Then .Range("A" & i).Value = "Not found"
                Else
                    tempStr = .Range("A" & i).Value
                    For j = 2 To Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
                        If .Range("D" & i).Value = Worksheets("Sheet2").Range("G" & j).Value And _
                        Application.CountIfs(Worksheets("Sheet2").Range("E2:E" & j), Worksheets("Sheet2").Range("E" & j), _
                        Worksheets("Sheet2").Range("G2:G" & j), Worksheets("Sheet2").Range("G" & j)) = 1 Then
                            tempStr = tempStr & " and " & Worksheets("Sheet2").Range("E" & j).Value
                        End If
                    Next j
                    If Left(tempStr, 5) = " and " Then tempStr = Mid(tempStr, 6)
                    .Range("A" & i).Value = tempStr
                End If
            End If
       
        Next i
   
    End With

End Sub

---

a lot of if statements but it works wonderful! Thanks a lot for your help mancubus! I really appreciate it.

welcome.

mark the thread as solved for future references please...