Sort/order a list by an existing one

RayManzarek

New Member
Joined
Sep 20, 2018
Messages
6
Hello,

I have a customer list with approx. 200 addresses, each in a separate line, that are ordered according to my needs (shortest trip with my car).

Every day I receive a new list with customers, but this new one is not ordered according to my needs. Please tak a look at a sample;

My List:

1. London street 1
2. Berlin street 44
3. Paris Road 4
4. Madrid street
5. Wiena Road

Every day list - The list I receive every day;
1. Wiena Road
2. Paris Road 4
3. Berlin street 44
4. London street 1
5. Madrid street
6. Amsterdam Road


Is there a way that I take data regarding address from My list and with that data I will change the Every day list address order according to the one in My list? Also if there is a new address in Every day list (in this case "Amsterdam Road") that is not included in My List, is it possible that this "new" address is ordered at the end of the list?

Thank you for you help!

Regards from Slovenia (EU),

Luca
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Welcome to the forum.

There is such a thing as a custom sorting list, but the number of items in it is well under 200. I'd recommend adding another column after the every day list that looks up the address from your "My List", like this:

ABCDEFG
1My List:Other stuffEvery day list - The list I receive every day;
2London street 1AWiena Road5
3Berlin street 44BParis Road 43
4Paris Road 4CBerlin street 442
5Madrid streetDLondon street 11
6Wiena RoadEMadrid street4
7FAmsterdam Road9999

<tbody>
</tbody>
Sheet13

Worksheet Formulas
CellFormula
F2=IFERROR(MATCH(E2,$A$2:$A$10,0),9999)

<tbody>
</tbody>

<tbody>
</tbody>



Then just sort your table (D:F in this example) by column F.

I don't know how you get your data, but I'd worry a bit that the addresses won't match up exactly. If that's the case, then you'd have to do some fuzzy matching, and that gets tricky real fast.
 
Last edited:

RayManzarek

New Member
Joined
Sep 20, 2018
Messages
6
Hello Eric,

Thank you very very much!!!

I will be in action in a second and try out your idea!

I am not "very good" in Excel programming but I do know that there is a function in Excel to sort a list and I know that it is (unfortunately) limited way under 200.

I will let you know if you are the one who brought me a "no headache day" :)

Best regards,

Luca
 

RayManzarek

New Member
Joined
Sep 20, 2018
Messages
6
Hello Eric,

Tank you again for your help and support.

Unfortunately I am stuck with Excel due to my Excel basic knowledge (it is not pure basic but coding in Excel is for me rocket science).

If you are so kind plese take a look at the sample list below. I would be more than thankful for your help - instructions where and how to insert your code.


ABCDEFG
1EVERY DAY LIST (not sorted)
2CUSTOMER (code and name/surname)ADDRESSZIPDELIVERY DATEPURCHASED ITEM
3286257 JOHN DOECESTA NA HAJDINO 24232521.09.2018iPHONE
4286257 JOHN DOECESTA NA HAJDINO 24232521.09.2018SAMSUNG
5357649 DENIS BLACKCESTA V NJIVERCE 36232521.09.2018iPHONE
6707709 MARY GREENGEREČJA VAS 3228821.09.2018iPHONE
7707709 PATRICK YELLOWGEREČJA VAS 3228821.09.2018SAMSUNG
8187734 DONALD REDGEREČJA VAS 11228821.09.2018iPHONE*This one is in Every day list but not in My list
9311906 DIANA QUEENGEREČJA VAS 21228821.09.2018LED TV
10620674 SAMSUNG ORANGEGEREČJA VAS 21228821.09.2018iPHONE
11620674 SAMSUNG ORANGEGEREČJA VAS 21228821.09.2018SAMSUNG
12713505 BRIGIT NELSONGEREČJA VAS 26228821.09.2018iPHONE
13162663 ALICE SWAZYGEREČJA VAS 39228821.09.2018iPHONE
14667041 TINA TURNERGEREČJA VAS 40C228821.09.2018iPHONE
15189513 ELVIS McROCKERGEREČJA VAS 69228821.09.2018iPHONE
16722748 MARIAH SINGERINDUSTRIJSKO NASELJE 1232521.09.2018iPHONE
17630353 RAY MANZAREKINDUSTRIJSKO NASELJE 10232521.09.2018RADIO Player
18
19
20MY LIST (sorted)
21DELIVERY ORDERADDRESS
221.CESTA V NJIVERCE 36
232.CESTA NA HAJDINO 24
243.INDUSTRIJSKO NASELJE 10
254.INDUSTRIJSKO NASELJE 1
265.GEREČJA VAS 21
276.GEREČJA VAS 69
287.GEREČJA VAS 3
298.GEREČJA VAS 40C
309.GEREČJA VAS 26
3110.GEREČJA VAS 39
3211.GEREČJA VAS 70*This one is in My list but not in the Every day list - did not made a purchase
33

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Thank you in advance!!!!

Best regards,

Luca
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
ABCDEFGHI
1EVERY DAY LIST (not sorted)
2CUSTOMER (code and name/surname)ADDRESSZIPDELIVERY DATEPURCHASED ITEMSequence #
3286257 JOHN DOECESTA NA HAJDINO 24232521.09.2018iPHONE2
4286257 JOHN DOECESTA NA HAJDINO 24232521.09.2018SAMSUNG2
5357649 DENIS BLACKCESTA V NJIVERCE 36232521.09.2018iPHONE1
6707709 MARY GREENGERECJA VAS 3228821.09.2018iPHONE7
7707709 PATRICK YELLOWGERECJA VAS 3228821.09.2018SAMSUNG7
8187734 DONALD REDGERECJA VAS 11228821.09.2018iPHONE*This one is in Every day list but not in My list9999
9311906 DIANA QUEENGERECJA VAS 21228821.09.2018LED TV5
10620674 SAMSUNG ORANGEGERECJA VAS 21228821.09.2018iPHONE5
11620674 SAMSUNG ORANGEGERECJA VAS 21228821.09.2018SAMSUNG5
12713505 BRIGIT NELSONGERECJA VAS 26228821.09.2018iPHONE9
13162663 ALICE SWAZYGERECJA VAS 39228821.09.2018iPHONE10
14667041 TINA TURNERGERECJA VAS 40C228821.09.2018iPHONE8
15189513 ELVIS McROCKERGERECJA VAS 69228821.09.2018iPHONE6
16INDUSTRIJSKO NASELJE 1INDUSTRIJSKO NASELJE 1232521.09.2018iPHONE4
17630353 RAY MANZAREKINDUSTRIJSKO NASELJE 10232521.09.2018RADIO Player3
18
19
20MY LIST (sorted)
21DELIVERY ORDERADDRESS
221CESTA V NJIVERCE 36
232CESTA NA HAJDINO 24
243INDUSTRIJSKO NASELJE 10
254INDUSTRIJSKO NASELJE 1
265GERECJA VAS 21
276GERECJA VAS 69
287GERECJA VAS 3
298GERECJA VAS 40C
309G
ERECJA VAS 26
3110GERECJA VAS 39
3211GERECJA VAS 70*This one is in My list but not in the Every day list - did not made a purchase

<tbody>
</tbody>
Sheet14

Worksheet Formulas
CellFormula
H3=IFERROR(MATCH(C3,$C$22:$C$32,0),9999)

<tbody>
</tbody>

<tbody>
</tbody>




Put the formula in H3, then copy down the column to H17. Then select B3:H17, click Home > Sort & Filter > Custom Sort > Sort by > column H. That should do it. I'd recommend putting "My List" on another tab, and getting rid of row 1 from this example, then you can just select columns B:H when sorting.
 

RayManzarek

New Member
Joined
Sep 20, 2018
Messages
6
Hello,

Thank you for your time and your kindness!

I know what went wrong in my Excel. I have had to change the code from the original =IFERROR(MATCH(C4,$K$3:$K$13,0),9999) in to =IFERROR(MATCH(C4;$K$3:$K$13;0);9999).

The diference is the change from "," in to ";".

Thank you again for your excellent help!!!!

Very best regards from sleepy Slovenia,

Luca
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Aha! I should have thought of that when you said you were in Solvenia. Glad you got it worked out! :)
 

RayManzarek

New Member
Joined
Sep 20, 2018
Messages
6
Hello,

Thank you again for your help! :)

I have 3 columns, approx 300 rows and a simple formula in column C ... =SUM(A:B) ...;

A B C
1 1 2

How to mark all results in column C that are =2 with a color?

I was able to find all results that are =2 but if I go find all and replace all there is no option to chose only "results" that are 2.

What to do?

Best regards,

Luca
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Try Conditional Formatting. Select column C, click Conditional Formatting > Highlight Cell Rules > Equal To > enter 2 and select a color from the drop down.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top