[Solved] Append series of suffixes based on root

Discuss the spreadsheet application

[Solved] Append series of suffixes based on root

Postby cartoonjazz » Sun Mar 22, 2020 9:14 pm

Hi, I'm a noob and not sure if this can be done in calc or in base
I have 1 table with a series of skus and prices
sku price
abc 10
def 20

and one table with possible suffixes that should be applied to each sku
99
58
65

the final table should look like this
abc99 10
abc58 10
abc65 10
def99 20
def58 20
def65 20

any help is appreciated
Last edited by cartoonjazz on Wed Mar 25, 2020 10:18 pm, edited 1 time in total.
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 48
Joined: Sun Sep 04, 2016 9:40 pm

Re: append series of suffixes & apply price based on root

Postby RusselB » Sun Mar 22, 2020 10:42 pm

Easily done in Calc, not sure about Base.
My question is, how is it determined which suffix should be applied the the SKU?
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: append series of suffixes & apply price based on root

Postby Lupp » Sun Mar 22, 2020 11:12 pm

Setspeak: Do you want to get concatenated all the possible "pairs" out of the Cartesian product "SetOfSku x SetOfSuffix" in a column?
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: append series of suffixes & apply price based on root

Postby cartoonjazz » Mon Mar 23, 2020 2:34 am

I would like all possible suffixes applied to all skus
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 48
Joined: Sun Sep 04, 2016 9:40 pm

Re: append series of suffixes & apply price based on root

Postby Lupp » Mon Mar 23, 2020 2:14 pm

Sigh.
I had hoped for a "YES" or "NO", probably followed by additional explanations.
Since I want to get this from my to-do, I take your answer for a "yes", and post my respective statements:

-0- Create a few helper columns, and don't be too reluctant with it. Helpers often are the essential means to get a clear and maintainable structure in spreadsheets.
Thus:
-1- Calculate the number of expected rows in advance and create a column simply giving the indices for the results from 1 to the highest.
-2a- Create a column for the index into the column of primary items (SKU in your case) per row of the results.
-2b- As I uderstand your question, this will also be the index into the column of values (prices in your case).
-2b-- Concerning this statement I don't actually believe it makes sense in the real world.
-3- Create a column for the index into the column of secondary items ("suffixes" in your case) per row of the results.
-3-- If you intend to treat things looking like numbers as strings, definitely make them being strings from the beginning: Apply the 'Numbers' format code '@' ('Text') to the respective cells (a column) .
-4- Create the column for the results to be concatenated from the entries in the primary and secondary column using the mentioned indices.
-5- Create the column for the additional values and fill it in the appropriate way - using the first index or not.

Depending on the situation you may then want to copy the results and to PasteSpecial them as constant content elsewhere.

What formulas you can use is shown in the attached example.

Your questions and your answers to questions in return might be in the future as thorough as -hopefully, at least intentionally- this answer is. Long series of incomplete posts are wasting time.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: append series of suffixes & apply price based on root

Postby MrProgrammer » Mon Mar 23, 2020 4:20 pm

cartoonjazz wrote:Hi, I'm a noob …
You've been on the forum for over three years and have 47 posts so it's time to read this: [Tutorial] Ten concepts that every Calc user should know.

cartoonjazz wrote:I have 1 table with a series of skus and prices
   sku price
abc 10
def 20
and one table with possible suffixes that should be applied to each sku
   99
58
65
the final table should look like this
   abc99 10
abc58 10
abc65 10
def99 20
def58 20
def65 20
Sheet S0 has the original SKUs and prices.
Sheet XX has the suffixes. Create formulas, shown, in column B. The formulas in B3, B4, …, are all the same.
Sheet S1 will have the updated SKUs and prices. Create formulas, shown, in column A. The formulas in A2, A3, …, are all the same.
Click the A at the top of that column, then use Data → Text to Columns → OK.

202003221723.ods
(11.78 KiB) Downloaded 14 times

[Tutorial] How do I specify the formula for a column?
[Tutorial] Text to Columns

Lupp wrote:aoo101433rearrangeCartesianProductAsConcatenatedPairsInColumn_1.ods
This solution requires LibreOffice because IFERROR is not available in OpenOffice. IFERROR(α;β) could be replaced by IF(ISERROR(α);β;α).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3981
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: append series of suffixes & apply price based on root

Postby Villeroy » Mon Mar 23, 2020 6:52 pm

Trivial to do and easy to maintain in Base.

Tutorial: [SQL] JOINing 2 row sets
Attachments
CartesianProduct_t101433.odb
(5.25 KiB) Downloaded 17 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Append series of suffixes & apply price based on root

Postby cartoonjazz » Wed Mar 25, 2020 10:16 pm

Thank you, the base method definitely easiest works great
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 48
Joined: Sun Sep 04, 2016 9:40 pm


Return to Calc

Who is online

Users browsing this forum: jrkrideau, RusselB and 11 guests