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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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. "
 
Upvote 0
"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?
 
Upvote 0
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?


Excel 2010
ABCDEFGH
1Week 1Week 1Week 2Week 2Week 3Week 3Week 4Week 4
2KeyIPKeyIPKeyIPKeyIP
31234545123454512345451234545
44561546357435743574
535744561546
Sheet1
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top