Importing(Pulling) Data and Repeating Rows in Google Sheets

Cross-posted at
https://webapps.stackexchange.com/qu...-google-sheets
https://support.google.com/docs/thread/179014714?hl=en

Sample Sheet attached.(Link posting was not allowed)

Context: The Recommendations sheet and the Live Sheet would already be provided, and I need them as it is - no changes to be made in either of them. The task is to automate the Orders sheet and the Consolidated sheet. The Live sheet data will be manually updated by the pizzeria employees, and accordingly the Orders sheet and the Consolidated sheet should get automatically updated.

I request the following:
1. In the Orders Sheet - I need to pull the data, i.e. Orders!Columns A:M from the Live Sheet's respective columns.
Should I simply use arrayformulas or query or is there a better method?

2. In the Orders Sheet - What formula to use for Column N? The results are required as represented.

3. In the Consolidated Sheet - Please suggest how I can automate columns A to F of this sheet. We need to pull this data from the Orders sheet. This is the primary requirement, please help!

P.S. I know the Orders sheet might seem redundant, but I need the sheet to be created for other purposes as well.

Thank you in advance!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Since this is your first post I have added links, but please take the time to review our rules. There aren't many, and they are all important.

Have you posted this anywhere else that I didn't add links for?

Note that I didn't see that this had been posted on other sites until I had finished. Not seeing a ban on responding I'll go ahead and reply.
Hello ninjabang and Welcome to Excel Forum.
This proposed solution is written for Excel so I am not sure how it translates into google sheets, although I tried to avoid using functions that are not found in the google sheets function list.
On the Orders sheet:
1. Columns A:M are populated using formula similar to: =IF('Live Sheet'!A2="","",'Live Sheet'!A2)
2. Column N is populated using: =C2&"-"&D2&IF(F2<>"","-"&F2,"")&IF(H2<>"","-"&H2,"")&IF(J2<>"","-"&J2,"")&IF(L2<>"","-"&L2,"")
3. Column O [added] is populated using: =IF(A2="","",SUM(O1,SUMPRODUCT(--(D2:L2<>""))/2))
On the Consolidated sheet:
1. Columns A:C are populated using**: =INDEX(Orders!A$2:A$10,SMALL(IF(ROWS(A$2:A2)<=Orders!$O$2:$O$10,ROW(Orders!A$2:A$10)-ROW(Orders!A$1)),1))
2. Column D is populated using: =IF(A2="","",INDEX(Orders!C$2:L$10,MATCH(A2,Orders!A$2:A$10,0),COUNTIF(A$2:A2,A2)*2))
3. Column F is populated using: =IF(A2="","",INDEX(Orders!D$2:M$10,MATCH(A2,Orders!A$2:A$10,0),COUNTIF(A$2:A2,A2)*2))
**Denotes an array formula in Excel.
Let us know if you have any questions.

Originally Posted by JeteMc

Not seeing a ban on responding I'll go ahead and reply.

All good. We only bar replies when the OP is expected to resolve a violation, and in this case I did it for them.

Hi JeteMc,
Thank you so much for the solution. I really appreciate your effort and am grateful for the time you put in and your expertise.
However, I just have one additional request. The formula for consolidated!A:C isn't working properly in google sheets. It just keeps repeating the first value infinitely. Therefore, I would request you to please solve it in a google sheet, and share the link for the google sheet here.
Thanks a ton again!

Originally Posted by ninjabang

please solve it in a google sheet, and share the link for the google sheet here or to my email

If you need a solution in Google Sheets then you should put your file in Google sheets and you should share the link, giving others access to it.

Also please note that your post is public to the Internet and in spite of your efforts it will expose your email publicly. We recommend you do not post any personal information here.

I assume that you mean the array formula: =INDEX(Orders!A$2:A$10,SMALL(IF(ROWS(A$2:A2)<=Orders!$O$2:$O$10,ROW(Orders!A$2:A$10)-ROW(Orders!A$1)),1))
In Excel you would activate the formula by holding down CTRL and SHIFT, and only then press ENTER.
I think that in google sheets you wrap the formula within "ARRAYFORMULA" although I am not familiar enough with google sheets to know.
There are some contributors here who are more up to date in google sheets who may be able to confirm whether or not that is the case.
Sorry not to be of more help.

You can put the URL without the HTTP prefix and it should be allowed as long as it's not a clickable link. Otherwise you can PM me and I'll add it to the thread.

@6StringJazzer, heres the link with solutions highlighted in green on the orders and Consolidated tabs:

https://docs.google.com/spreadsheets...#gid=585417284