Compare four sets of Columns

[Miroy72]

If there is a formula or a macro for this, that would be great. Using Excel 2010.

What I am trying to do here is, I am trying to match all the "Key" columns across all 4 weeks and see if they have the IP column blank. If the IP column is blank across all 4 weeks, I just need to indicate it lets say in column I with "No Activity".

There are about 5000 rows and the numbers in column "Key" are always out of order and some Key are added in week 2 and were not there in week 1 so I cant just sort them and compare rows.

Here is an example of the data:

In this case, the row with Key = 3574 would be the one where I would indicate it with "No Activity" in column I.

Thanks in advance!

A	B	C	D	E	F	G	H
Week 1	Week 1	Week 2	Week 2	Week 3	Week 3	Week 4	Week 4
Key	IP	Key	IP	Key	IP	Key	IP
1234	545	1234	545	1234	545	1234	545
3574		3574		3574		3574

<tbody>
</tbody>

 

[63falcondude]

Re the SUMPRODUCT and in particular the wide A:G column range
what happens in the (probably unlikely, though totally uncatered for) event that a number in the IP column matches a number in the Key column?
Since the column after this is actually a key and should contain a value the real key in the table now has a non blank against it so the result will be "Activity".
But if the real key occurs 4 times and has 4 blanks the result should be "No Acitivty". The existence of a key in the IP column that matches a key has forced the result into "No Activity".

I think what im trying to say is your range of A:G should only take into account columns that are keys and not look at IP columns at all. Maybe incorporate a MOD(,2) to identify odd numbered columns (the keys)?

example data

1234 blank 1234 blank 1234 blank 1234 blank
7890 1234  5678 13

Good point. What about adding ($A$2:$G$2="Key") into the formula?

SUMPRODUCT part only:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Week 1	Week 1	Week 2	Week 2	Week 3	Week 3	Week 4	Week 4
2	Key	IP	Key	IP	Key	IP	Key	IP
3	1234		1234		1234		1234		0
4	7890	1234	5678	13					1
Sheet1

Cell Formulas
Range	Formula
I3	=SUMPRODUCT(($A$3:$G$4=A3)*($A$2:$G$2="Key")*($B$3:$H$4<>""))

 

[Miroy72]

The numbers in "Key" column, can me numbers like 54755455 and some include letters like 5454dfdfe54e. The "Key" column and the "IP" column cant have the same number included. The "IP" columns are IP addresses and they are a different format like 10.244.34.577.

Not sure if this changes anything.

Thanks again for all the help!

 

[63falcondude]

The numbers in "Key" column, can me numbers like 54755455 and some include letters like 5454dfdfe54e. The "Key" column and the "IP" column cant have the same number included. The "IP" columns are IP addresses and they are a different format like 10.244.34.577.

Not sure if this changes anything.

Thanks again for all the help!

Perfect! Then the SUMPRODUCT formula from post #9 should work fine for you.

 

[Miroy72]

Perfect! Then the SUMPRODUCT formula from post #9 should work fine for you.

Thank you, works great!

 

[63falcondude]

Thank you, works great!

You're welcome, glad to help!