which teams were removed

[emvella]

Hi,

I have a long list of soccer teams in each cell example like the one below from a website data.

B1 LIVERPOOL
B2 CHELSEA
B3 ARSENAL
B4 MAN UNITES
etc...

Once a week I udate data by pressing, data, refresh all, so teams will change from week to another. I want a formula that the new teams in cells will be colored with a different color to make it easier for me to know which teams are new. Example the one below Newcastle is a new team so I want it to be colored.

B1 LIVERPOOL
B2 CHELSEA
B3 NEWCASTLE (ARSENAL)
B4 MAN UNITES
etc...

If possible I also want to know which teams were removed from the list! Example I want something yo show me that Arsenal was removed.

Thanks

 

[mart37]

It is possible when the old list exists. You can use than a double vlookup.

 

[FDibbins]

It is possible when the old list exists. You can use than a double vlookup.

I don't see how that will change the color?

I would use Conditional Formatting for this.

With your main data in the top list and your new data in the 2nd list...

	B​
1​	LIVERPOOL
2​	CHELSEA
3​	ARSENAL
4​	MAN UNITES
5​
6​
7​	LIVERPOOL
8​	CHELSEA
9​	NEWCASTLE (ARSENAL)
10​	MAN UNITES

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =ISERROR(MATCH(B7,$B$1:$B$4,0)) format fill RED (or choose a color)

This is obviously being applied to my small range, so you will need to adjust my ranges as needed

 

[emvella]

I think the best thing is I send the excel file with more details what exactly I want. Please let me know who wants the file and I send it to you.

 

[FDibbins]

Did you try what I suggested?

 

[emvella]

Hi FDibbins and thanks for your help. Yes I did and maybe I did wrong, but it didnt work.

 

[FDibbins]

I just tested it again, and it works fine for me

	B​
1​	LIVERPOOL
2​	CHELSEA
3​	ARSENAL
4​	MAN UNITES
5​
6​
7​	LIVERPOOL
8​	CHELSEA
9​	NEWCASTLE (ARSENAL)
10​	MAN UNITES

In my sample, I used $B$7:$B$10 as the range.
Then for the New Rule/Use Formula I used this
=ISERROR(MATCH(B7,$B$1:$B$4,0))

 

[emvella]

Hi FDibbins I tried it and basically that's what I am looking for. The only thing is that when I refresh data, the new list take place of the old list so it should be a different formula.

Please can I send you my excel list and tell you more details?

 

[FDibbins]

if you keep over-writing cells and any underlying functions, with new data, then you would need to use VBA to keep replacing those functions. Either that, or update your new data to below the existing data (add it below), then use another table (with formulas) to extract the specific data that you need

 

[emvella]

hi,

I just found exactly what I wanted with conditional formatting 'format only unique values'.

I paste two lists in excel, one the old list (left) and one the new list (right). So when I paste the new list, the new teams are marked with red in the new list, and in the old list teams that are not in the new list, they are marked with red too!

I have a list of over 100 teams so it was difficult to find and search manually, now it's very easy!