Search and return new values???

[erwin_m29]

I have multiple given values in Sheet 1, range a1:a200.
1st day i have to update Sheet 2, range a1:a200, and find ouf if this values matches the values
on Sheet 1, range a1:a200, if match found, i will write Y in day 1, if not i will write N.
day 2, i will again update Sheet 2, range a1:a200, and find out if this values matches from sheet 1, range a1:a200,
if match found, i will write y on day 2 column, if not, will write N... so on and so fort...

Sheet 1

Data1	day1	day2	day3
AAAAAA	Y
AAABBB	N
AAACCC	Y
AAADDD	Y

<tbody>
</tbody>

Sheet 2

AAAAAAA

AAACCC

AAADDD

<tbody>
</tbody>

Any suggestion how to solve this problem, in just a click?

 

[hohlick]

I suggest you can use this formula on Sheet2: =IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$200,0)),"N","Y") where A1 is the first cell on Sheet2 with updated values, and then fill it down to cell A200. After that, you need to replace formulas in A1:A200 on Sheet2 with values. Next day you should repeat this
Hope it helps

 

[Rick Rothstein]

Assuming you update cells A1:A200 only once a day, then you can run this macro immediately after doing the daily update...

Sub Update()
  Dim LastRow As Long, NextDay As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NextDay = Cells(2, Columns.Count).End(xlToLeft).Column + 1
  With Cells(2, NextDay).Resize(LastRow - 1)
    .Formula = "=IF(COUNTIF(Sheet2!$A$1:$A$200,$A2),""Y"",""N"")"
    .Value = .Value
  End With
End Sub

 

[erwin_m29]

thank yall for the quick response.

for rick, your solution works perfectly, thank you.
i have another conditions to add.
after writing Y, it will format the cell with fill color GREEN.
while if it is N, the cell will be colored RED.
how would u add this to your macro?

 

[Rick Rothstein]

i have another conditions to add.
after writing Y, it will format the cell with fill color GREEN.
while if it is N, the cell will be colored RED.
how would u add this to your macro?

Give this modified version of the code I posted earlier a try...

Sub Update()
  Dim LastRow As Long, NextDay As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NextDay = Cells(2, Columns.Count).End(xlToLeft).Column + 1
  With Cells(2, NextDay).Resize(LastRow - 1)
    .Formula = "=IF(COUNTIF(Sheet2!$A$1:$A$200,$A2),""Y"",""N"")"
    .Value = .Value
    .Interior.ColorIndex = 3
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 4
    .Replace "Y", "", xlWhole, ReplaceFormat:=True
    Application.ReplaceFormat.Clear
  End With
End Sub

 

[erwin_m29]

you are so awesome mr rick.
im trying to understand the command lines, what if i modify my sheet1 (eg i added headings, so i move my data to fr A1:A200 to A3:A203 (A1 to A2 rows will be assign for table heading).
which command lines manages this condition?

 

[Rick Rothstein]

you are so awesome mr rick.
im trying to understand the command lines, what if i modify my sheet1 (eg i added headings, so i move my data to fr A1:A200 to A3:A203 (A1 to A2 rows will be assign for table heading).
which command lines manages this condition?

I had actually assumed a single row setting with the data starting on Row 2. The following changes should handle your new data start cell (I highlighted the changes I made from my last posted code in red so you can see what needed to be changed)...

Sub Update()[COLOR=#FF0000][/COLOR]
  Dim LastRow As Long, NextDay As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NextDay = Cells([COLOR=#FF0000][B]3[/B][/COLOR], Columns.Count).End(xlToLeft).Column + 1
  With Cells([COLOR=#FF0000][B]3[/B][/COLOR], NextDay).Resize(LastRow - [COLOR=#FF0000][B]2[/B][/COLOR])
    .Formula = "=IF(COUNTIF(Sheet2!$A$1:$A$200,$A2),""Y"",""N"")"
    .Value = .Value
    .Interior.ColorIndex = 3
    Application.ReplaceFormat.Clear
    Application.ReplaceFormat.Interior.ColorIndex = 4
    .Replace "Y", "", xlWhole, ReplaceFormat:=True
    Application.ReplaceFormat.Clear
  End With
End Sub

 

[erwin_m29]

thank you mr rick. now i understand the lines.
#definitelylearndsomethingnew.