-2

I want to work the following. I am using this formula

=ARRAYFORMULA(Split(Transpose(Split(Query(Transpose(query(transpose(if(Input!B2:I<>"", ";"&Input!A2:A&"\"&Input!B2:I, )) ,,999^99)),,999^99), ";")), "\"))

but it does not give the desired results. Here is the desired output 'Automatically restructure all data from input tab as "Example Output" tab illustrates
No rows for "blank" cells in the input tab
Use formula(s) only in the first row - i.e. no need to drag cells down the entire sheet, and this tab auto-updates when new entry made in Input tab" get the sheets on this link and give ideas on how to improve the formula or insights into how you can do it differently

player0
  • 124,011
  • 12
  • 67
  • 124
user001000
  • 29
  • 3
  • 2
    This is very unclear. A question shouldn't require an external link to understand. It is up to you to tell us what the desired output is, and in what way the formula fails to satisfy it. Based simply on information that you have given in the question, I really have no idea what you are asking. – John Coleman Jul 29 '19 at 11:54
  • 2
    Pretty sure `ArrayFormula` and `Query` are Google Sheets only. – Darren Bartrup-Cook Jul 29 '19 at 11:58

1 Answers1

1
=QUERY({Input!A2:C; 
 Input!A2:A, Input!D2:E; 
 Input!A2:A, Input!F2:G; 
 Input!A2:A, Input!H2:I; 
 Input!A2:A, Input!J2:K}, "where Col3 is not null", 0)

0

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(LEN(Input!A2:A),
 "♦"&Input!A2:A&"♥"&Input!B2:B&"♥"&Input!C2:C&
 "♦"&Input!A2:A&"♥"&Input!D2:D&"♥"&Input!E2:E&
 "♦"&Input!A2:A&"♥"&Input!F2:F&"♥"&Input!G2:G&
 "♦"&Input!A2:A&"♥"&Input!H2:H&"♥"&Input!I2:I&
 "♦"&Input!A2:A&"♥"&Input!J2:J&"♥"&Input!K2:K, )) 
 ,,999^99)),,999^99), "♦")), "♥"), "where Col3 is not null", 0))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank player0. However, the end results is not equal to what i desire from the sheets. I would like each block no to show all scrap types and their weights as follows: Block No. Scrap Type Scrap Weight 1505MC70401 Soft 151.7 1505MC70401 Hard 30.4 1505MC70402 Soft 150.4 1505MC70402 Hard 31.1 ..... 1505MB01 Recycle 45.1 1505MB01 Skin 45 1505MB01 Soft 80.1 1505MB01 Hard 30.1 – user001000 Jul 29 '19 at 16:52
  • can you talk to me via mugomacharia09@gmail.com and i will explain further please – user001000 Jul 29 '19 at 16:56