CODEWITHSUNDEEP
excelexcel-formula
Vinayak Bhat
Vinayak Bhat

Reputation: 33

Creating a list from table values in Spreadsheet/Excel

I have some data in tabular form and based on tables values, I need to create a list using corresponding Row and Column headers

enter image description here

So, as seen in table, Number 1 is at intersection of City1 and Item1, so I want City1_Item1 in the output list. Similarly 2 is at intersection of City2, Item2 and also at City3, Item3, so I want City2_Item2_City3_Item3 as another item in the output list.

Upvotes: 0

Views: 946

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

In B10, array formula (Ctrl+Shift+Enter) copied down :

=SUBSTITUTE(TRIM(CONCAT(IF(B$2:F$7=A10,A$2:A$7,"")&" "&IF(B$2:F$7=A10,B$1:F$1,"")&" "))," ","_")

enter image description here

Upvotes: 2

Related Questions