Forum Discussion
iliulian
Microsoft
MicrosoftCombine table name with each value in the first column
Hello, I need a little help in Excel. So I have some tables of the following structure scattered across multiple sheets. (Fake data)
Nokia Phone model Color 6610i Blue 3310 Y...
One way could be using the following formula-
=VSTACK(
IFNA(HSTACK(Sheet2!A1,Sheet2!A3:B4),Sheet2!A1),
IFNA(HSTACK(Sheet3!A1,Sheet3!A3:B4),Sheet3!A1),
IFNA(HSTACK(Sheet3!A1,Sheet4!A3:B4),Sheet4!A1)
)
iliulianMicrosoft
MicrosoftCould this be expanded to handle cases where there are multiple tables in some sheets or none in some sheets without activating any advanced features, just with basic functions?
=LET(sheets,A1:A5, sheetscombine,DROP(REDUCE("",sheets,LAMBDA(x,y, VSTACK(x,IFERROR(TRIMRANGE(INDIRECT("'"&y&"'!A:B")),HSTACK("",""))))),1), removeheaders,FILTER(sheetscombine,NOT(BYROW(INDEX(sheetscombine,,1),LAMBDA(x,ISNUMBER(SEARCH("Phone",x)))))), filldownmanufacturer,SCAN("",MAP(INDEX(removeheaders,,1),INDEX(removeheaders,,2), LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),"",a))),LAMBDA(a,b,IF(REGEXTEST(b,"[A-Za-z]+"),b,a))), stacked,HSTACK(filldownmanufacturer,removeheaders), VSTACK( HSTACK("Manufacturer","Model","Color"), FILTER(stacked,REGEXTEST(INDEX(stacked,,3),"[A-Za-z]+"))))I understand you only want basic functions but just in case you want to log on to your Microsoft account and use all the latest functions in Excel online you can use this formula. Or perhaps other users want to try this formula which handles multiple tables in some sheets and no table in some sheets.
In that case, we may need to manually enter table names inside REDUCE() array. Or we can make a list of tables name in cells and then can use REDUCE() to iterate those cells to stack data.
