Highlighted cells to modify another Cell

G

gueddes

New Member
Joined
Jul 6, 2017
Messages
18
I have a long filtered sheet with aircraft data, each day more data comes in. All of this is filtered down to probably 50 items that are being monitored (Column K has a rule that only "Monitored", "Already Monitored" or "Fixed" can be put in with a pull down). The next day when data comes in several of those items that are monitored show up again. I have run a duplicate rule that highlights the items that are duplicated. What Im trying to do is have those cells that are highlighted (which happens to be Column L) make Column K change to "Already monitored". This needs to happen only to the new data so that the duplicate that was there already and was "Monitored" stays "Monitored" and the new duplicate will go to "Already Monitored".

Im using Excel 2010

Thanks in advance for any suggestions
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Does the new data always go below the original data (i.e. always below the item that is already monitored)?
 
Upvote 0
Yes, the new days data shows up and I have a macro that brings in the monitored data which goes to the bottom of the days data, so yes, at the bottom
 
Upvote 0
Try the code below (test on a copy of your worksheet).

Code: 
Sub MonitorDups()
    Dim testCell As Range, uniqueCell As New Collection
    Dim counter As Long, myRng As Range

    Application.ScreenUpdating = False

    Set myRng = Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row)
    
    For Each testCell In myRng
       On Error Resume Next
        If testCell.Value <> "" Then
            uniqueCell.Add testCell.Value, CStr(testCell.Value)
            If Err.Number <> 0 Then
                testCell.Offset(, -1).Value = "Already Monitored"
            End If
        End If
       On Error GoTo 0
    Next testCell

    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thanks mark ,
I tried it with it filtered and copy and pasted it into another sheet so that only the visible unfiltered data was there, nothing happened.
 
Upvote 0
Your data is column L still?

With the data below.


Excel 2010
KL
22
31
4AA
54
6BBB
72
8bb
9AA
10cc
111
122
13cc
14AA
154
16BBB
17KKKK
18BBB
19CCC
20AA
Overage Tracking Running Report


I get the below after running the macro.


Excel 2010
KL
22
31
4AA
54
6BBB
7Already Monitored2
8bb
9Already MonitoredAA
10cc
11Already Monitored1
12Already Monitored2
13Already Monitoredcc
14Already MonitoredAA
15Already Monitored4
16Already MonitoredBBB
17KKKK
18Already MonitoredBBB
19CCC
20Already MonitoredAA
Overage Tracking Running Report
 
Upvote 0
Actually, I just ran it again. Ive been trying so many different variants that I left Column L out. Column L combines Column A & B so I can root out the dups. Your code made all the items that were monitored and had duplicate change into already monitored. Im trying to get the items that are new to go from the blank cell which only allows monitor or already monitored to say already monitored. When the new data shows up row K is blank.
 
Upvote 0
Your last statement confuses me more. What column contains the values that have the original data (not anything you have altered)?

In fact, post a usable screenshot of your data so I can actually see it. There are links in my signature block on some ways to do this (don't post images like JPegs as they don't paste into the cells)
 
Upvote 0
Im at Work and those links are blocked, Basically I have the new data, and row K is blank all the way down. (someone has to assess it and decide if its worth working or monitor etc.) then I have a collection sheet where all the data is stored. its filtered so only the monitored items are shown. I copy the data that's being monitored and put it at the bottom of the new data. A lot of the people that are using this system will have multiple duplicates on monitor so my intention is to clean up the data base so each items is represented only once in the original document. When I ran your code it changed the items that were currently being monitored to "already monitored", well only the duplicates which is what im after. What Im trying to do is change the new data that is still blank in row K to "already monitored" If it is already "monitored" at the bottom. The data is then exported back into the main collection leaving out anything that's not on monitor. Your code is the closest Ive come to getting this to work. Much appreciated
 
Upvote 0
I don't get the first duplicate overwritten with the code I posted, are you sure that you don't have the word "Monitored" in cells that are not the first instance. I can test for the cell being blank but don't want to do it unless necessary.

As for the links in my signature the borders-copy-paste link in my signature goes to another thread on this site and so if you can get to this page you can get to that one and use the method as long as you have Internet Explorer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
random selection from a specific worksheet to show in a specific cell in another worksheet
Replies
9
Views
321
kevin9999
K
M
  • Question
VBA to Clean Up CSV with Large Blank Spaces and Extra Text Rows
Replies
5
Views
408
kevin9999
K
L
  • Solved
Conditional Formatting in Excel Table and Ignore Blanks Not Working
Replies
3
Views
253
Joe4
Joe4
B
  • Question
Formatting to highlight item warranty <6 months <12 Months and in warranty
Replies
4
Views
1K
awoohaw
awoohaw
M
  • Question
Conditional Format to highlight dupes on EACH column BUT only on visible filtered selection
Replies
4
Views
182
felixstraube
F

Forum statistics

Threads
1,214,661
Messages
6,120,792
Members
448,994
Latest member
rohitsomani

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top