SOLVED

Excel Online - Formula to get the unique rows from multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2568605%22%20slang%3D%22en-US%22%3EExcel%20Online%20-%20Formula%20to%20get%20the%20unique%20rows%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2568605%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Good%20Day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20formula%20will%20I%20use%20to%20get%20the%20the%20unique%20rows%20from%20multiple%20sheet%20and%20compile%20it%20to%20master%20sheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20use%20power%20query%20because%20I%20have%20no%20access%20to%20the%20Office%20desktops%20apps.%20I%20can%20only%20edit%20the%20file%20in%20excel%20online.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20So%20much..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amarie07_0-1626840404432.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297269i409CBCB04A8B258D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22amarie07_0-1626840404432.png%22%20alt%3D%22amarie07_0-1626840404432.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2568605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2571213%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Online%20-%20Formula%20to%20get%20the%20unique%20rows%20from%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2571213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1108584%22%20target%3D%22_blank%22%3E%40amarie07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Funique-function-in-data-validation%2Fm-p%2F2532454%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Funique-function-in-data-validation%2Fm-p%2F2532454%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, Good Day.

 

I need your help.

 

What formula will I use to get the the unique rows from multiple sheet and compile it to master sheet?

 

I can't use power query because I have no access to the Office desktops apps. I can only edit the file in excel online.

 

Thank You So much..

 

 

 

amarie07_0-1626840404432.png

 

4 Replies
best response confirmed by amarie07 (New Contributor)
Solution

@amarie07 

You may use references, but better transform your data in structured tables. When

=LET(
  rA, ROWS(Table1),
  rB, ROWS(Table2),
  c,  SEQUENCE(,COLUMNS(Table1)),
  k,  SEQUENCE(rA+rB),
  UNIQUE(IF(k<=rA, INDEX(Table1,k,c), INDEX(Table2,k-rA,c)))
)