Hi,

I wanted to merge only COUNTRY 'ENGLAND' values in one cell. Though, I'm at MS Office 2013,i may convince the business on warning message as I'm using EXL2K. I don't want to use Macro at this time.

DEFINE FILE CAR
MYCOUNTRY/A200=IF COUNTRY EQ 'ENGLAND' THEN '<table tr>'|'<td rowspan="4";>'|COUNTRY|'</td>' |'</tr>'|'</table>' 
ELSE COUNTRY;
END
TABLE FILE CAR
PRINT MYCOUNTRY SALES 
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K
END  

Thanks to Waz on his original post!

This message has been edited. Last edited by: <Kathryn Henning>,

Rifaz,
What are you trying to accomplish?

Sorry Daniel, I can't post the picture now.I wanted to merge the cells in Excel.

What I want

I only want the COUNTRY column ENLAND 4 rows merged to be in one cell

 
COUNTRY	SALES

	0
        12000
ENGLAND	0
	0 

What I get

Perhaps as per the xml, Sales column also getting merged, which i don't want.

ENGLAND	0
	
	
	
ENGLAND	12000
	
	
	
ENGLAND	0
	
	
	
ENGLAND	0
	
	  

Please let me know, if you understand it.

This message has been edited. Last edited by: Rifaz,

Got it.

Any other way?

This message has been edited. Last edited by: Rifaz,

Okay, I'm okay to go ahead with Macros. However, I need the detailed steps to create macro enabled template file (.xltm) from MS Office Excel 2013. I don't have XP machine.

What I followed:

Open excel, record macro(merge cells), delete original content save it in .xltm format & close.

Re-open the saved .xltm file, enable content but it's not executing the macro.

Help me getting rid of this, what I'm missing.

Rifaz,

Would the below code work for you?

  
TABLE FILE CAR
 PRINT
 COMPUTE FILLER1/A5 = '    '; AS ''
     'CAR.SPECS.LENGTH'
     'CAR.SPECS.WIDTH'
     'CAR.SPECS.HEIGHT'
 COMPUTE FILLER2/A5 = '    '; AS ''
     'CAR.SPECS.BHP'
     'CAR.SPECS.RPM'
     'CAR.SPECS.MPG'
 COMPUTE HEAD1/A4 = '    '; NOPRINT
 COMPUTE HEAD2/A30 = 'Dimensions'; NOPRINT  COMPUTE HEAD3/A30 = 'Statistics'; NOPRINT  BY 'CAR.COMP.CAR'
 BY 'CAR.CARREC.MODEL'
 HEADING
  "<HEAD1 <HEAD2 <HEAD3 "
 ON TABLE SET PAGE-NUM NOLEAD
 ON TABLE NOTOTAL
 ON TABLE HOLD FORMAT HTMTABLE
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
    UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
    TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, $
    TYPE=TITLE, STYLE=BOLD, $
    TYPE=HEADING, SIZE=12, STYLE=BOLD, HEADALIGN=BODY, $
    TYPE=HEADING, LINE=1, OBJECT=FIELD, ITEM=1, COLSPAN=2, JUSTIFY=CENTER, $
    TYPE=HEADING, LINE=1, OBJECT=FIELD, ITEM=2, COLSPAN=3, CLASS=headstyle, $
    TYPE=HEADING, LINE=1, OBJECT=FIELD, ITEM=3, COLSPAN=3, CLASS=headstyle, $
ENDSTYLE
END
-*
SET HTMLFORMTYPE=XLS
-RUN
-*
-HTMLFORM BEGIN
<style type="text/css">
.headstyle {font-family:Arial; font-size:12pt; font-weight:bold; text-align:center; background:rgb(210,210,210); border: 3px solid red;} </style>

!IBI.FIL.HOLD;
-HTMLFORM END

Thanks prodrigu, I need to merge the datas vertically ,wish to have a option like ROWSPAN to specified cells. See the sample screenshot, what I'm expecting

This message has been edited. Last edited by: Rifaz,

Color can be used to group data in a report. The COMPUTES assign sequence numbers and odd/even indicators to the countries, which are then used in the style sheet to assign background colors to the data.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON

TABLE FILE CAR
 PRINT SALES
 COMPUTE SEQ/I5 = IF (COUNTRY NE LAST COUNTRY) THEN (LAST SEQ + 1) ELSE LAST SEQ ;  NOPRINT
 COMPUTE HALF/I5 = SEQ / 2 ;  NOPRINT
 COMPUTE WHOLE/I5 = HALF * 2 ;  NOPRINT
 BY COUNTRY
 ON TABLE SET HTMLCSS ON
 ON TABLE SET BYDISPLAY OFF
 ON TABLE COLUMN-TOTAL
 ON TABLE PCHOLD FORMAT EXL2K
 ON TABLE SET STYLE *
  TYPE=REPORT, COLOR=RGB(66 70 73), FONT='TREBUCHET MS', SIZE=9, SQUEEZE=ON, GRID=OFF, $
  TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255), STYLE=-UNDERLINE+BOLD, $
  TYPE=DATA, BACKCOLOR=RGB(235 235 240)), WHEN=SEQ NE WHOLE, $
  TYPE=DATA, BACKCOLOR=RGB(255 255 255)), WHEN=SEQ EQ WHOLE, $
  TYPE=GRANDTOTAL, BACKCOLOR=RGB(66 70 73), COLOR=RGB(255 255 255), STYLE=BOLD, $
 ENDSTYLE
END

My issue is not to do conditional styling, its about merging cells & aligned to vertically centre(not within the same cell).Just to showcase, I posted using different colours. Even i tried with identifiers to place the ENGLAND values, but the problem again is that it won't meet the exact expectation. Looks little odd, for instance, if sales has 4 values either i can have ENGLAND either in Row 3 or Row 2.

I'm going to create a COUNTER column as a identifier to give a try as this is the last option I've now. Would be appreciated, if anyone has alternate workaround or proper steps to create a macro template in Excel 2013.

So Rifaz's question was avoided? I'm looking at the same issue of getting a WebFOCUS procedure to output an Excel file or HTML table where I want adjacent rows within a single column merged together when they have the same value. Just like the Excel image that Rifaz posted above on 4/8/2015 and saying, "Thanks prodrigu, I need to merge the datas vertically ,wish to have a option like ROWSPAN to specified cells. See the sample screenshot, what I'm expecting". This could very easily be accomplished when I did reporting in SSRS.

Also is there a way to turn the data of one column 90 degrees (vertical)?

Hi Lee,

I used the dummy counter column to do the styling because my column informations are static. So, based on the counter values I performed the conditional stylings to show them in middle of the cell. Agree,if the data count for the particular sort value is 4(even), it won't be looking nice however you can place either in 2nd or 3rd cell, we could at-least land up at the skies when we aim for the moon.

quote:

Also is there a way to turn the data of one column 90 degrees (vertical)?

Check this out, please do search the forums, you'll end up with lot of clues.

Set Vertical Position for Column Titiles in PDF and EXCEL

Good luck!

Hello Rifaz,
I need somethign similar for row merge/vertical center. How to accomplish this seems like a ROWSPAN option? In this example I need to see COUNTRY field as one CELL centered vertically.

 TABLE FILE CAR
SUM SALES  
BY COUNTRY 
BY CAR
END

 

Pls let me know.

I'm on vacation till next week, so didn't carry my laptop to give the exact code.

As far as I remember, I created a counter column in a DEFINE field and another one temporary field to perform the conditional styling(border styling on/off accordingly).

Good luck :-)

I posted something on this a short while ago (24 March 2016)- link

T