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