Compare four sets of Columns

Miroy72

New Member
Joined
Aug 7, 2014
Messages
47
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>
 

Excel Facts

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,412
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. "
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,412

ADVERTISEMENT

"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
Joined
Jan 15, 2016
Messages
3,572
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
Joined
Aug 7, 2014
Messages
47

ADVERTISEMENT

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
Joined
Aug 7, 2014
Messages
47
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
Joined
Jan 15, 2016
Messages
3,572
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
Joined
Nov 7, 2006
Messages
8,412
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:

Forum statistics

Threads
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.
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
Top