Partially merge two rows, then delete second

[jadawl01]

Hi all
I am back again with my timetable
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: http://user.services.openoffice.org/en/ ... 52&t=21890

subgrpbefore.png (8.32 KiB) Viewed 22258 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:

[squenson]

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.

[eremmel]

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.

[jadawl01]

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:
http://user.services.openoffice.org/en/ ... 890#p99574

Now I need to do the same thing for student sets
pull data to build a student timetable like this

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
and sorry for bad english

[eremmel]

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

=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

=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

=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

=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

=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

=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.

[jadawl01]

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