Home

Syncing Out Of Order Datasets

%3CLINGO-SUB%20id%3D%22lingo-sub-817582%22%20slang%3D%22en-US%22%3ESyncing%20Out%20Of%20Order%20Datasets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817582%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20one%20spreadsheet%2C%20I%20have%20a%20list%20of%20origin%20cities%20in%20column%20A%20and%20a%20list%20of%20destination%20cities%20in%20column%20B.%20In%20column%20C%2C%20I%20have%20the%20mileage%20between%20the%20two.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20another%20spreadsheet%2C%20I%20have%20the%20same%20list%20of%20origin%20and%20destination%20cities%20but%20with%20the%20rate%20per%20mile%20for%20each%20route%20in%20column%20C.%20This%20spreadsheet%20is%20not%20in%20the%20same%20order%20as%20the%20first%20one.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20formulae%20can%20I%20use%20to%20bring%20over%20the%20content%20of%20spreadsheet%202%2C%20column%20C%2C%20and%20have%20it%20in%20correctly%20ordered%20with%20the%20content%20of%20spreadsheet%201%3F%20I%20am%20planning%20on%20multiplying%20the%20rate%20per%20mile%20by%20the%20mileage%20to%20get%20the%20rate%20for%20the%20trip%20so%20the%20mileage%20and%20the%20rates%20must%20match.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20guys%20so%20much!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-817582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818163%22%20slang%3D%22en-US%22%3ERe%3A%20Syncing%20Out%20Of%20Order%20Datasets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818163%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396826%22%20target%3D%22_blank%22%3E%40jucotransfer%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20all%20of%20the%20values%20in%20column%20A%20unique%3F%3CBR%20%2F%3EOr%20are%20all%20of%20the%20values%20in%20column%20B%20unique%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818238%22%20slang%3D%22en-US%22%3ERe%3A%20Syncing%20Out%20Of%20Order%20Datasets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396826%22%20target%3D%22_blank%22%3E%40jucotransfer%3C%2FA%3EYou%20could%20use%20SUMIFS%20to%20lookup%20both%20the%20origin%20and%20destination%20in%20the%20first%20spreadsheet%20and%20return%20the%20value%20for%20mileage%20to%20your%20second%20spreadsheet.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818590%22%20slang%3D%22en-US%22%3ERe%3A%20Syncing%20Out%20Of%20Order%20Datasets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396826%22%20target%3D%22_blank%22%3E%40jucotransfer%3C%2FA%3E%26nbsp%3Bfor%20such%20sample%3C%2FP%3E%0A%3CP%3ESheet1%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20329px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128080i4EF070C6CB799A6D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESheet2%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20214px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128081i56B8AB10D9E65999%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Erate%20into%20first%20sheet%20could%20be%20returned%20by%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(INDEX(Sheet2!C%3AC%2C%20MATCH(1%2CINDEX(%20(A2%3DSheet2!A%3AA)*(B2%3DSheet2!B%3AB)%2C0)%2C0))%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
jucotransfer
Occasional Visitor

Hello everyone, 

 

In one spreadsheet, I have a list of origin cities in column A and a list of destination cities in column B. In column C, I have the mileage between the two. 

 

In another spreadsheet, I have the same list of origin and destination cities but with the rate per mile for each route in column C. This spreadsheet is not in the same order as the first one. 

 

What formulae can I use to bring over the content of spreadsheet 2, column C, and have it in correctly ordered with the content of spreadsheet 1? I am planning on multiplying the rate per mile by the mileage to get the rate for the trip so the mileage and the rates must match. 

 

Thank you guys so much! 

3 Replies
Hello @jucotransfer

Are all of the values in column A unique?
Or are all of the values in column B unique?

@jucotransferYou could use SUMIFS to lookup both the origin and destination in the first spreadsheet and return the value for mileage to your second spreadsheet.  

@jucotransfer for such sample

Sheet1:

image.png

Sheet2:

image.png

rate into first sheet could be returned by

=IFNA(INDEX(Sheet2!C:C, MATCH(1,INDEX( (A2=Sheet2!A:A)*(B2=Sheet2!B:B),0),0)),0)
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies