Useful Tips For Making Sheets

Reduce time spent creating your sheets

1. Consider alternatives to Excel

It is true that Ascend kind of, sort of needs Excel to import sheets. However, you can also use something like Libreoffice to speed things up.
Libreoffice does have built in functions that work similarly to Excel that you may prefer to use to speed up your sheet creation.
Some still prefer to use Excel, which is totally fine. However, if you find Excel infuriating to use you may just want to use it as little as possible.
You can use Libreoffice to do all of the heavy lifting, and then Excel to open your file and save it (since sometimes Ascend isn't happy with the Libreoffice .xlsx file)

2. Strip out good text from bad cells

How many times have you seen a sheet with cells that have some useful information, but you need to extract the good out from everything? For example:

Description Cost MSRP UPC
Some Good Text - Followed By Garbage 25.00 49.99 012345561788
Garbage - Followed by Good Text 50 199.99 012345597411

Here we can use a formula to extract the text on either side of the hyphen.

If we want to keep what is on the left, we can use the LEFT function in combination with SEARCH tell it when to stop extracting.
For example, you can use =LEFT(cellname,SEARCH("-",cellname)-2) to turn Some Good Text - Followed By Garbage into Some Good Text.
You can also use =RIGHT(cellname, LEN(cellname) - FIND("-", cellname)-1) to turn Garbage - Followed by Good Text into Followed by Good Text.

LEFT or RIGHT dictates which direction to extract from. They both require 2 values: the cellname and how much to extract.
The second value depends on the cell contents, so we use SEARCH to find the split point (hyphen) and then remove 1 or 2 cells to remove the spaces around the hyphen.

Description Cost MSRP UPC
Some Good Text 25.00 49.99 012345561788
Followed by Good Text 50 199.99 012345597411

Two quick methods that can be used to strip out StyleName values from semi complete descriptions.

3. Clean & Complete your data!

The Ascend vendor spreadsheet does have many columns that you can use to enjoy the best possible data.
You can combine this data together to generate results that are uniform and easy for staff to search through.

Consider the following case (irrelevant columns omitted):

Description Cost MSRP UPC Brand
Jersey Brandname Modelname Gender Size Color 10.00 19.99 012345567896 Bike Brand

This is the standard type of row you may encounter when examining a new vendor sheet.
Technically the information is in the description, but you don't have any information for color, size, or gender that you can search.
Because of course you know you when you search in Ascend you can search specific columns like color, size, and gender right?

Instead we can fill out the information like this:

Description Cost MSRP UPC Brand Color Size StyleName Gender
10.00 19.99 012345567896 Bike Brand Color Size StyleName Gender

Now, we can use a formula in our software like textjoin to combine all of the cells that have text in them to form a good description!
In Libreoffice you can use textjoin(" ", 1, gendercell, stylenamecell, sizecell, colorcell) and you would get:

Description Cost MSRP UPC Brand Color Size StyleName Gender
Gender StyleName Size Color 10.00 19.99 012345567896 Bike Brand Color Size StyleName Gender

Obviously you can set it up anyway you want. However, once you do this it is much easier to search through, and you will have much more readable descriptions!

4. Adjust your prices!

Many times sheets will have prices that need to be adjusted. Sometimes a vendor may list an MSRP that needs to be tweaked. For example:

Description Cost MSRP UPC
Men's SuperFast Short Sleeve Jersey M White/Red 60.00 120 012345567896
Men's UltraFast Short Sleeve Jersey L UltraBlack 90.00 179.95 012345568145

Here we have 2 MSRPs that need to be tweaked.
They can both be adjusted using the same formula.

We can use the CEILING function to round up our values and then take our arbitrary 1 cent off (if that's how you roll).
For example, you can use =CEILING(cellname,1)-.01 to turn 120 into 119.99. It will also turn 179.95 into 180.

CEILING simply means to round up to the nearest number you set after the cellname. We used 1 (to round up to the nearest dollar).
If you had prices such as 122.49 and wanted to round up to $124.99 you could use =CEILING(cellname,5)-.01 (round up to nearest $5 increment)

Description Cost MSRP UPC
Men's SuperFast Short Sleeve Jersey M White/Red 60.00 119.99 012345567896
Men's UltraFast Short Sleeve Jersey L UltraBlack 90.00 179.99 012345568145

One quick function that makes adjusting prices a LOT easier!

5. Fix those case quantities!

We do have a case quantity tool that can help to identify inaccuracies between a new sheet and your current products.
However this requires your current information to be correct. You may have missed some case quantities, here is how you can find some sneaky products from your sheets.

Description Cost MSRP UPC
CASSETTE SPROCKET, CS-HG21, 9-SPD,10-PIECS SEMI-BULK PACK 190.00 399.99 187857931678
MTB STEEL BRAKE INNER CABLE BOX 1.2X3000MM 100PCS 200 449.99 187857931005

Here are products from a vendor sheet that are actually case quantities. Some are easier to find due to the price, but using keywords can be helpful to track them down.

Depending on the vendor, they may use certain keywords that *MIGHT* be a case quantity. You may have to do a little digging to confirm.
Some keywords that have been useful in the past (this list will likely be updated): pair, bulk, pcs, piec, pack

Remember when possible to use singular versions to get more results. So, although pair and pairs may yield results, searching for pair will get both.

Description Size Cost MSRP UPC CaseQTY CaseMSRP CaseUPC
HG21 Cassette 9s 11-30T 11-30T 190.00 44.99 10 399.99 187857931678
MTB Brake Cable 1.2x3000mm 1.2x3000mm 200 4.99 100 449.99 187857931005

Don't forget to use the opportunity to fix the descriptions and fill out all of the relevant information!
Good luck in your searches!