Mar 24 2021 10:01 AM
I have a large "table" that lists types of furniture down the side, and room numbers across the top. As you can see, there are a lot of blank spaces, depending on what furniture goes into what room.
I'm trying to figure out if there is a way that Excel can do like a summary (like I highlighted in yellow), where it would list the room numbers from the top row, for each piece of furniture, where the quantity is greater than zero.
Thanks!
Mar 24 2021 10:06 AM
In case the excel file I attached doesn't open correctly, here is a jpg of the same data
Mar 24 2021 10:39 PM
@JayClark In case you are a MS365 subscriber in the Current channel, you could use FILTER for this as demonstrated in the picture.
Mar 24 2021 11:34 PM
This is also a 365 solution, and differs from @Riny_van_Eekelen 's only in that I ban the use of direct cell referencing.
= LET(
itemCount, XLOOKUP(@item,item,count),
FILTER(room, itemCount>0))
An alternative strategy is to employ a function that does exist outside 365
= LET(
itemcount, XLOOKUP(@item, item, count),
TEXTJOIN(", ",, IF(itemcount>0, room,"")))
Mar 25 2021 12:14 AM
@JayClark Another variant using Power Query. Works on practically all Excel versions, except Mac. See column O:P in the attached workbook.