[Solved] Repeat latest group 1 for each group 2

[DynV]

I have 2 type of data, which are labeled group 1 & 2, which come in order and the 2nd type most likely is repeated; for example

1, A
2, B
2, C
2, D
1, E
2, F
2, G
1, H
2, I
2, J
2, K

I'd like for all the group 2 entries to mention its latest group 1; working with the previous example ending up with

A, B
A, C
A, D
E, F
E, G
H, I
H, J
H, K

I'm not sure which tool from the OO suite would help me with such issue, but I suspect OO Base would.

Thank you kindly for your help.

[MrProgrammer]

I have 2 type of data, which are labeled group 1 & 2, which come in order and the 2nd type most likely is repeated … I'd like for all the group 2 entries to mention its latest group 1; working with the previous example

This is easily accomplished in Calc with an IF() function. Click the dropdown in Cell A1 and select group 2.

202011181508.ods

(12.49 KiB) Downloaded 170 times

Edit: Replaced earlier attachment with this simpler one.

If your source data, shown as two values separated by commas, is actually in a single cell, first split it in two using [Tutorial] Text to Columns. Since the format of your source data is not clear to me, please attach a document demonstrating the situation next time (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). You will find that you receive better answers when you attach your data.

[Tutorial] Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[DynV]

The example was included in a Base forum, not Calc; I understand the latter would be prone to confusion, but the former seem to me pretty clear the quoted content included CSV, and yes it's CSV.

For archival or those that can't open the file, the 1st row is some odd header system, and the 1st 2 col(umns) are as in the OP example, and 3rd col contains

Code: Select all

=IF(A2=1,B2,C1)

which I don't really understand why the 1st content row would fetch something in what seems to be the header, but anyway it will never happen as that condition for the 1st content will never happen... well with right data, but this isn't to be a commercial process to check for everything. I marked the thread as solved as although I don't fully understand the issue at the beginning of this paragraph, the important part to just copy the 1st content group 1 over then check if the row is a group 1, and if not just copy the cell above.

I don't know why I didn't think of that. I was convinced this was something often seen in commercial situations (ie going through logs), and there would be tools for it; perhaps a set of macros or templates.

Thank you again

[DynV]

I removed the solved tag as I realized there's something not addressed, that the group 1 rows not be included; if what I'm referring to is unclear, please refer to the OP 2nd quote, a reminder both are CSV.

[Villeroy]

I'm not sure which tool from the OO suite would help me with such issue, but I suspect OO Base would.

This is easy to do in Calc as demonstrated by MrProgrammer. If it needs to be done in Base, there has to be a column which determines the order of appearance, a time stamp or auto-ID.

[Villeroy]

May be something like this. I'm not sure if this is a valid solution.

[DynV]

The issue is solved for this particular case but it would be nice to have it a bit "better" so I can use the method in a future similar situation.

Click the dropdown in Cell A1 and select group 2.

I saw it was selected and that something was displayed (as opposed to some error code instead) so didn't think it caused a problem to remain that way. I didn't realize you meant to unselect all but it.

Perhaps you'd like to explain how the system of how unselecting make it display as I desire (the thing that make the header I assume).

May be something like this. I'm not sure if this is a valid solution.

Thank you again. Is there a way to automate filling the table? Either reading from the file containing the data (flat-file DB), or something with an automated import process (ie pasting in Calc).

[Villeroy]

1) you can connect a Base document to the flat-file DB and copy a table icon onto a table icon in the other DB (mine is an embedded HSQLDB).
2) you can connect a text file to the embedded HSQLDB so it appears as a linked text table and copy that icon onto the binary table. [Tutorial] Using csv/text files as editable data source.
3) you can open the file with Calc, copy the range and paste onto the table.

You can export any table, view or query into spreadsheets: [Tutorial] Using registered datasources in Calc

[MrProgrammer]

Is there a way to automate filling the table?

If you want the process automated, don't use OpenOffice, use a non-interactive program, say Perl. Here is a Perl program which does what you requested in your initial post; I tested it. I realize you may not know how to program in Perl, but you didn't know how to do this with OpenOffice either, so what's the difference? Use a web search to find Perl forums if you need additional assistance with this approach.

use strict; use warnings; use Text::CSV;               # Program initialization
my ($in, $out, $csv, $ar, $t1);                        # Declare variables
$csv = Text::CSV->new();                               # Load CSV routines
open $in,  "<input.csv"  or die "input.csv: $!\n";     # Open input CSV
open $out, ">output.csv" or die "output.csv: $!\n";    # Open output CSV
while ($ar = $csv->getline($in)) {                     # Set array from input line
   if ($ar->[0] == 1) {$t1 = $ar->[1]; next;}          # Type 1: Just save data
   $ar->[0] = $t1;                                     # Type 2: Set saved data
   $csv->say($out,$ar);                                # Type 2: Write fields to CSV
   }

[DynV]

Click the dropdown in Cell A1 and select group 2.

Was Sorting, drilling, filtering, refreshing - Apache OpenOffice Wiki used to make the fancy headers?

[Villeroy]

With no doubt, Perl is the coolest scripting language of the past 40 years. However, the 2nd coolest scriping language which is installed together with the office suite is Python including the csv module.

Code: Select all

import csv
try:
    fout = open('output.csv','w')
except:
    print('can not open output.csv for writing')
    exit()
try:
    fin = open('input.csv','r')
except:
    print('can not open input.csv for reading')
    exit()
a = list()
r = csv.reader(fin, delimiter=',')
w = csv.writer(fout)
grp = ''
for row in r:
    if row[0] == '1':
        grp = row[1].strip()
    elif grp != '':
        item = row[1].strip()
        w.writerow((grp,item))

This one skips any leading lines where the second value is not 1 and it strips any leading and trailing space from the result values.

[MrProgrammer]

Was Sorting, drilling, filtering, refreshing - Apache OpenOffice Wiki used to make the fancy headers?

Yes. Data → Filter → AutoFilter. Read about this helpful feature in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum. Having to ask this question suggests to me that you could greatly benefit by reading the first half dozen or so chapters of any book about spreadsheets written in the last 25 years. And read the Ten Concepts tutorial which I linked for you earlier. Filters are in section 7.

This one skips any leading lines where the second value is not 1

Probably this modification; I didn't test.

next unless defined $t1; $ar->[0] = $t1;

it strips any leading and trailing space from the result values

I didn't allow for that because the OP's example didn't suggest it was necessary. The following change should strip leading and trailing spaces.

$csv = Text::CSV->new({allow_whitespace=>1});