PDA

View Full Version : [SOLVED] compare between worksheets and write into cell



Oryos
08-30-2015, 06:10 AM
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:


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

mancubus
08-30-2015, 11:14 PM
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...

Oryos
09-11-2015, 07:14 AM
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.
14367

mancubus
09-15-2015, 04:45 AM
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.

Oryos
09-15-2015, 10:33 AM
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?

mancubus
09-16-2015, 12:05 AM
instead of B2 and A2 (Tabelle1) you should have used "Col B values" and "Col A values" respectively.



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.

Oryos
10-27-2015, 09:28 AM
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:


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!
14662

mancubus
10-27-2015, 11:41 PM
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/forums/showthread.php?t=240420
same applies to vba Express...

like this:


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

Oryos
10-28-2015, 01:19 PM
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

mancubus
10-28-2015, 03:10 PM
i insist on saying that both workbooks are the same!

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



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

Oryos
10-29-2015, 09:40 AM
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


GER_BU and FR_BU
Only GER_BU
Only FR_BU
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

mancubus
10-29-2015, 11:49 PM
@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.

mancubus
10-30-2015, 04:28 AM
if below helps, omit my previous message.



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

Oryos
11-10-2015, 01:40 PM
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!

Oryos
11-13-2015, 02:05 PM
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?


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.

Oryos
11-15-2015, 02:15 PM
I was working on my macro again today and came up with following code that is supposed to solve my problem:

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:

mancubus
11-16-2015, 12:48 AM
i have not used that many if stetements before.... :D




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

Oryos
11-22-2015, 08:05 AM
a lot of if statements but it works wonderful! Thanks a lot for your help mancubus! I really appreciate it.

mancubus
11-23-2015, 12:45 AM
welcome.

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