[Solved] Split rows for multiple categories

Discuss the spreadsheet application
Post Reply
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

[Solved] Split rows for multiple categories

Post by TCLynx »

Here is example A
Date       Ref  #   Name        Item    Total    Fee  Sales tax  Shipping
01/25/21   828163   Sharon      Order       8      0       0.56         0
01/28/21   828724   Elizabeth   Order      16      0       1.12         0
02/04/21   829868   Kristen     Order      21   0.63       1.47         0
02/10/21   830861   Margaret    Order      24   0.72          0       8.9
02/17/21   831714   Sharon      Order    17.5      0       1.23         0
02/18/21   831889   Margaret    Order      20    0.6          0      4.55
02/24/21   832784   Elizabeth   Order      29      0       2.03         0
02/26/21   832960   Margaret    Order      15   0.45          0       4.2
02/26/21   832964   Sharon      Order      23   0.69       1.61         0
And here is the example B
Date         Ref #      Name        Item           Total
01/25/21     828163     Sharon      Order              8
01/25/21     828163     Sharon      Sales tax       0.56
01/28/21     828724     Elizabeth   Order             16
01/28/21     828724     Elizabeth   Sales tax       1.12
02/04/21     829868     Kristen     Order             21
02/04/21     829868     Kristen     Fee             0.63
02/04/21     829868     Kristen     Sales tax       1.47
02/10/21     830861     Margaret    Order             24
02/10/21     830861     Margaret    Fee             0.72
02/10/21     830861     Margaret    Shipping         8.9
02/17/21     831714     Sharon      Order           17.5
02/17/21     831714     Sharon      Sales tax       1.23
02/18/21     831889     Margaret    Order             20
02/18/21     831889     Margaret    Fee              0.6
02/18/21     831889     Margaret    Shipping        4.55
02/24/21     832784     Elizabeth   Order             29
02/24/21     832784     Elizabeth   Sales tax       2.03
02/26/21     832960     Margaret    Order             15
02/26/21     832960     Margaret    Fee             0.45
02/26/21     832960     Margaret    Shipping         4.2
02/26/21     832964     Sharon      Order             23
02/26/21     832964     Sharon      Fee             0.69
02/26/21     832964     Sharon      Sales tax       1.61
I'm sure there should be some way to do this sort of thing kinda automatically without having to copy and paste everything by hand. I've been using templates to paste spreadsheets into to make it easier to get things organized for import into other software but I'm just not getting my mind around what operation I would need to carry out to make this sort of change. Seems to me there must be a way and :crazy: I'm just not thinking of the right term to search for.
Last edited by TCLynx on Tue Apr 06, 2021 10:00 pm, edited 3 times in total.
TCLynx Open office 4.1.6 Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Split rows for multiple categories

Post by RusselB »

I'm guessing you have Example A and want Example B. Personally I can't think of a way to automatically do this, mainly because some of the entries have 2 entries and some have 3.
In fact, based on your Example A, it's possible for you to have anywhere from 1 to 4 entries.
Looking at the information from the standpoint of trying to calculate totals (eg: total sales tax for the month), you'll go crazy trying to get the information from your Example B, but from your Example A, it's a simple usage of SUMIF, SUMIFS, or SUMPRODUCT.
There is an almost endless list of calculations that you may/will want to do with your data that will require far more work with Example B than Example A.
I can not recommend that you switch to using the format in Example B vs. Example A, or I can see you coming back looking for methods to do calculations that are simple using Example A, but nearly impossible with Example B.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

Well seeing as I will already HAVE example A I will be able to calculate anything I need. However, for purposes of entering the Invoices into financial software, I need each thing as a separate line with the same reference number.

Also, it would be OK to add lines with the 0 values, I just don't actually need them.

Would a pivot table be able to do this for me? I'm not very good with pivot tables but I have managed to use them some.
TCLynx Open office 4.1.6 Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Split rows for multiple categories

Post by RusselB »

Are you generating Example B in order to have it in an order for manually entering into financial software or are you going to be importing it?
If you're going to be importing it, check your financial software for options that may allow you to use the form that you currently have in Example A.

As to a pivot table, I believe it would be an option, but I haven't used them much, and thus do not consider myself an expert regarding them.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

I have software for Importing into the financial software. The software does not import it properly in it's current form (I can Map one of the amounts at a time which would require me doing the operation 4 times which becomes problematic if any of the Items is a negative number, it works far better if I can get it into the correct form and just use a single mapping.)
TCLynx Open office 4.1.6 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split rows for multiple categories

Post by Villeroy »

Where does example A come from? You did not type these data into the sheet, did you?
A pivot table would convert example 2 into example 1.
The term you are not searching for is "database"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

I did basically type out these examples (some info taken from a real source but it is really a fictitious example.)

Item
Date Reference # Name Data Order Total Result
01/25/21 828163 Sharon Sum - Total 8 8
Sum - Fee 0 0
Sum - Sales tax 0.56 0.56
Sum - Shipping 0 0
01/28/21 828724 Elizabeth Sum - Total 16 16
Sum - Fee 0 0
Sum - Sales tax 1.12 1.12
Sum - Shipping 0 0
02/04/21 829868 Kristen Sum - Total 21 21
Sum - Fee 0.63 0.63
Sum - Sales tax 1.47 1.47
Sum - Shipping 0 0
02/10/21 830861 Margaret Sum - Total 24 24
Sum - Fee 0.72 0.72
Sum - Sales tax 0 0
Sum - Shipping 8.9 8.9
02/17/21 831714 Sharon Sum - Total 17.5 17.5
Sum - Fee 0 0
Sum - Sales tax 1.23 1.23
Sum - Shipping 0 0
02/18/21 831889 Margaret Sum - Total 20 20
Sum - Fee 0.6 0.6
Sum - Sales tax 0 0
Sum - Shipping 4.55 4.55
02/24/21 832784 Elizabeth Sum - Total 29 29
Sum - Fee 0 0
Sum - Sales tax 2.03 2.03
Sum - Shipping 0 0
02/26/21 832960 Margaret Sum - Total 15 15
Sum - Fee 0.45 0.45
Sum - Sales tax 0 0
Sum - Shipping 4.2 4.2
02/26/21 832964 Sharon Sum - Total 23 23
Sum - Fee 0.69 0.69
Sum - Sales tax 1.61 1.61
Sum - Shipping 0 0

Above is what I got from making a pivot table from example A. Then I just need to figure out how to fill the lines in a special paste with the correct reference number.
Villeroy wrote: The term you are not searching for is "database"
I miss the old works database program. It wasn't really a full fledged database but it was super functional for simple things.
TCLynx Open office 4.1.6 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split rows for multiple categories

Post by Villeroy »

TCLynx wrote:I did basically type out these examples (some info taken from a real source but it is really a fictitious example.)
I wanted to know where the real data come from. I guess you import these data from a csv file. Or do you copy/paste it from some website? As already stated, a pivot table can split up categories into columns, so it would generate something like example B from example A but not the other way round.

The attached database has 2 tables "Import" and "Data". Import is where you can paste spreadsheet data. "Data" is the resulting table where I already imported your example data.
There is a form with no form controls. It is a text document containing a script.
1) Copy the new import data from spreadsheet.
2) In the main database window select the icon of the "Import" table (don't open it).
3) Paste
4) In the dialog check "first row has column headers" if you included the headers in step 1). Confirm with [Create...]. The column order is the same as in your example data.
5) Copy the SQL script from the form.
6) In the database main window call Tools>SQL.....
7) Paste and execute the script.
What the script does is more or less self-explaining:

Code: Select all

INSERT INTO "Data"(SELECT "Date","Ref","Name","Item","Total",NULL FROM "Import" where "Item"='Order');
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Fee',"Fee",NULL FROM "Import" where "Fee">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Sales tax',"Sales tax",NULL FROM "Import" where "Sales tax">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Shipping',"Shipping",NULL FROM "Import" where "Shipping">0);
DELETE FROM "Import";
It inserts new records from the first 5 fields where column "Item" has the string 'Order'. The 6th column is an auto-ID (kind of row number) and receives a NULL value. The database engine takes care of that value.
Then it inserts new records from the first 3 fields, the text 'Fee' and the "Fee" value where column "Fee" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Sales tax' and the "Sales tax" value where column "Sales tax" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Shipping' and the "Shipping" value where column "Shipping" has a value >0.
Finally it deletes all data from the "Import" table.
------
I have added a unique index on columns "Ref" and "Item" which means that the table refuses to store duplicates with the same ref and item name. This means you get the following error message when you try to import the same example data again:
"Violation of unique index "uniqRef": duplicate value(s) for column(s) "Ref","Item""
Attachments
t104947.odb
(12.55 KiB) Downloaded 138 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

Villeroy wrote:
The attached database has 2 tables "Import" and "Data". Import is where you can paste spreadsheet data. "Data" is the resulting table where I already imported your example data.
Yes the initial data would be from a spreadsheet.

Wow, that last post is a Big one. Thank you, I will see if I can get my head around it. (so far when I've tried to use the Database in open office I've usually gotten myself so confused that I've given up.)

Thank you again. I will give it a try later when I have a little more uninterrupted time to focus on it.
TCLynx Open office 4.1.6 Windows 10
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Split rows for multiple categories

Post by JohnSUN-Pensioner »

Yes, Villeroy is right - this is indeed a task for the database.
I did a little differently - I created and registered a database, dragged the data from Example A (from Calc sheet) into it and created a Query1:

Code: Select all

SELECT "Date", "Ref #", "Name", "Item", "Total" FROM "Sheet1" Where "Total" > 0
UNION
SELECT "Date", "Ref #", "Name", 'Fee' AS "Item", "Fee" AS "Total" FROM "Sheet1" Where "Total" > 0
UNION
SELECT "Date", "Ref #", "Name", 'Sales tax' AS "Item", "Sales tax" AS "Total" FROM "Sheet1" Where "Total" > 0
UNION
SELECT "Date", "Ref #", "Name", 'Shipping' AS "Item", "Shipping" AS "Total" FROM "Sheet1" Where "Total" > 0
ORDER BY "Name"
Then I just dragged the query result back to the Calc sheet and got the required Example B
UnPivotQuery.png
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split rows for multiple categories

Post by Villeroy »

Yep, that works. However, I would try to eliminate Calc completely and create a text table linked to the import.csv file (if there is any csv file). Then I would open the database, copy the import table over the data table and that's it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split rows for multiple categories

Post by Villeroy »

TCLynx wrote: I miss the old works database program. It wasn't really a full fledged database but it was super functional for simple things.
Believe me, Works couldn't handle this neither. Base comes with a 90% fledged database (HSQL 1.8) but you can connect Base to any professional database engine that comes with a suitable driver. Base is not a database. First of all, is a tool to work with databases. Then it comes with its own HSQL database engine on board which can be useful in some cases.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Split rows for multiple categories

Post by JohnSUN-Pensioner »

OK, you can always use a macro, why not?

Code: Select all

Function Unpivot(aData As Variant, nConst As Integer) As Variant
Dim aRes As Variant
Dim aTemp As Variant
Dim i As Long, j As Integer, k As Integer, nRow As Long
Rem Length of new table
	i = (UBound(aData) - LBound(aData))*((UBound(aData,2)-nConst)+1)+1
	ReDim aRes(1 To i,1 To nConst)
Rem Header
	nRow = 1
	For i = 1 To nConst
		aRes(nRow, i) = aData(nRow, i)
	Next i
Rem First nConst columns
	For j = 2 To UBound(aData)
		nRow = nRow + 1
		For i = 1 To nConst
			aRes(nRow, i) = aData(j, i)
		Next i
	Next j
Rem Rest of columns (after nConst)
	For k = nConst+1 To UBound(aData,2)
		For j = 2 To UBound(aData)
			nRow = nRow + 1
			For i = 1 To nConst-2
				aRes(nRow, i) = aData(j, i)
			Next i
			aRes(nRow, nConst-1) = aData(1, k)
			aRes(nRow, nConst) = aData(j, k)
		Next j
	Next k
	Unpivot = aRes
End Function
Just use formula like as =UNPIVOT(A1:H10;5) and press Ctrl+Shift+Enter
First param is source range, second - the number of columns that copied unchanged, "as is"
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

Thank you all for the ideas.

I'm having an issue figuring out how to use Base, I don't see an import icon on the base file you shared. (sorry, it is probably that you are calling it something different than what I'm looking for maybe? I don't know.)

And when I click on Tables I'm getting an error telling me I don't have Java? Or something like that? I do have the latest version of Java on this computer so I'm not sure how to fix that.

However, I think I have figured out how to do this with the Pivot Table Idea. Basically open the spreadsheet and copy into a template, refresh the Pivot table, copy and paste into a sheet with a template to fill in the needed data into the needed fields for import.
Sorry, I'm an old stagehand turned Farmer/Mom that is now having to do the book keeping for multiple businesses and none of the software plays nice together from more than two play dates in a row. And then I have the problem of not being able to remember how I fixed the problems the previous time I had problems since it worked last month. Anyone ever notice that Paypal change the order of the fields from one download to the next? And my farm market software gives one report that breaks everything out by line item but the other report does it a different way and doesn't actually give me a report that shows the convenience fee. :knock: Sorry, rant over :ucrazy: Thank you for not getting worked up by my venting.
TCLynx Open office 4.1.6 Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Split rows for multiple categories

Post by RoryOF »

Re: Java : OpenOffice is a 32 bit application, so you must download and install (and then select) a 32 bit Java for it. The selection is made in /Tools /Options /OpenOffice /Java. The 32 bit Java will live happily alongside your installed 64 bit Java.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

So a pivot table from example A

Item
Date Reference # Name Data Order Total Result
01/25/21 828163 Sharon Sum - Total 8 8
Sum - Fee 0 0
Sum - Sales tax 0.56 0.56
Sum - Shipping 0 0
01/28/21 828724 Elizabeth Sum - Total 16 16
Sum - Fee 0 0
Sum - Sales tax 1.12 1.12
Sum - Shipping 0 0
02/04/21 829868 Kristen Sum - Total 21 21
Sum - Fee 0.63 0.63
Sum - Sales tax 1.47 1.47
Sum - Shipping 0 0
02/10/21 830861 Margaret Sum - Total 24 24
Sum - Fee 0.72 0.72
Sum - Sales tax 0 0
Sum - Shipping 8.9 8.9
02/17/21 831714 Sharon Sum - Total 17.5 17.5
Sum - Fee 0 0
Sum - Sales tax 1.23 1.23
Sum - Shipping 0 0
02/18/21 831889 Margaret Sum - Total 20 20
Sum - Fee 0.6 0.6
Sum - Sales tax 0 0
Sum - Shipping 4.55 4.55
02/24/21 832784 Elizabeth Sum - Total 29 29
Sum - Fee 0 0
Sum - Sales tax 2.03 2.03
Sum - Shipping 0 0
02/26/21 832960 Margaret Sum - Total 15 15
Sum - Fee 0.45 0.45
Sum - Sales tax 0 0
Sum - Shipping 4.2 4.2
02/26/21 832964 Sharon Sum - Total 23 23
Sum - Fee 0.69 0.69
Sum - Sales tax 1.61 1.61
Sum - Shipping 0 0
Total Sum - Total 173.5 173.5
Total Sum - Fee 3.09 3.09
Total Sum - Sales tax 8.02 8.02
Total Sum - Shipping 17.65 17.65

I then Copy and paste the desired portion of said pivot into another sheet where I have set up the template with formula to look for appropriate information or copy from above row and here is the result below.

Date Invoice # Name Item amount
01/25/21 828163 Sharon Total 8
01/25/21 828163 Sharon Fee 0
01/25/21 828163 Sharon Sales tax 0.56
01/25/21 828163 Sharon Shipping 0
01/28/21 828724 Elizabeth Total 16
01/28/21 828724 Elizabeth Fee 0
01/28/21 828724 Elizabeth Sales tax 1.12
01/28/21 828724 Elizabeth Shipping 0
02/04/21 829868 Kristen Total 21
02/04/21 829868 Kristen Fee 0.63
02/04/21 829868 Kristen Sales tax 1.47
02/04/21 829868 Kristen Shipping 0
02/10/21 830861 Margaret Total 24
02/10/21 830861 Margaret Fee 0.72
02/10/21 830861 Margaret Sales tax 0
02/10/21 830861 Margaret Shipping 8.9
02/17/21 831714 Sharon Total 17.5
02/17/21 831714 Sharon Fee 0
02/17/21 831714 Sharon Sales tax 1.23
02/17/21 831714 Sharon Shipping 0
02/18/21 831889 Margaret Total 20
02/18/21 831889 Margaret Fee 0.6
02/18/21 831889 Margaret Sales tax 0
02/18/21 831889 Margaret Shipping 4.55
02/24/21 832784 Elizabeth Total 29
02/24/21 832784 Elizabeth Fee 0
02/24/21 832784 Elizabeth Sales tax 2.03
02/24/21 832784 Elizabeth Shipping 0
02/26/21 832960 Margaret Total 15
02/26/21 832960 Margaret Fee 0.45
02/26/21 832960 Margaret Sales tax 0
02/26/21 832960 Margaret Shipping 4.2
02/26/21 832964 Sharon Total 23
02/26/21 832964 Sharon Fee 0.69
02/26/21 832964 Sharon Sales tax 1.61
02/26/21 832964 Sharon Shipping 0

So not super elegant but I can certainly make a template to handle this.
TCLynx Open office 4.1.6 Windows 10
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

RoryOF wrote:Re: Java : OpenOffice is a 32 bit application, so you must download and install (and then select) a 32 bit Java for it. The selection is made in /Tools /Options /OpenOffice /Java. The 32 bit Java will live happily alongside your installed 64 bit Java.
Ok, so you are saying I need to download and install 32 bit Java. And then within Open office Tools, I need to select it?
TCLynx Open office 4.1.6 Windows 10
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Split rows for multiple categories

Post by Hagar Delest »

Yes, you need to do so.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Split rows for multiple categories

Post by Villeroy »

Or install the 64 bit version of LibreOffice for Windows.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

Hagar Delest wrote:Yes, you need to do so.
I Just tried but I don't think it worked, or there is something I need to do that it isn't telling me I need to do?
Villeroy wrote:Or install the 64 bit version of LibreOffice for Windows.
Ok so is LibreOffice just the "new" version of Open office? I've been using open office for a while now and while I'm no expert with it, if I upgrade, am I going to be messing up all the work and templates I've made in my current Open Office Calc?

At the moment, I think I've figured out the Solution to my challenge so am wondering if I need to just leave well enough alone for now and not run down another rabbit hole of installing/upgrading software (which often wastes days working out the kinks.)
TCLynx Open office 4.1.6 Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Split rows for multiple categories

Post by RusselB »

LibreOffice is a fork of OpenOffice and has a more active development team.
Most, if not all, of your templates should work just fine in LibreOffice
You could install a portable version of LibreOffice to try just in case there are problems with the templates.
There are differences between the two, but whether those differences will affect your templates I have no idea.
You can have both OpenOffice and LibreOffice installed on the same system
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

RusselB wrote:LibreOffice is a fork of OpenOffice and has a more active development team.
Most, if not all, of your templates should work just fine in LibreOffice
You could install a portable version of LibreOffice to try just in case there are problems with the templates.
There are differences between the two, but whether those differences will affect your templates I have no idea.
You can have both OpenOffice and LibreOffice installed on the same system
Ok, might try LibreOffice then.
TCLynx Open office 4.1.6 Windows 10
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

Villeroy wrote: The attached database has 2 tables "Import" and "Data". Import is where you can paste spreadsheet data. "Data" is the resulting table where I already imported your example data.
There is a form with no form controls. It is a text document containing a script.
1) Copy the new import data from spreadsheet.
2) In the main database window select the icon of the "Import" table (don't open it).
3) Paste
4) In the dialog check "first row has column headers" if you included the headers in step 1). Confirm with [Create...]. The column order is the same as in your example data.
5) Copy the SQL script from the form.
6) In the database main window call Tools>SQL.....
7) Paste and execute the script.
What the script does is more or less self-explaining:

Code: Select all

INSERT INTO "Data"(SELECT "Date","Ref","Name","Item","Total",NULL FROM "Import" where "Item"='Order');
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Fee',"Fee",NULL FROM "Import" where "Fee">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Sales tax',"Sales tax",NULL FROM "Import" where "Sales tax">0);
INSERT INTO "Data"(SELECT "Date","Ref","Name",'Shipping',"Shipping",NULL FROM "Import" where "Shipping">0);
DELETE FROM "Import";
It inserts new records from the first 5 fields where column "Item" has the string 'Order'. The 6th column is an auto-ID (kind of row number) and receives a NULL value. The database engine takes care of that value.
Then it inserts new records from the first 3 fields, the text 'Fee' and the "Fee" value where column "Fee" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Sales tax' and the "Sales tax" value where column "Sales tax" has a value >0.
Then it inserts new records from the first 3 fields, the text 'Shipping' and the "Shipping" value where column "Shipping" has a value >0.
Finally it deletes all data from the "Import" table.
------
I have added a unique index on columns "Ref" and "Item" which means that the table refuses to store duplicates with the same ref and item name. This means you get the following error message when you try to import the same example data again:
"Violation of unique index "uniqRef": duplicate value(s) for column(s) "Ref","Item""
So I installed LibreOffice (since for some reason, even after installing 32 bit Java I can't get the OpenOffice Base to play nice) And I think I was able to get through Step 4. After that .... Well I think I'm doing step 5 and 6 fine but step 7 doesn't do anything.

Oh, wait, am I suppose to copy the code from here?
Ok, that seemed to work. Hum, now where do I find the output?
Oh, right the Data table.

So that seems to work too. However, It may take me more to get my head around how to modify it to handle somewhat more complex initial import material.
TCLynx Open office 4.1.6 Windows 10
TCLynx
Posts: 31
Joined: Tue Jun 09, 2020 1:20 am

Re: Split rows for multiple categories

Post by TCLynx »

JohnSUN-Pensioner wrote:OK, you can always use a macro, why not?

Code: Select all

Function Unpivot(aData As Variant, nConst As Integer) As Variant
Dim aRes As Variant
Dim aTemp As Variant
Dim i As Long, j As Integer, k As Integer, nRow As Long
Rem Length of new table
	i = (UBound(aData) - LBound(aData))*((UBound(aData,2)-nConst)+1)+1
	ReDim aRes(1 To i,1 To nConst)
Rem Header
	nRow = 1
	For i = 1 To nConst
		aRes(nRow, i) = aData(nRow, i)
	Next i
Rem First nConst columns
	For j = 2 To UBound(aData)
		nRow = nRow + 1
		For i = 1 To nConst
			aRes(nRow, i) = aData(j, i)
		Next i
	Next j
Rem Rest of columns (after nConst)
	For k = nConst+1 To UBound(aData,2)
		For j = 2 To UBound(aData)
			nRow = nRow + 1
			For i = 1 To nConst-2
				aRes(nRow, i) = aData(j, i)
			Next i
			aRes(nRow, nConst-1) = aData(1, k)
			aRes(nRow, nConst) = aData(j, k)
		Next j
	Next k
	Unpivot = aRes
End Function
Just use formula like as =UNPIVOT(A1:H10;5) and press Ctrl+Shift+Enter
First param is source range, second - the number of columns that copied unchanged, "as is"
I Like this Idea. I must admit however, I probably need some tutorials to help me get my head around how to do macros.
TCLynx Open office 4.1.6 Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Split rows for multiple categories

Post by RoryOF »

Probably more than you need is in the OpenOffice BASIC manual. The completest works are those of Andrew Pitonyak, but they are 5 - 6 times the length of the BASIC manual, and may tell you much more than you want or need to know,
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Post Reply