Formula for Join Two Sheet into One.

[Blek]

Hello again :D
Need Help.
Is it possible to join two Sheet into one?
Let's Pictures Talk.

Screenshot_24.jpgScreenshot_25.jpg
Screenshot_26.jpg

What's Single Formula for this situation if possible?

[azumi]

Put this on Sheet3 on cell A4 and copied down and cross:

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100<>""),ROW(A1))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100<>""),ROW(A1)-COUNTIF(Sheet1!A$4:A$100,"<>"))),""))

[Blek]

Put this on Sheet3 on cell A4 and copied down and cross:

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100<>""),ROW(A1))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100<>""),ROW(A1)-COUNTIF(Sheet1!A$4:A$100,"<>"))),""))

Not working for Excel 2016. Any suggest? xD.

It's Work perfect on newest excel. Thank you so much!

[Bo_Ry]

For Excel 2016 I would use Power query, you can add many tables with the same header.

1. Create tables
Sheet1 Select A3:Fxx Press Ctrl+T > OK
same for other sheets

2. Get data press Alt A P N O Q or Ribbon Data > Get Data > From other Sources > Blank Query

Advance editor > paste below code

Please Login or Register  to view this content.

Close and load to > Table > A3

Press Ctrl+Alt+F5 for refresh after add more data

[Blek]

Its working thank you so much azumi & Bo_Ry.
Always help :D

[Blek]

Put this on Sheet3 on cell A4 and copied down and cross:

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100<>""),ROW(A1))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100<>""),ROW(A1)-COUNTIF(Sheet1!A$4:A$100,"<>"))),""))

Hi Azumi, would you please help me with this Formula?

Screenshot_28.jpg

[Blek]

For Excel 2016 I would use Power query

Now I have latest excel. Do you know how to fix this ? Bo_Ry... Again.. Help :D

I Puted this on Sheet3 on cell A4 a

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100<>""),ROW(A1))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100<>""),ROW(A1)-COUNTIF(Sheet1!A$4:A$100,"<>"))),""))

The result gone wrong with Zero things =(.

Screenshot_28.jpg

[Bo_Ry]

Please try a4

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100>0),ROWS(A$4:A4))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100>0),ROWS(A$4:A4)-COUNTIFS(Sheet1!A$4:A$100,"<>0",Sheet1!A$4:A$100,"<>"))),""))

[Blek]

Please try a4

=IFERROR(INDEX(Sheet1!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$4:A$100)-MIN(ROW(Sheet1!A$4:A$100))+1)/(Sheet1!A$4:A$100>0),ROWS(A$4:A4))),IFERROR(INDEX(Sheet2!A$4:A$100,AGGREGATE(15,6,(ROW(Sheet2!A$4:A$100)-MIN(ROW(Sheet2!A$4:A$100))+1)/(Sheet2!A$4:A$100>0),ROWS(A$4:A4)-COUNTIFS(Sheet1!A$4:A$100,"<>0",Sheet1!A$4:A$100,"<>"))),""))

Working Perfect! Thank You so much! ;D

All Done.