Forum Discussion
Sorting backup: Making a new column from a separate sheet sorted based on original sheet
I've been using Clover for our business for a few years now, but after a recent update I am unable to sort my items by quantity on their website in the category tabs anymore. As an alternative I want to use the exportable excel documents to help us in the meantime. the only problem is I'm not sure how to pull off what I want to do. I also have been unsure how or what to search for to solve this issue to see if anyone else has a solution or not, so apologies if this has been asked before.
In the exported document, there is an "Items" tab with all the info for each item. In the "categories" tab, each category is shown with which items are in those categories. Since I sorted our inventory so no items exist in 2 categories, I am wondering if there is a way to auto-populate a new Column in the "items" tab, where a category name can be placed in the new column that matches the corresponding items location in the "categories" tab?
Once I can get this figured out then I can easily sort all cells by category, then by quantity!
3 Replies
I'd structure the Categories sheet differently:
You can then use a simple XLOOKUP, VLOOKUP or INDEX/MATCH formula to look up the category an item belongs to,
the problem I am facing though is the spreadsheet you are seeing is how it is automatically exported from Clover. the hope is to be able to auto-populate column A on "items" tab without having to rebuild the "categories" tab every time I export the inventory document.
However, I have been looking at the lookup functions recently, I can take a look at the index/match functions. If there is a way to auto-format the "categories" tab to look like what you got there then yes I agree it will make this SO much easier!Try this:
=IFERROR(LET(Col, SUM((Categories!$B$2:$Z$99=B2)*COLUMN($B$1:$Z$1)), IF(Col=0, "", INDEX(Categories!$A$1:$Z$1, Col))), "")
