Partially merge two rows, then delete second

Discuss your migration story or project

Partially merge two rows, then delete second

Postby jadawl01 » Tue Sep 01, 2009 1:40 am

Hi all
I am back again with my timetable :oops:
I dont know if there is macro that can do such a complicated task
In bref: I need to compare two rows, if a condition is meet, concatenate/add some cells from second row to the first row , delete the second row, edit a cell in first row, then look for next
these is simplified data from the timetable posted here: viewtopic.php?f=52&t=21890

subgrpbefore.png
subgrpbefore.png (8.32 KiB) Viewed 10258 times

Column C contains student groups like: TS1, TS2... TL1, TL2, 2S1...
Column C contain Subgroups named like this:
TS1-1, TS1-2 those are sbgroups for group TS1
TS2-1 and TS2-2 are subgroups for group TS2
1S1-1 and 1S1-2 are subgroups for group 1S1
and so on..... (there are many groups and subgroups, with same naming scheme,
and for a given group G, there is only two possible subgroups: G-1 and G-2)
I need to merge each two rows containing data for the two subgroups
like this:
suppose a first occurence of first subgroup is in row 3 (like in screenshoot)
check for the next row containing data about second subgroup, its name is ending in "-2"
add content from cell E12 to cell E3
add content from cell F12 to cell F3
delete row 12
rename C3's content from "TS1-1" to "TS1" (strip subgroup reference)
then loop
ouuf !! hope I explained my problem very well !!
Basically, I want output to be similar to that:

Image
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Partially merge two rows, then delete second

Postby squenson » Wed Sep 02, 2009 8:11 pm

You would definitively need a macro for that purpose, as the only way to delete or add rows is macros, not formulas. This is a support forum for OOo you should try by yourself and propose your code, then we could help you. An excellent free ebook to start.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Partially merge two rows, then delete second

Postby eremmel » Thu Sep 03, 2009 10:07 pm

I think that you might get away without a macro. But that depends on how strict your data patters are. You stated that there are two possible sub groups: '-1' and '-2'. But when I check the uploaded file farabitimetable.ods in the other topic I see sub groups named '-4', '-22' etc. So can you be more specific about this.
You are also stating the algorithm that you want to apply, it is based on the order of the data. But when I look to your data it seems to me that you have a kind of key value that matches with the next record to be found: Day+Period+Student-group and Subject. We can use this key value with a VLOOKUP() to construct the concatenation of the two fields.
When we apply auto filter we can select all the records that you need and copy them into a new sheet.
Let me now if this manual action is acceptable and we might work it out.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 939
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Partially merge two rows, then delete second

Postby jadawl01 » Sun Sep 06, 2009 9:53 pm

Thanks for help
I attached a new file, hopefully well formatted
squenson gave me a precious help to build teacher's timetables form raw data, here:
viewtopic.php?f=52&t=21890#p99574

Now I need to do the same thing for student sets
pull data to build a student timetable like this
Image
whith "student set's name" instead of "teacher's name"
each cell should contain related information from column D, E and F
You are also stating the algorithm that you want to apply, it is based on the order of the data

The order is not important
The problem is that for a given student set group, there are also two subgroups belonging to that group
The first ends in "-1"
The second ends in "-2"
Informations related to a subgroup should be displayed within parent group timetable
The two subgroups are taking courses simulatanousely, so data should be put in the same cell
for this reason, I wanted to concatenate them into one cell

Hope I made myself clear :D
and sorry for bad english
Attachments
farabitimetable2.ods
(20.43 KiB) Downloaded 365 times
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Partially merge two rows, then delete second

Postby eremmel » Mon Sep 07, 2009 12:59 pm

You stated:
The problem is that for a given student set group, there are also two subgroups belonging to that group

I see in the data you attached that there is a Student Set Group on certain day/time OR there are two subgroups for a certain Student Set Group on a certain day/time.
I understand that the issue is to combine the data of the two sub groups and represent. I think that you can make a solution for this. I also assume you learned from the teacher table so when you have data on one row per Student Set Group you can take it over.

So here is my idea to help you, We need a couple of helper columns:
1a. We might need to reorder the data so we have only those rows at the top of the sheet that will go into your overview. That will be the rows that refer to a single Student Group and the ones that refer to the first Student Set Group. Column H StudentSetOrder:
Code: Select all   Expand viewCollapse view
=IF(RIGHT(C2;2)="-2";2;0)

1b We need a column that gives the Student Set Group with values 0,1 and 2. Column I StudentSetGroup:
Code: Select all   Expand viewCollapse view
=IF(LEFT(RIGHT(C2;2);1)="-";VALUE(RIGHT(C2;1);0)

2a. There is a function VLOOKUP() that can search through a table and show data from it based on a key. We will use that to find a match between the '-1' and '-2' sub groups. First we have to define this key by combining the information from columns: A,B,C. Column J LookupKey
Code: Select all   Expand viewCollapse view
=CONCATENATE(A2;B2;C2)

3. It is handsome to combine in info from the columns D,E,F to one field for use in VLOOKUP. Column K Info:
Code: Select all   Expand viewCollapse view
=CONCATENATE(D2;": ";E2;IF(LEN(F2);": ";"");F2)

4. We need a final Student Set name without the sub group (suppress infor of group '-2'). Column L StudentsSet2:
Code: Select all   Expand viewCollapse view
=IF(I2=0;C2;IF(I2=1;LEFT(C2;LEN(C2)-2);""))

5. We can now build the combined information for each group(suppress infor of group '-2', after // is comments): Column M CombinedInfo:
Code: Select all   Expand viewCollapse view
=IF(I2=0;K2;                                                               // Take Info value when no-sub groups
  IF(I2=1; CONCATENATE(K2;"; ";                                            // When sub group -1 combine Info with Info
      VLOOKUP(CONCATENATE(LEFT(J2;LEN(J2)-2);"-2");$J$2:$K$527;2;0))       // from vlookup of sub group with replacement of '-1' with '-2'.
  ;""))                                                                    // Else sub group '-2' do nothing.


See attached calc document.
Attachments
farabitimetable22.ods
(68.62 KiB) Downloaded 530 times
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 939
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Partially merge two rows, then delete second

Postby jadawl01 » Wed Sep 09, 2009 2:38 pm

Thanks a lot for taking all this time to help me
I will try to read an re-read added functions carefully to understadnd them
Then I will try to constuct a table to pull data and display it correctly into the student's timetable similar to teacher's timetable
Then I will came back with feedback
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm


Return to Institutions & Educational

Who is online

Users browsing this forum: No registered users and 1 guest