Google-sheets – URL parameters with unique product ID per row

google sheets

A company sells a lot of carrots; every Carrot has a unique product ID.

I want the first row, containing a 9 digit product-ID to make every ID into a clickable URL format, containing a treated string corresponding to the product-ID.

Here is an example of a given URL:

https://example.com/wp-admin/edit.php?action2=-1&action=-1&fb_sync_enabled&paged=1&post_status=all&post_type=product&product_type&s=010666666&stock_status

The ID's are made out of 9 digits, separated past the fourth one by a hyphen.

In order to create a functioning URL the hyphen needs to be removed.

For the above mentioned example, the product ID with that very clickable link would be 01066-6666 .

A 1 is a title and should thus be ignored.

Any method that renders this result is the right one.

Best Answer

I cannot answer how to solve this issue within Google sheets. But since the original Question says this:

Any method that renders this result is the right one.

here is how I would solve the problem with a workaround:

It requires the following conditions to be met:

From your Question I understand that all URLs are in the same column (for easy copying)
the URLs look the same / have the same length
You have a code editor installed that allows mass-editing, for example VS Code. you could probably substitute this for a online version

Follow these steps:

open new VS Code file
copy the whole column of your sheet into VS Code
delete the line that you want to have ignored (A1)
on line 1, click exactly on the position where the hyphen needs to be inserted
hold CTRL + ALT on your keyboard
press the arrow down key on your keyboard until you hit the last line
you will notice the cursor duplicated itself and shows on each line now, exactly at the position where you need to insert the hyphen
insert only 1 hyphen
the hyphen will get inserted into every line at the position of the respective cursor in that line
copy all URLs
paste them back into the sheet and overwrite the previous values

Related Solutions

Google Sheets – Create a Pivot Table with Custom Formula Per Row

As a pre-processing step, I suggest representing "yes" answers as 1 and "no" as 0; this makes aggregation easier.

Then in a pivot table, you can simply create a calculated field =sum(better)/sum(had), assuming "had" and "better" are your column headers.

However, I don't see the need for pivot table report when you are essentially using it as a query. Sorting within a pivot table report can be problematic, and in any case doesn't happen automatically. Here is a query solution, again assuming that the responses are recorded as 0-1 numbers. It automatically sorts by ratio (better)/(had prior), in descending order.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc", 1)

The last argument makes it explicit that the input data hasa= a header row.

And here is a more elaborate version, with labels on columns.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc label A 'Name', sum(C)/sum(B) 'Ratio'", 1)

Google Sheets Conditional Formatting – Alternate Row Color by Unique Sorted Values

Scenario

If you have 500 items, in column B, but only 20 of them are unique invoices due to them being broken up into purchase orders. It makes sense to keep them separate so you can more easily reconcile checks that are written to you.

Solution

The solution I came up with involves using the custom-formula for conditional formatting.

If column A is the Purchase-Order Numbers ( that are entirely unique ) and column B are your invoices that repeat multiple times. Assuming you have headers in row 1. =ISODD(MATCH($B2,UNIQUE($B$2:$B),0))

Explanation of Solution

UNIQUE() is an array returning formula that is exclusive to google sheets (it does not work in excel). This allows you to figure out how many unique invoices exist within column B. So UNIQUE($B$2:$B) returns a list of only the unique numbers, removing all duplicates.

MATCH() returns an integer of where an item is located in a 1 dimensional range or array. I used it to look at each item in column B and find it's location within the array created by UNIQUE($B$2:$B) ; SO MATCH($B2,UNIQUE($B$2:$B),0) will look at cell B2, and find where it is located in UNIQUE($B$2:$B) and return a number that represents which place it is in the list.

=ISODD() returns TRUE or FALSE depending on the number you give it. For Instance ISODD(1) will return TRUE. So if we wrap ISODD() around our match-unique formula it will alternate with TRUE, FALSE per each different invoice. SO, =ISODD(MATCH($B2,UNIQUE($B$2:$B),0))

Best Answer

Related Solutions

Google Sheets – Create a Pivot Table with Custom Formula Per Row

Google Sheets Conditional Formatting – Alternate Row Color by Unique Sorted Values

Related Topic