Can Excel do this?

%3CLINGO-SUB%20id%3D%22lingo-sub-2232976%22%20slang%3D%22en-US%22%3ECan%20Excel%20do%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2232976%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20%22table%22%20that%20lists%20types%20of%20furniture%20down%20the%20side%2C%20and%20room%20numbers%20across%20the%20top.%26nbsp%3B%20As%20you%20can%20see%2C%20there%20are%20a%20lot%20of%20blank%20spaces%2C%20depending%20on%20what%20furniture%20goes%20into%20what%20room.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20figure%20out%20if%20there%20is%20a%20way%20that%20Excel%20can%20do%20like%20a%20summary%20(like%20I%20highlighted%20in%20yellow)%2C%20where%20it%20would%20list%20the%20room%20numbers%20from%20the%20top%20row%2C%20for%20each%20piece%20of%20furniture%2C%20where%20the%20quantity%20is%20greater%20than%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2232976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2233005%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20Excel%20do%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2233005%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20case%20the%20excel%20file%20I%20attached%20doesn't%20open%20correctly%2C%20here%20is%20a%20jpg%20of%20the%20same%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2234264%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20Excel%20do%20this%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1007543%22%20target%3D%22_blank%22%3E%40JayClark%3C%2FA%3E%26nbsp%3BIn%20case%20you%20are%20a%20MS365%20subscriber%20in%20the%20Current%20channel%2C%20you%20could%20use%20FILTER%20for%20this%20as%20demonstrated%20in%20the%20picture.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-25%20at%2006.37.03.png%22%20style%3D%22width%3A%20470px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266905iA6F94B908E6A6578%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-25%20at%2006.37.03.png%22%20alt%3D%22Screenshot%202021-03-25%20at%2006.37.03.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 

Thanks!

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

@JayClark 

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

image.png

 

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