# Sorting problem has me stumped.

#### Lycamp

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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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?

Hey that was a great solution!

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

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!

Replies
1
Views
148
Replies
3
Views
517
Replies
1
Views
302
Replies
19
Views
613
Replies
0
Views
595

1,212,149
Messages
6,106,250
Members
448,010
Latest member
wbarkwell

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