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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

inetgnu@gmail.com - 02 Mar 2007 05:08 GMT

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

Reply to this Message

Herbert Seidenberg - 03 Mar 2007 16:12 GMT

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.

Reply to this Message

inetgnu@gmail.com - 15 Mar 2007 12:53 GMT

On Mar 4, 12:12 am, "Herbert Seidenberg" <herbds7-ms...@yahoo.com>
wrote:

> 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

Reply to this Message