Need some Excel-Fu!

November 5, 2015 2:37 PM

I have an excel doc with products down the left and people's names across the top. The people have each marked in their column the quantity of each product they would like. How do I make sure everyone gets what they want?

I'd like to be able to print off a list: Person A purchased one of product 4, three of product 15. Person B purchased two of product 8, one of product 10, one of product 19. Etc.

Or just something that is easier than scrolling through 100 products and hoping I don't miss something someone ordered. Usually I'm pretty good with excel + google but not even sure what to google here.

posted by magnetsphere to Computers & Internet (7 answers total)

Sounds like what you want to do is transpose the rows and columns?
posted by elmay at 2:51 PM on November 5, 2015

Delete any lines that don't have any orders and then sum up the total of products for each person. You can use that as your check. As for each person, do you know about filtering? You can put a filter on the page and filter for non-blank values as you fill each person's order.
posted by soelo at 3:08 PM on November 5, 2015

Response by poster: I've got filters and items counts at the bottom. Just hoping there is an easier way to pick and pack than reading down a column.
posted by magnetsphere at 3:15 PM on November 5, 2015

Have tried creating your list using a pivot table?
posted by srboisvert at 3:25 PM on November 5, 2015 [1 favorite]

I think what you actually need is to "unpivot" that data (aka melt it), such that you'd have one row for every combination of person and product (with the columns Person | Product | Quantity) - that would make a pretty nice checklist. This YouTube tutorial shows the general method, although it looks pretty arcane.

I could do this in about 45 seconds using the statistical software R, so drop me a message if you get stuck on Excel and just want to get it done.

Update: I just followed the instructions on some fake data and this works well; these instructions are more concise. By default this includes empty rows, but it sounds like you'd have no problem filtering those out.
posted by McBearclaw at 3:34 PM on November 5, 2015 [2 favorites]

It's funny how often this comes up in Excel. A grid format is easy to understand visually but not so great when you want to actually work with the data in any capacity. You want to get the data in list format, so that each line just has the person, the product, and the quantity ordered in it.

Some googling led me to this question, which I think will do what you want. Years ago I wrote a macro to do this that's saved me tons of time but it's not really in a user friendly enough state that I could just post it here.
posted by zixyer at 3:36 PM on November 5, 2015 [1 favorite]

The Power Query add-in from Microsoft has an "unpivot" feature that does exactly what you need.
posted by crazycanuck at 10:01 PM on November 5, 2015 [1 favorite]