1

Sheet1

I have this data of car price based on its color on Sheet1

Car Red Blue Black White
BMW 100 120 90 120
FIAT 90 100 110 120
FORD 70 80 80 80

Sheet2

I want these cars and colors combined in column A. In column B, I want to display its respective price. I have achieved to display column A with this formula, but I am open to it if there is any suggestion. For column B, I can not figure out yet how to accomplish the goal.

The formula for column A

=ArrayFormula(transpose(split(rept(concatenate(Sheet1!A2:A&char(9)),counta(Sheet1!B1:Sheet1!E1)),char(9)))
&" "&transpose(split(concatenate(rept(Sheet1!B1:Sheet1!E1&char(9),counta(Sheet1!A2:Sheet1!A))),char(9))))

I got this formula from here

The expected output in Sheet2

Car Color Price
BMW Red 100
BMW Blue 120
BMW Black 90
BMW White 120
FIAT Red 90
FIAT Blue 100
FIAT Black 110
FIAT White 120
FORD Red 70
FORD Blue 80
FORD Black 80
FORD White 80
player0
  • 124,011
  • 12
  • 67
  • 124
cowfifteen
  • 25
  • 4
  • Almost a near identical question like this was asked today. See answer here then suggest you close: https://stackoverflow.com/questions/68762676/formula-to-use-to-achieve-the-header-into-each-row/68763695?noredirect=1#comment121525260_68763695 – pgSystemTester Aug 13 '21 at 04:40
  • Thanks for the reply. Since my Google Workspace doesn't support script editing, I will consider this solution for another condition. But for now, I need a solution that is applicable anywhere. – cowfifteen Aug 13 '21 at 06:50

2 Answers2

4

use:

=INDEX(QUERY(SPLIT(FLATTEN(IF(B2:E="",,A2:A&" "&B1:E1&"×"&B2:E)), "×"), 
 "where Col2 is not null"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Sorcery!! I added this to my answer in the near identical question. https://stackoverflow.com/questions/68762676/formula-to-use-to-achieve-the-header-into-each-row/68763695?noredirect=1#comment121525260_68763695 – pgSystemTester Aug 13 '21 at 17:41
1

I agree with @pgSystemTester in the comments that you might consider closing this question.

But here is a solution using LET, if you have the newest version of Excel.

=LET(x, $I$2:$L$4,
myrows, ROWS(x),
mycols, COLUMNS(x),
mycount, SEQUENCE(myrows*mycols),
car, $H$2:$H$4, color, $I$1:$L$1,
mylist, car&" "&color,
mycolumn, INDEX(mylist, CEILING(mycount/mycols,1), IF(MOD(mycount,mycols)=0, mycols,MOD(mycount,mycols))),
mydata, INDEX(x, CEILING(mycount/mycols,1), IF(MOD(mycount,mycols)=0, mycols,MOD(mycount,mycols))),
IF(SEQUENCE(1,2)=1, mycolumn, mydata))

enter image description here

EDS
  • 2,155
  • 1
  • 6
  • 21
  • 2
    There is no LET function in Google Sheets, by the way. But this solution is constructive if I use Microsoft Excel. – cowfifteen Aug 13 '21 at 07:10