# Search and return new values???

#### erwin_m29

##### New Member
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

<tbody>
</tbody>

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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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

Assuming you update cells A1:A200 only once a day, then you can run this macro immediately after doing the daily update...
Code:
``````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``````

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.

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.

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

Code:
``````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``````

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?

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)...

Code:
``````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``````

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

Replies
3
Views
235
Replies
6
Views
611
Replies
1
Views
475
Replies
10
Views
509
Replies
2
Views
178

1,221,424
Messages
6,159,824
Members
451,591
Latest member
j0eyjedi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back