Reverse Pivottable Help

[zinja]

I have looked through the forum and found a solution that is kinda what I need (

http://www.tek-tips.com/viewthread.cfm?qid=950733),

but it assumes a single column as the key field. I have three columns for key fields.

CODE    DESC	        DRGNO   XAE     XAB
ACEI	Aceon	        016266	N	N
ACEI	Altace	        020412	N	N
ACEI	benazepril	021610	Y	Y
ACEI	benazepril/HCTZ	021507	N	Y
ACEI	captopril	018750	Y	Y
ACEI	captopril/HCTZ	015342	N	Y
ACEI	enalapril	018981	Y	Y
ACEI	enalapril /HCTZ	019754	N	Y
ACEI	fosinopril	025577	N	Y
ACEI	fosinopril/HCTZ	023495	N	Y
ACEI	lisinopril	019605	Y	Y
ACEI	lisinopril/HCTZ	018686	Y	Y
ACEI	Mavik	        011031	N	N
ACEI	quinapril	023678	Y	Y
ACEI	quinaretic	021928	N	N
ACEI	Univasc	        013050	N	N
ACEI	Lotrel	        009844	Y	Y
ACEI	benazepril  	028425	N	Y
ACEI	trandolapril	028500	N	Y
ACEI	moexipril	028644	N	Y
ACEI	moexipril/HCTZ	029416	N	Y
ACEI	ramipril	029655	Y	N
ARB	Atacand	        018195	N	N
ARB	Avapro	        024556	N	N
ARB	Benicar	        018825	N	Y
ARB	Cozaar	        018184	Y	Y
ARB	Diovan	        021638	Y	Y
ARB	Micardis	019100	N	N
ARB	Teveten	        018910	N	N
ARB	Diovan HCT	022007	Y	Y
ARB	Hyzaar	        021757	Y	Y
ARB	Atacand HCT	016730	N	N
ARB	Avalide	        016268	N	N
ARB	Benicar HCT	025052	N	Y
ARB	Micardis HCT	023930	N	N
ARB	Teveten HCT	019990	N	N
HMGCOA	Altoprev	022427	N	N
HMGCOA	Crestor	        020690	Y	Y
HMGCOA	Lescol	        011707	N	N
HMGCOA	Lescol XL	017013	N	N
HMGCOA	Lipitor	        010765	Y	Y
HMGCOA	lovastatin	020667	Y	Y
HMGCOA	Mevacor	        008625	N	N
HMGCOA	Pravachol	006863	N	N
HMGCOA	Pravigard	021897	N	N
HMGCOA	Zocor	        025503	N	N
HMGCOA	Vytorin	        022859	Y	Y
HMGCOA	Zetia	        023896	Y	Y
HMGCOA	Pravostatin	027438	Y	Y
HMGCOA	Simvastatin	027631	Y	Y

That is how the table looks now. I need a column added for Company and then the data from columns XAE and XAB (among others, I just didn't include all the columns).

I am somewhat familiar with VBA and don't mind doing the work, just need some guidance.

Any help would be appreciated. After I get the spreadsheet in the correct layout, I then have to write a routine to get that data into an ISAM table. Lot's of fun!

Thanks,


LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[SkipVought]

LJ,

I'm not sure that I understand the question.

Regarding the ISAM file, format ALL cells as FONT - Courier 8. AUTOFIT the columns. SaveAS .prn text file.

If the column widths are not correct in the .prn file, adjust the offending column by padding ONE CELL with SPACES to the coorect character width.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[zinja]

I don't need any help with the ISAM file creation. I have a DBL routine to do that for me. What I need help with is getting the spreadsheet into the correct layout in order to import.

Thanks,


LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[SkipVought]

OK, you posted the CURRENT layout.

What is the OUTPUT format and mapping?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[zinja]

Skip,

I need the correct layout to be something like:

CODE     DESC     DRGNO     COMPANY    COV
ACEI     Aceon    016266    XAE        N    
ACEI     Aceon    016266    XAB        N
ACEI     Altace   020412    XAE        N
ACEI     Altace   020412    XAB        N
...

Thanks for the fast response!,



LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[SkipVought]

Where did COV come from?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[SkipVought]

OK got it.

How many company columns do you have in your source?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[zinja]

Company codes start at D1 (including the column header) and to Z1. Full data range for company columns is D1:Z394.

Thanks!,


LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[SkipVought]

The easiest way is to use MS Query. faq68-5829

You will have to edit the SQL in the SQL Window.

SELECT CODE, `DESC`, DRGNO, 'XAE' As Company, XAE
FROM `Sheet1$`

UNION ALL

SELECT CODE, `DESC`, DRGNO, 'XAB' , XAB
FROM `Sheet1$`

UNION ALL

and so forth........

and stack these UNION queries for the remaining columns.

This query will be on a NEW SHEET. The source data is on Sheet1.


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]