# Compare four sets of Columns

#### Miroy72

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

 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>

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### 63falcondude

##### Well-known Member
Try =IF(COUNT(H3,F3,D3,B3),"","No Activity") in I2

#### Special-K99

##### Well-known Member
Try =IF(COUNT(H3,F3,D3,B3),"","No Activity") in I2

Dont think you can do that cos of this:

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

#### Miroy72

##### New Member
Yes, that's the issue

#### Special-K99

##### Well-known Member

"If the IP column is blank across all 4 weeks"

What happens if the Key doesnt exist 4 times? If it's only 3 times and all IPs for it are blank?
Does that also constitute a "No Activity" or must the key exist in all 4 weeks for a "No Activity" to be returned?

#### 63falcondude

##### Well-known Member
Dont think you can do that cos of this:

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

Missed that. I was going off of the sample data. OP, how would you correlate column I to the other columns if everything is out of order? Take this layout for example. What would column I look like?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Week 1</td><td style=";">Week 1</td><td style=";">Week 2</td><td style=";">Week 2</td><td style=";">Week 3</td><td style=";">Week 3</td><td style=";">Week 4</td><td style=";">Week 4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Key</td><td style=";">IP</td><td style=";">Key</td><td style=";">IP</td><td style=";">Key</td><td style=";">IP</td><td style=";">Key</td><td style=";">IP</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1234</td><td style="text-align: right;;">545</td><td style="text-align: right;;">1234</td><td style="text-align: right;;">545</td><td style="text-align: right;;">1234</td><td style="text-align: right;;">545</td><td style="text-align: right;;">1234</td><td style="text-align: right;;">545</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4561</td><td style="text-align: right;;">546</td><td style="text-align: right;;">3574</td><td style="text-align: right;;"></td><td style="text-align: right;;">3574</td><td style="text-align: right;;"></td><td style="text-align: right;;">3574</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3574</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4561</td><td style="text-align: right;;">546</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

#### Miroy72

##### New Member

It must be across all 4 weeks. So if there is no IP for 3 weeks and then there is an IP for the 4th week, I would not count that one as "No Activity". Thanks!

#### Miroy72

##### New Member
So in this case, Column A would be the main column and "No Activity" would be in Row 5/Column I sine Key=3574 has no IP number across all 4 weeks. Row 3 and 4 would be labeled "Activity" in column I. Thanks for the help!

#### 63falcondude

##### Well-known Member
So in this case, Column A would be the main column and "No Activity" would be in Row 5/Column I sine Key=3574 has no IP number across all 4 weeks. Row 3 and 4 would be labeled "Activity" in column I. Thanks for the help!

Try this in I3.

=IF(SUMIF(\$A\$3:\$G\$5,A3,\$B\$3:\$H\$5),"Activity","No Activity")

Ranges based off of example in post #6. This assumes that the IP consists of numbers like in the sample data.

If the IP's can be something other than numbers, try this instead

=IF(SUMPRODUCT((\$A\$3:\$G\$5=A3)*(\$B\$3:\$H\$5<>"")),"Activity","No Activity")

Last edited:

#### Special-K99

##### Well-known Member
Try this in I3.

=IF(SUMIF(\$A\$3:\$G\$5,A3,\$B\$3:\$H\$5),"Activity","No Activity")

Ranges based off of example in post #6. This assumes that the IP consists of numbers like in the sample data.

If the IP's can be something other than numbers, try this instead

=IF(SUMPRODUCT((\$A\$3:\$G\$5=A3)*(\$B\$3:\$H\$5<>"")),"Activity","No Activity")

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

Code:
1234 blank 1234 blank 1234 blank 1234 blank
7890 1234  5678 13

Last edited:

Replies
0
Views
85
Replies
9
Views
99
Replies
3
Views
90
Replies
13
Views
187
Replies
5
Views
556

1,140,925
Messages
5,703,205
Members
421,280
Latest member
Jaycee01

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