Sorting problem has me stumped.

[Lycamp]

Hello. I hope someone can help me. My team and I collect data on large numbers of Pcs in my corporation to insure they are up to date with all the microsoft security patches. We collect this data in the morning and afternoon. We put the mornings list of PC names in column A. The afternoons list goes in column B. I want to use columns C&D to show the sort. Column C should show what was on A in the Morning and is no longer on B for the Afternoon. (those that fell off(got the patch)) and Column D should show what is on B in the Afternoon and was not on A in the Morning. (new machines)

I have not been able to work out a solution. ANY help woud be GREATLY appreciated and I thank you in advance for everyone's time and trouble [/img]

 

[Tatster]

This might work

Hi,

I've gto something that kinda works. Can't do this VBA stuff yet so will try and explain it long hand. One lateration I would encourage would be to swap your B and C columns round. It just reads better visually but it's up to you. or demonstrations sake I'm assuming a list of 20 computers.

The way I see it in Column A you have your list of PC names from the morning collection.
Then I'd enter the PC names for the afternoon in Column C.

In the first cell of Column B type in:

=IF(ISERROR(MATCH(A1,$C$1:$C$20,0)),A1&" - Patched",A1)

Copy this formula down column B. You should get the same list of computer names as in Column A except if it's "fallen off" as you put it, then the returned result will be:

Computer A - Patched

If the computer is also in the afternoon's collection then just the name will show. You can tinker as you like with result format by altering the [value if true / false] entries in the initail formula.

To identify new computers you can use a variation of the same trick. In the first cell of column D type in:

=IF(ISERROR(MATCH(C1,$A$1:$A$20,0)),C1&" - New",C1)

Again copy this formula down the column. If there's a computer which wasn't in the morning collection then the returned result should be:

Computer B - New

If a computer was present in both moring and afternoon collections then just the name will show.

Hope this is useful to you. Let me know how it works out if it is.

Note: It's tempting to just select the entire column instead of a specified range as the [lookup_array] but this won't work. Excel probably doesn't count an entire columns as an array for some undoubtedly sensible reason which I don't know.

 

[Lycamp]

tried it it seems to work. Need to ask you one quetion and that is when it's run and it returns patched or not...does it search through the whole column or by row...A1=C1,A2=C2?

 

[Atom]

Hey that was a great solution!

It will search down the whole list looking for a match.

 

[Lycamp]

So far, in testing your solution, it has worked great! Thank you! Is there a way to take the data in column B and D that was generated and be able to sort it showing which ones are 'computer name - patched' and 'computer name - new' in new columns? If it can't be done, no problem, or if someone can tell me how to sort it where all of the ones with 'patched' are together and the same for all of those that say 'new'. One again thank you for the quick help, it is already a great time saver!