Excel Sort Order doesn't match comparison

ozloz

New Member
Joined
Aug 29, 2018
Messages
5
Hi all
I am trying to so something which I thought would be very simple but am tearing my hair out.
I am trying to work through two lists to update values that match in both worksheet 1 and 2, and add values from worksheet 2 that don't have a corresponding entry in WS 1

Do do this I could skip down each entry in WS1, use find to see if there is a matching entry in WS1. If so, update it. If not, add it. Simple but slow if there are a lot of records

So I thought I could sort them both by the key field (computer name) and then bubble my way down.
* If the entry in WS1 is less than the current entry in WS2, add it and move both to the next
* If they are the same, update it and move both to the next
* If the entry in WS2 is greater than the current entry in WS1, ignore it and move to the next

BUT Excel doesn't seem to sort in the same order than it compares. For example excel sorts a list like this:
Worksheet 1:
ACT-P222H
ADWIN7VM
AG_TESTBC
AG100000020
AG100000021
AG100000022

WorkSheet 2:
Skipping
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

ozloz

New Member
Joined
Aug 29, 2018
Messages
5
Sorry, can find how to edit as it posted accidentally. Anyway I carry on:
Worksheet 1:
ACT-P222H
ADWIN7VM
AG_TESTBC
AG100000020
AG100000021
AG100000022

Worksheet 2:
ACT-P222H
AG100000020
AG100000021
AG100000022

Skipping through and checking,
if I use if (WS2.value < WS1.Valut) then

AG_TESTBC compares greater than AG100000020
? "AG_TESTBC" < "AG100000020"
False


Does anybody know how it is possible to either sort in the same order that Excel will compare or change the way I compare values?

Many thanks
 

ozloz

New Member
Joined
Aug 29, 2018
Messages
5
Amazing how immediately after posting a question the answer comes to you.
It seems to work correctly if I use strComp(WS1.value,WS2.Value,vbTextCompare)
Doh!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,941
Messages
5,627,738
Members
416,271
Latest member
Bolweavil

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