Color all cells in Google Sheets that contain data present in a set

Question

I have a Google Spreadsheet having possibly thousands of IP addresses extending from column A1 to column CS2990

And

I have a list of about 100 IP addresses

I need to color all the cells in Green which contain any IP addresses that is in the list of those 100 IP addresses.

The sheet contains IP addresses as 1.2.3.4/32 or 7.8.9.10/28 or something where number after / can change

And the list that I have contains IP addresses in the format of 1.2.3.4

So color the cells having 1.2.3.4, doesn't matter if it was 1.2.3.4/32 or 1.2.3.4/28

user0 · Accepted Answer · 2018-08-20 13:21:53Z

1

you can try this as a custom formula:

=COUNTIF(A1; "*1.2.3.4*")

for 100+ conditions you can do:

=OR(COUNTIF(A1;"*2.2.2.2*");COUNTIF(A1;"*1.2.3.4*");COUNTIF(A1;"*5.5.5.4*"))

or you can set a new sheet with references:

=OR(COUNTIF(A1;"*"&newsheet!A1&"*");COUNTIF(A1; "*"&newsheet!A2&"*"))

or you can use formula builder:

answered Aug 19 '18 at 20:25

user0

21.2k7 gold badges33 silver badges53 bronze badges

Thanks, but I have 100 of those IP addresses, so I'd need to do this 100 times. Isn't there a way where I can give all 100 IP addresses at once? – GypsyCosmonaut Aug 20 '18 at 1:06
@GypsyCosmonaut it's difficult because you are playing with masks ("*abc...*"), but I can think of only such way which I added to the end of my answer – user0 Aug 20 '18 at 3:00
=OR(COUNTIF(A1;"*2.2.2.2*");COUNTIF(A1;"*1.2.3.4*");COUNTIF(A1;"*5.5.5.4*")) This helped and I created a script to generate the formula in this format for 100 IPs and it worked. Thanks – GypsyCosmonaut Aug 20 '18 at 13:01
1

For people who'd like to know what I did, the solution is for linux users, or users using linux tools somehow on Windows with Cygwin. I pasted the list of IP addresses in file separated by newline and named the file ip and ran the following command echo '=OR('$(while read ip; do echo 'COUNTIF(A1;"*'$ip'*");'; done < ip | tr '\n' '%' | sed 's/%//g')')' which gave me the formula – GypsyCosmonaut Aug 20 '18 at 13:06
1

@GypsyCosmonaut nice. non-linux users can use docs.google.com/spreadsheets/d/… to generate custom formula by populating A column – user0 Aug 20 '18 at 13:15

add a comment |

1 Answer 1