Converting varying length Vertically-aligned records to Horizontal Rows of list (tabular)

[inetgnu]

I have a Excel Spreadsheet with Records of books. The fields and data
value is entered as the First and Second Column (ColA, ColB). The
records are separated by 2 empty rows. Some fields are missing for
some records: Some record may miss the 'author' whereas some records
may miss some other fields, say ,'subject' AND 'totalpage'.

The Objective is to tranform the record horizontally with Each ffield
in one column with missing field blanked so that all the field are
aligned in a column e.g. colA for 'Title', colB for 'Author'


How can we convert varying length Vertically-aligned records (Figure
1) to Horizontal Rows of list (tabular) (Figure 2)

(Figure 1- Three records: varying length- subject & totalpage missing)
==================================================
Title Intro to Cpt
Author James, Page
publisher M-HILL
subject CPT
totalpage 311
isbn 123

Title Basic A/C
Author Ben William
publisher JWS
isbn 721


Title Modern Phy
publisher P-HALL
subject PHY
totalpage 466
isbn 265


(Figure 2: One Header of field name and all the records list
horizontally)
============================================
Title Author publisher subject totalpage isbn
Intro to Cpt James, Page M-HILL CPT 311 123
Basic A/C Ben William JWS 721
Modern Phy P-HALL PHY 466 265


Thank in advance

 

[Herbert Seidenberg]

It can be done with Pivot Table > Multiple Consolidation Ranges,
but data has to be translated to numbers and back to text.
Also limited to 75 books in my approach.
Details available on request.

 

[inetgnu]

It can be done with Pivot Table > Multiple Consolidation Ranges,
but data has to be translated to numbers and back to text.
Also limited to 75 books in my approach.
Details available on request.

Dear Mr. Seidenberg,

I've tried <Pivot Table> -> <Multiple Consolidation Ranges>. Then I
input the 3 records in the Range in the <PilotTable and PivotChart
Wizard - Step 2b of 3>. Then in the <PilotTable and PivotChart Wizard
- Step 3 of 3>.window, I Click the <Layout> Button and perform the
following steps:

1) Transpose( by Dragging) the <ROW> button to the COLUMN Area,
2) Transpose( by Dragging) the <COLUMN> button to the ROW Area,

However in the DATA Area, I get Only the (Count of Value), I have
tried to edit this Button and Drag another <Value> button to the DATA
Area. The operations on this Button are either Numerical or
Statistical functions. I CANNOT find any function on Text Data
(String)

After clicking <Finish>, I get the following result


Count of Value Row
Column Author isbn publisher subject totalpage
Grand Total
Basic A/C 1 1 1 3
Intro to Cpt 1 1 1 1 1 5
ModernPhy 1 1 1 1 4
Grand Total 2 3 3 2 2 12
**************************
Figure 1 - Text (String) Data cannot be transformed. e.g. The value of
the fields: Author, Publisher, Subject
----------------------------------------------------------------------------------------------------------------------------------------------------------
This is NOT what I want, I need the following:

Count of Value Row
Tilte Author isbn publisher subject totalpage
Basic A/C Ben William 721 JWS
Intro to Cpt James, Page 123 M-HILL CPT 311
ModernPhy 265 P-HALL PHY 466
**************************
Figure 2 - EXPECTED Output: Text (String) Data transformed

E.g. The value of the fields: Author, Publisher, Subject are filled
with the Corresponding String:

-- 'Ben William' for <Author> field;
-- 'JWS' for <publisher> field for the BOOK "Basic A/C"

Is there any step wrong or missing? Could you please send our kindest
advice to this problem?

Thank in advance