Can Excel do this?

New Contributor

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.



4 Replies

In case the excel file I attached doesn't open correctly, here is a jpg of the same data

@JayClark In case you are a MS365 subscriber in the Current channel, you could use FILTER for this as demonstrated in the picture.

Screenshot 2021-03-25 at 06.37.03.png


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,"")))



@JayClark Another variant using Power Query. Works on practically all Excel versions, except Mac. See column O:P in the attached workbook.