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

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

0
inetgnu (3)
3/2/2007 5:08:51 AM
excel 39879 articles. 2 followers. Follow

2 Replies
281 Views

Similar Articles

[PageSpeed] 31

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.

0
3/3/2007 4:12:45 PM
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

0
inetgnu (3)
3/15/2007 11:53:47 AM
Reply:

Similar Artilces:

questions on drop down list
Hello all!!im filling a dropdown list with results from a query as th code shows (i fill the drop down everytime i open the xls file), BU this dropdown keeps all information when i close the file and when run the code again the result is merged into the older, i need to clea the dropdown in this code before i fill it again!!! i tried .Clear bu excel says it doesnt belong to the object class..i dunno what to do > > Sub Auto_Open() > > Const strActiveConnection > "DSN=TURMALINA;Description=TURMALINA;;;APP=Microsoft Offic > 2003;WSID=GENERATOR;DATABASE=JDE_DEVELOPME...

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Go To Record
I have a form where the user updates information in fields on a form that is set to Continuous Forms to mimic a data sheet. When it is necessary to add additional information, the user clicks a button that opens a seperate form for additional info that is not a part of every record. Example Two tables, tblone, tbltwo. Two forms, frmRequest (continuous forms mimicking a datasheet), frmDayApproval On frmRequest, Record 1 has data fields a, b, c, d filled in and are all stored on tblone. On frmRequest, Record 2 has data fileds a, b, c, d filled in and are also stored on tblone. However,...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Color Coding Email Listings
Hi All, Without using the Followup function. Is there a way to color code the email listings. Mac's are good for this, and Eudors on PC. I may want to highlight an email with color codes... and the entire listing being highlighted is a better visual then the followup flag. And these items really aren't being highlighted for followup.. just for more important future reference. Thanks -Dave Dave Bar <d@d.com> wrote: > Without using the Followup function. > Is there a way to color code the email listings. See if these help: http://www.howto-outlook.com/howto/colore...

how to convert excel's .cvf file to .csv file
...

match rows from spreadsheets-Please help.
how do i match rows from different spreadsheets to a directory: Directory ZIP No. 80001 1 80002 2 80003 3 80004 4 80005 5 80006 6 80007 7 Sheet 1: ZIP POP 80001 134 80003 9890 80006 9489 80009 883 =>: Directory ZIP No. POP 80001 1 134 80002 2 80003 3 9890 80004 4 80005 5 80006 6 9489 80007 7 Use VLOOKUP If you tell us where each block of data resides, I will give a more detailed answer. -- Bernard V Liengme www.stfx.ca/people/bliengme r...

Creating custom list with a comma in it
I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these va...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Specific row at the top of the screen?
I have been looking in this forum for an answer to help me position a specific row located by a macro at the top of the screen. Can someone help? Thank's ahead From the Excel Vba help file... This example moves row ten to the top of the window. Worksheets("Sheet1").Activate ActiveWindow.ScrollRow = 10 -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Bobby" wrote in message I have been looking in this forum for an answer to help me position a specific row located by a macro at the top of the scre...

Pivot Table Row Field Format (OLAP Source)
I have a pivot table linked to an OLAP cube. In this instance, my only row field contains numbers (the number of days taken to complete a task) and all I want to do is display a count for each total (i.e. 234 items took 1 day, 345 took 2 etc). The problem is that these numbers are listed as text, therefore 1,10,100,11 instead of 1,2,3. In view of the number of days, I'm trying to avoid manually adjusting the list. Apart from importing the source data into Excel rather than using the cube, is there any way to get around this? The problem is in your cube. Edit the dimension in your...

related records in advanced find doesn't work
Hello, I have an advanced find where I am trying to bring in some information from the 'related to' lead on an appointment. No matter what lead field I select, no data is returned for that column. Has anyone seen this behavior or had any issues with this? Thanks! The 'related to' fields work as a seach filter criteria so you are specifying here which records to include in your search. The 'edit columns' option dictate the data columns visibly returned from the query. Regards Marion "jpop" wrote: > Hello, > > I have an advanced find w...

Change color of row (Col A thru Col Q) based on text value in Col J
I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? One way: Target.EntireRow.resize(1,17).Interior.ColorIndex = 36 JingleRock wrote: > > I...

Row Grand Totals in Pivot Tables?
I'm working in Excel 2007 and I can't seem to see my row grand totals in my pivot table. I can see the grand totals on the columns, but no rows. Any ideas? Hi adodson See if the article at http://www.techonthenet.com/excel/pivottbls/gtotal_col2007.php does what you want. Regards, Pedro J. > I'm working in Excel 2007 and I can't seem to see my row grand totals in my > pivot table. I can see the grand totals on the columns, but no rows. Any > ideas? Yeah, that is what I would expect it to do too. However, I set that option and don't receive the total. ...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Starting with no records for a filter
Hi, i have a subform that i filter based on some combo boxes at the top of the main form that the user can type into. i have a search method that is called using the following: call searchMethod(Nz(box1.value, ""), Nz(boz2.value,"") , ..... ) this search method then builds up an sql string to filter the records. this works well. when i initially load the form up, i have a minor problem. all of the records are shown, because all of the box values are "", and so there is effectively no filter. i actually want the opposite of this. i.e if the user doesnt se...

hisind rows with an if statement
I would like to be able to hide a number of rows based on the response to a question.. The response to the question is a list box with either "yes" or "no" as the choices.. If the response = "no" then I would like to hide the next 3 rows, otherwise do nothing.. Is there a "hide" command of some sort that I can use as part of an IF statement or is this going to require a macro ? -- Thanks Larry Requires a macro, specifically a Worksheet_Change() event macro: Assuming your "YES/NO" choice is in column A the code belo...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Can I import a windows explorer list of files into an Excel sprea.
I was wondering if it is possible to import the list of files within a particular folder that one sees when using Windows Explorer, into an Excel spreadsheet? I am using WinXP and Office2003. modify to suit Sub GetFileList() Dim iCtr As Integer With Application.FileSearch .NewSearch .LookIn = "c:\aa" .SearchSubFolders = True .Filename = ".xls" If .Execute > 0 Then For iCtr = 1 To .FoundFiles.Count Cells(iCtr, 1).Value = .FoundFiles(iCtr) Next iCtr End If End With End Sub -- Don Guillett SalesAid Software donaldb@281....

Problem converting from Quicken to M2005
My Quicken files are mostly investment related, and generally converted fine. However all bonds (regular and muni's) converted as Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) How do you change the Investment Type for an item? Thank you. In microsoft.public.money, Mike wrote: >My Quicken files are mostly investment related, and generally >converted fine. However all bonds (regular and muni's) converted as >Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) Money typically converts custom data types from Quicken into funds. I thou...

Inventory list columns
There needs to be more columns avaialble in the inventory and other lists. It would also be nice to be able to print from the inventory lists. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en...